Search SQL database with multiple filters

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.

Answer

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.

Advertisement

Answer