How to Effectively Use Parameterized Queries with LIKE and IN Conditions in .NET?

Parameterized Queries with LIKE and IN Conditions

Using parameterized queries in .Net typically follows a specific pattern, as demonstrated in the example:

SqlCommand comm = new SqlCommand(@"
   SELECT * 
   FROM   Products 
   WHERE  Category_ID = @categoryid
", 
   conn);
comm.Parameters.Add("@categoryid", SqlDbType.Int);
comm.Parameters["@categoryid"].Value = CategoryID;

However, performing more complex queries with conditions like IN and LIKE can be challenging.

Implementing IN and LIKE Conditions

Consider the case where you have a list of category IDs stored as a comma-separated string and a product name potentially containing special characters. To construct a parameterized query for this scenario:

1. Break down the IN condition: Convert the comma-separated category ID string into an array of integers.

   int[] categoryIDs = Array.ConvertAll(CategoryIDs.Split(','), int.Parse);

2. Generate parameter names: Create a sequence of parameter names, e.g., @p0, @p1, @p2, and so on.

   string[] parameters = new string[categoryIDs.Length];
   for (int i = 0; i < categoryIDs.Length; i++)
   {
      parameters[i] = "@p" + i;
   }

3. Add parameters to the command: Add each parameter to the command with the corresponding category ID as its value.

   for (int i = 0; i < categoryIDs.Length; i++)
   {
      comm.Parameters.AddWithValue(parameters[i], categoryIDs[i]);
   }

4. Construct the IN condition: Join the parameter names into a comma-separated string within the IN clause of the query.

   WHERE Category_ID IN (" + string.Join(",", parameters) + ")

5. Handle the LIKE condition: Parameterize the LIKE condition by using a wildcard character and the input string.

   OR name LIKE @name

   where @name is a parameter added to the command with the input string as its value.

Putting it all together, the parameterized query becomes:

string Name = "someone";
int[] categoryIDs = new int[] { 238, 1138, 1615, 1616, 1617, 1618, 1619, 1620, 1951, 1952 };

SqlCommand comm = conn.CreateCommand();

string[] parameters = new string[categoryIDs.Length];
for(int i=0;i<categoryIDs.Length;i++)
{
   parameters[i] = "@p"+i;
   comm.Parameters.AddWithValue(parameters[i], categoryIDs[i]);
}
comm.Parameters.AddWithValue("@name",$"%{Name}%");
comm.CommandText = "SELECT * FROM Products WHERE Category_ID IN (";
comm.CommandText += string.Join(",", parameters) + ")";
comm.CommandText += " OR name LIKE @name";

This approach creates a fully parameterized query that addresses both IN and LIKE conditions with proper parameterization.

The above is the detailed content of How to Effectively Use Parameterized Queries with LIKE and IN Conditions in .NET?. For more information, please follow other related articles on the PHP Chinese website!