Passing Multiple Parameters In SQL IN Clause With SQL Command

As we know, using parameters is more secure, more readable, less error-prone, and negates the need to escape single quotes in text values.

Today, I am going to discuss about retrieving the records based on the values in a specific column in SQL database using IN Clause with sqlcommand.

Suppose, we have one table.

select * from tblemployee 

SQL Server

If we want to retrieve the data of "John, Rohan, Krist, Bronk, Peter" using SQL Query, then we can use

select * from tblemployee where ename in('John','Rohan','Krist','Bronk','Peter')

The output is given below.

SQL Server

But if we try this with sqlcommand like this -
  1. DataSet ds = new DataSet();  
  2. String strNames = "";  
  3. strNames = "John,Rohan,Krist,Bronk,Peter";  
  4.  SqlCommand cmd = new SqlCommand();  
  5. cmd.CommandText = "select * from tblemployee where ename in(@strNames)";  
  6. cmd.Parameters.AddWithValue("@strNames", strNames);  
  7. SqlDataAdapter da = new SqlDataAdapter();  
  8. da.SelectCommand = cmd;  
  9. da.SelectCommand.CommandTimeout = 0;  
  10. da.Fill(ds);  
We will get an empty dataset, because it considers all the names as one string.

SQL Server

To overcome this issue, we have to write the below logic.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Data;  
  7. using System.Data.SqlClient;  
  8. using System.Web.UI.WebControls;  
  9. public partial class _Default: System.Web.UI.Page {  
  10.     protected void Page_Load(object sender, EventArgs e) {  
  11.         DataSet ds = new DataSet();  
  12.         String strAppend = "";  
  13.         String strNames = "";  
  14.         int index = 1;  
  15.         String paramName = "";  
  16.         String[] strArrayNames;  
  17.         strNames = "John,Rohan,Krist,Bronk,Peter";  
  18.         strArrayNames = strNames.Split(',');  
  19.         SqlCommand cmd = new SqlCommand();  
  20.         foreach(String item in strArrayNames) {  
  21.             paramName = "@idParam" + index;  
  22.             cmd.Parameters.AddWithValue(paramName, item); //Making individual parameters for every name  
  23.             strAppend += paramName + ",";  
  24.             index += 1;  
  25.         }  
  26.         strAppend = strAppend.ToString().Remove(strAppend.LastIndexOf(","), 1); //Remove the last comma  
  27.         cmd.CommandText = "select * from tblemployee where ename in(" + strAppend + ")";  
  28.         ds = RetrieveSqlData(cmd);  
  29.     }  
  30.     public DataSet RetrieveSqlData(SqlCommand cmd) {  
  31.         DataSet ds = new DataSet();  
  32.         try {  
  33.             SqlConnection con = new SqlConnection();  
  34.             con.ConnectionString = "server=**********;UID=**;PWD=*******;DATABASE=Employee;";  
  35.             cmd.Connection = con;  
  36.             SqlDataAdapter da = new SqlDataAdapter();  
  37.             da.SelectCommand = cmd;  
  38.             da.SelectCommand.CommandTimeout = 0;  
  39.             da.Fill(ds);  
  40.             return ds;  
  41.         } catch (Exception ex) {  
  42.             return ds;  
  43.         }  
  44.     }  
Then, the output is -

SQL Server