Want to pass list in sql query

Question

_{Thursday, December 3, 2015 6:26 PM}

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
 

All replies (6)

_{Thursday, December 3, 2015 7:12 PM ✅Answered | 1 vote}

Use a table-valued parameter.

---

_{Friday, December 4, 2015 9:58 AM ✅Answered}

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

---

_{Thursday, December 3, 2015 7:31 PM}

how can we implement this using table values parameter in my context?

---

_{Thursday, December 3, 2015 9:22 PM}

Follow the link. It contains a complete example.

---

_{Friday, December 4, 2015 2:27 AM}

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 10:53 AM | 1 vote}

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.