Search SQL database with multiple filters

Question

I have the following code:

List<string> L1 = new List<string>();
int Id = 1;

using (SqlConnection conn = new SqlConnection())
{               
    conn.ConnectionString = "...";
    SqlCommand cmd = conn.CreateCommand();

    cmd.CommandText = "SELECT * FROM categories WHERE category_id = @Id";
    cmd.Parameters.Add(new SqlParameter("@Id", Id));
                                
    conn.Open();
                                
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        category = reader.GetString(1);
        L1.Add(category);
    }
}

This gives me the rows where "category_id" is equal to the 1.

I now want the SELECT statement to return rows where the "category_id" is equal to one of the values I give it.

So a simple SELECT statement would look something like this

SELECT * FROM categories WHERE category_id = 1 or category_id = 2
//returns all rows where "category_id" is equal to 1 or 2

The problem is that I wouldn't know the values to search for when setting up the SELECT statement. I would have the values in a string, list or something similar.

i tried using a foreach loop to change the value of "Id"

cmd.Parameters.Add(new SqlParameter("@Id", typeof(int)));
foreach (int val in Values)
{
    cmd.Parameters["@Id"].Value = val;
}

But this just returns the rows where "category_id" is equal to the last value int the array "Values".

I have tried to use the IN operator, but got an error

cmd.CommandText = "SELECT * FROM categories WHERE category_id IN (@Id)";
cmd.Parameters.Add(new SqlParameter("@Id", Values));

One final idea I had was using a loop to make a string from the value.

string ValueString = "0";
foreach (int val in Values)
{
    ValueString += ",";
    ValueString += Convert.ToString(val);
}

I have not tried this yet but I'm expecting it to give an error as the column is "category_id" has the data type "int".

Is there any way to search the SQL database with an array/list as parameter, so it gives every row where it equals one of the values in the array/list.

What is your DBMS? Sql Server supports [Table Valued Parameters](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters) for example. — Serg, Nov 29 '21 at 10:21

KevinM · Accepted Answer · 2021-12-04T23:20:16.020

The best way I have found to do this is to make a joined string with the values in the array/list.

string L1Joined = string.Join(",", L1);
//(L1: name of the array/list)

Then using the "IN" keyword and making the CommandText a formatted string.

So the CommandText looks like this:

cmd.CommandText = $"SELECT * FROM categories WHERE category_id IN ({L1Joined})";

Not sure if this is the most efficient way, but it works for my specific problem.

Buesche · Answer 2 · 2021-11-29T20:35:31.380

-1

You can use the "IN" Keyword for your Command:

SELECT * FROM categories WHERE category_id IN (1,2)

Your CommandText needs to look like this:

 cmd.CommandText = "SELECT * FROM categories WHERE IN (@Id)";

You can set the Parameter @ID like this:

cmd.Parameters.Add(new SqlParameter("@Id", string.Join(",", L1)));

Before you rund it you can simply add "1" and "2" to your L1-List and there you go.

edited Nov 29 '21 at 20:35

answered Nov 29 '21 at 10:25

Buesche

31
5

Using this method you get the following error : "Conversion failed when converting the nvarchar value '1,2,3' to data type int.". I was able to find the solution. using string.Join() and making the command text a formatted string. Then placing string.Join() inbetween { }. – KevinM Dec 03 '21 at 08:30
And another one that does not know how SQL parameters work. This way what happens is that it takes @Id AS ONE VALUE. You want to do that, you need ONE PARAMETER PER VALUE. – TomTom Dec 04 '21 at 23:15

score -1 · Answer 3 · answered Dec 03 '21 at 14:53

-1

cmd.CommandText = "SELECT * FROM categories WHERE IN (@Id)";

cmd.Parameters.Add(new SqlParameter("@Id", string.Join(",", L1)));

answered Dec 03 '21 at 14:53

srinithi R

206
1
5

That is NOT how parameters work. Regardless what you have in @Id, it will take it as ONE VALUE. – TomTom Dec 04 '21 at 23:14
This gives a conversion error because the parameter is a nvarchar and the column is an int – KevinM Dec 04 '21 at 23:15

Search SQL database with multiple filters

3 Answers3