Passing Multiple Parameters In SQL IN Clause With SQL Command

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

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 



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.



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 -