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. }
  45. }
Then, the output is -

SQL Server