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 -

DataSet ds = new DataSet();
String strNames = "";
strNames = "John,Rohan,Krist,Bronk,Peter";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from tblemployee where ename in(@strNames)";
cmd.Parameters.AddWithValue("@strNames", strNames);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.SelectCommand.CommandTimeout = 0;
da.Fill(ds);

We will get an empty dataset, because it considers all the names as one string.

To overcome this issue, we have to write the below logic.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
public partial class _Default: System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e) {
DataSet ds = new DataSet();
String strAppend = "";
String strNames = "";
int index = 1;
String paramName = "";
String[] strArrayNames;
strNames = "John,Rohan,Krist,Bronk,Peter";
strArrayNames = strNames.Split(',');
SqlCommand cmd = new SqlCommand();
foreach(String item in strArrayNames) {
paramName = "@idParam" + index;
cmd.Parameters.AddWithValue(paramName, item); //Making individual parameters for every name
strAppend += paramName + ",";
index += 1;
}
strAppend = strAppend.ToString().Remove(strAppend.LastIndexOf(","), 1); //Remove the last comma
cmd.CommandText = "select * from tblemployee where ename in(" + strAppend + ")";
ds = RetrieveSqlData(cmd);
}
public DataSet RetrieveSqlData(SqlCommand cmd) {
DataSet ds = new DataSet();
try {
SqlConnection con = new SqlConnection();
con.ConnectionString = "server=**********;UID=**;PWD=*******;DATABASE=Employee;";
cmd.Connection = con;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.SelectCommand.CommandTimeout = 0;
da.Fill(ds);
return ds;
} catch (Exception ex) {
return ds;
}
}
}

Then, the output is -

SQL Server

3 Comments

Join the conversation! Your thoughts help the community grow.

Hadshana Kamalanathan8y ago
Thank you for sharing information
jyoti singhal8y ago
My problem is not solve. i can not understand value of @idparam. where is this variable defined?
- Prashanth J8y ago
  No need to declare the parameter '@idParam'. For each and every loop count we generate the new string and assigned it to string ' paramName'.