How to Parameterize LIKE and IN Conditions in .NET SQL Queries?

Parameterized Queries with LIKE and IN Conditions

In the world of .NET programming, parameterized queries are typically straightforward to set up. However, difficulties arise when dealing with complex conditions involving multiple values.

Consider the following query that includes both an IN condition and a LIKE condition:

SqlCommand comm = new SqlCommand(@"
   SELECT * 
   FROM   Products 
   WHERE  Category_ID IN (@categoryids) 
      OR  name LIKE '%@name%'
", 
   conn);
comm.Parameters.Add("@categoryids", SqlDbType.Int);
comm.Parameters["@categoryids"].Value = CategoryIDs;
comm.Parameters.Add("@name", SqlDbType.Int);
comm.Parameters["@name"].Value = Name;

In this case, CategoryIDs is a comma-separated list of numbers, and Name is a string that may contain special characters. The challenge lies in parameterizing these values correctly.

Solution

To tackle this issue, we construct a sequence of parameter names @p0 through @pN-1, where N is the number of category IDs in the array. We then create parameters for each name and assign the corresponding category ID as the value.

For the LIKE condition, we use string concatenation to build a fuzzy search expression containing @name.

Here's an example to illustrate the process:

string Name = "someone";
int[] categoryIDs = new int[] { ... };

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 fully parameterized query ensures security and flexibility in handling complex conditions. While the example uses arrays, the technique is applicable to any collection of values.

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