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.
Advertisement
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.