none
Want to pass list in sql query RRS feed

  • Question

  • want to pass the list of ProductID's in Sql query. And retrive all rows that are matched with productId's list. I am doing in such a way but it's run only row in any case.
            public DataTable GetAllProductWhere(List<string> productIds)
            {
                DataTable dt = new DataTable();
                DataAccess da = new DataAccess();
                SqlParameter[] parameter = { };
                string query = string.Format("select * from GEMSSProduct where product_Id in ({0})", string.Join(",", productIds));
                dt = da.ExecuteQuery(query, CommandType.Text, parameter);
                return dt;
            }
    waiting for your reply
     
    • Moved by Bob Beauchemin Thursday, December 3, 2015 6:54 PM Moved to the forum for client-side SqlClient for best results
    Thursday, December 3, 2015 6:26 PM

Answers

  • Thursday, December 3, 2015 7:12 PM
  • Hi DanyalHaider,

    I create a demo that it could solve your issue. The following is my code snippet.

    public DataTable GetAllProducts(List<string> productIds)
    {
        string connString = ConfigurationManager.ConnectionStrings["conn"].ToString(); 
        DataTable dt = new DataTable();
        string query = string.Format("select * from Product where productId in ({0})", string.Join(",", productIds));
        using (SqlConnection connection = new SqlConnection(connString))
         {       
           try
             {
                  connection.Open();
                  SqlDataAdapter command = new SqlDataAdapter(query, connection);
                  command.Fill(dt);
              }
             catch (System.Data.SqlClient.SqlException ex)
             {
                  throw new Exception(ex.Message);
              }           
          }
        return dt;
    }
    

    Note that SQL Injection. You could also use entity framework to solve the problem. The following code snippet is reference for you.

    string[] produectidsArr = productIds.ToArray();
     var query = context.Products.Where(m => produectidsArr.Contains(m.ProductId));
    

    Best Regards,

    Cole

    Friday, December 4, 2015 9:58 AM
    Moderator

All replies

  • Thursday, December 3, 2015 7:12 PM
  • how can we implement this using table values parameter in my context?
    Thursday, December 3, 2015 7:31 PM
  • Follow the link. It contains a complete example.
    Thursday, December 3, 2015 9:22 PM
  • Although you can pass in a list using table-valued parameters, you can't parameterize the IN clause in a SQL Server query "...where product_Id in ({0})". You'd have to take use dynamic SQL, using the table-value parameter to build the dynamic SQL's IN clause.
    Friday, December 4, 2015 2:27 AM
  • Hi DanyalHaider,

    I create a demo that it could solve your issue. The following is my code snippet.

    public DataTable GetAllProducts(List<string> productIds)
    {
        string connString = ConfigurationManager.ConnectionStrings["conn"].ToString(); 
        DataTable dt = new DataTable();
        string query = string.Format("select * from Product where productId in ({0})", string.Join(",", productIds));
        using (SqlConnection connection = new SqlConnection(connString))
         {       
           try
             {
                  connection.Open();
                  SqlDataAdapter command = new SqlDataAdapter(query, connection);
                  command.Fill(dt);
              }
             catch (System.Data.SqlClient.SqlException ex)
             {
                  throw new Exception(ex.Message);
              }           
          }
        return dt;
    }
    

    Note that SQL Injection. You could also use entity framework to solve the problem. The following code snippet is reference for you.

    string[] produectidsArr = productIds.ToArray();
     var query = context.Products.Where(m => produectidsArr.Contains(m.ProductId));
    

    Best Regards,

    Cole

    Friday, December 4, 2015 9:58 AM
    Moderator
  • With a TVP, you either place SELECT * FROM @tvp in your IN clause or you simply modify it to an INNER JOIN.

    The TVP:

    USE Test;
    GO
    
    CREATE TYPE dbo.tvp_Test AS TABLE
    	( 
    		ID INT NOT NULL, 
    		PRIMARY KEY CLUSTERED ( ID ASC )
    	);
    GO

    and the query:

    namespace ConsoleCS
    {
        using System;
        using System.Data;
        using System.Data.SqlClient;
    
        class Program
        {
            static void Main(string[] args)
            {
                const string CONNECTION_STRING = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=(local);";
                const string COMMAND_TEXT = "SELECT COUNT(*) FROM sys.tables T INNER JOIN @tvp TVP ON T.object_id = TVP.ID;";
                using (SqlConnection connection = new SqlConnection(CONNECTION_STRING))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(COMMAND_TEXT, connection))
                    {
                        using (DataTable objectIds = new DataTable("tvp"))
                        {
                            objectIds.Columns.Add("ID", typeof(int));
                            objectIds.Rows.Add(565577053);
                            objectIds.Rows.Add(581577110);
                            SqlParameter tvpParam = command.Parameters.AddWithValue("@tvp", objectIds);
                            tvpParam.SqlDbType = SqlDbType.Structured;
                            tvpParam.TypeName = "dbo.tvp_Test";
                            int result = (int)command.ExecuteScalar();
                            Console.WriteLine("Result: {0}", result);
                        }
                    }
                }
    
                Console.WriteLine("Done.");
                Console.ReadLine();
            }
        }
    }
    

    Where the int's are the object_id of some tables in my test database.

    Friday, December 4, 2015 10:53 AM