Click here to Skip to main content
14,734,454 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to filter data by using check boxes with the help of array or list, compare data which is stored in array or list with co.

What I have tried:

i tried this code but it is not working perfectly, i mean data is present in array/list but i have no idea how to compare of this data with table.

[HttpPost]
       public JsonResult Dummmyy1(RegisterModel model)

       {

           string[] dum = { "ddd", "aaa", "fff", "bbb" };
          string[] data = model.array;
           var sqlcommand = new SqlCommand();


           List<RegisterModel> consultantList = new List<RegisterModel>();  // creating list of model.
           using (DBClass context = new DBClass())
           {




              DataTable dt = context.getData("Select * from emp_detail where EState in (@PARAM)", CommandType.Text);
               sqlcommand.Parameters.Add(new SqlParameter(@"@PARAM", String.Join(",", data)));
Posted
Updated 10-Nov-20 6:11am

1 solution

You can't pass multiple values to a single parameter and then use that parameter with an IN clause.

The simplest solution is to pass multiple parameters:
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand())
{
    command.Connection = connection;
    command.CommandType = CommandType.Text;
    
    var parameterNames = new List<string>(data.Count);
    foreach (string item in data)
    {
        string name = "@p" + command.Parameters.Count;
        command.Parameters.AddWithValue(name, item);
    }
    
    command.CommandText = string.Format(
        "SELECT * FROM emp_detail WHERE EState In ({0})",
        string.Join(", ", parameterNames));
    
    var dt = new DataTable();
    var da = new SqlDataAdapter(command);
    da.Fill(dt);
    
    ... DO SOMETHING WITH THE DATA HERE ...
}
There are other approaches available - for example:
Arrays and Lists in SQL Server (The Full Story)[^]
Using Table-Valued Parameters in SQL Server and .NET[^]
   
Comments
Member 14743579 10-Nov-20 12:24pm
   
DataTable dt = context.getData("Select * from emp_detail where EState = any(" +data+ ")", CommandType.Text);

In the above code data is the name of array, can i compare the array with sql in c# code
Richard Deeming 11-Nov-20 3:36am
   
That code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900