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

Parameterized Queries with LIKE and IN Conditions

Parametrized queries in .Net typically involve using specific parameter names and adding values to these parameters. However, when dealing with conditions like IN or LIKE, which may require multiple or dynamic parameter values, the syntax can become more complex.

Let's address the specific question:

Query:

SELECT * 
FROM Products 
WHERE Category_ID IN (@categoryids) 
OR name LIKE '%@name%'

Parameters:

• CategoryIDs: A comma-separated list of numbers without quotes.
• Name: A string that may contain special characters.

Modified Syntax:

To create a fully parameterized query, we need to dynamically construct the parameter names and values for the IN condition. We can achieve this by using a loop:

int[] categoryIDs = ...;
string Name = ...;

SqlCommand comm = ...;

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}%");

Modified Query Text:

Concatenate the generated parameter names into the IN condition:

WHERE Category_ID IN (@p0, @p1, ...)

The final query text would look like:

SELECT * 
FROM Products 
WHERE Category_ID IN (@p0, @p1, ...) 
OR name LIKE @name

This approach ensures that each CategoryID is parameterized individually and that the LIKE condition is also parameterized with the appropriate pattern syntax. By fully parameterizing the query, you prevent SQL injection and improve performance.

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