none
Can I more select specific number from SQL query in C#

    Question

  • I work in windows form application c# vs2015 integrated to sql server 2012
    I face problem 
    i need to select more specific number in same time but i dont know
    what query i write or what i do in c#
    current i using select * from table where userID=@UserID
    this is select only number
    but if i need to select more specific number
    suppose i have numbers from 1001 to 1020
    i need to select 1005,1009,1012,1017
    How to get result for more records from SQL query or c# in same time 
    any way i can accept
    Thursday, March 30, 2017 9:43 AM

Answers

  • Here you go, there is no checking to see if items are separated by comma nor are the values numeric, that is easy to add.

    int[] Values = Array.ConvertAll(textBox1.Text.Split(','),
        delegate (string s) { return int.Parse(s); });
    
    var ops = new Operations();
    ops.Demo(Values);

    EDIT Ok, here is a version that asserts if each item is a int. You should need to see if there are items(s) in the TextBox

    int[] Values = Array.ConvertAll(textBox1.Text.Split(','), new Converter<string, int>((input) => {
        int output = 0;
    
        int.TryParse(input, out output);
    
        return output;
    }));
    
    var ops = new Operations();
    ops.Demo(Values);


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, March 30, 2017 11:02 AM
    Moderator

All replies

  • Here is one way to do this.

    In a form

    var Values = new int[] { 2, 10, 5 };
    var ops = new Operations();
    ops.Demo(Values);

    In a class (best to separate from form code)

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Xml;
    
    namespace Operations_cs
    {
        public class Operations
        {
            private string Server = "KARENS-PC";
            private string Catalog = "ForumExamples";
            private string ConnectionString = "";
    
            public Operations()
            {
                ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
            }
    
            public DataTable Demo(int[] Values)
            {
                
                var result = string.Join(",", Values);
                var dt = new DataTable();
                using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = $"SELECT [PK],[FullName] FROM [People] WHERE PK IN ({result})";
                        cn.Open();
                        dt.Load(cmd.ExecuteReader());
                    }
                }
    
                return dt;
    
            }
        }
    }
    

    FIrst we should test outside of code e.g. in SQL-Server Management Studio

    Or create a text file in your project, rename it to say DemoSql.sql which allows you to run a query but first connecting via the 5th button then press the first button to execute.

    The last screenshot shows our results using the DataSet Visualizer by hovering over the dt object


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by RJP1973 Thursday, March 30, 2017 11:01 AM
    Thursday, March 30, 2017 10:33 AM
    Moderator
  • thank you for reply

    this is static but if i need it as avariable

    how i do it

    so that how i do

    my  situation is

    text box have 1005,2006,1004

    how i do as to be dynamic

    Thursday, March 30, 2017 10:47 AM
  • Karen's answer IS dynamic.

    The Values argument is combined into a string in the result variable then formatted into the SQL using the newer $ interpolated string syntax. If you are not using C# 6 you can rewrite that line as:

    cmd.CommandText = String.Format("SELECT [PK],[FullName] FROM [People] WHERE PK IN ({0})", result);

    To get the Values argument - an array of ints - you could process your text from your TextBox (split it using String.Split and loop around turning it into an array of ints) or just pass the text straight in and use that instead of the int array (you would need to be sure that the text box absolutely contained a list of numbers though - probably best to validate that first).

    Thursday, March 30, 2017 10:59 AM
  • Here you go, there is no checking to see if items are separated by comma nor are the values numeric, that is easy to add.

    int[] Values = Array.ConvertAll(textBox1.Text.Split(','),
        delegate (string s) { return int.Parse(s); });
    
    var ops = new Operations();
    ops.Demo(Values);

    EDIT Ok, here is a version that asserts if each item is a int. You should need to see if there are items(s) in the TextBox

    int[] Values = Array.ConvertAll(textBox1.Text.Split(','), new Converter<string, int>((input) => {
        int output = 0;
    
        int.TryParse(input, out output);
    
        return output;
    }));
    
    var ops = new Operations();
    ops.Demo(Values);


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, March 30, 2017 11:02 AM
    Moderator
  • THANK YOU VERY MUCH
    Thursday, March 30, 2017 11:46 AM
  • Glad I could help :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 30, 2017 11:47 AM
    Moderator