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

Parameterized Queries with LIKE and IN Conditions

When using parameterized queries in .NET, the examples typically demonstrate using them with simple scenarios, like comparing a single column value to a parameter. However, things can get more complex when dealing with conditions like IN and LIKE that involve multiple parameters or non-standard characters.

Query with IN Condition

Let's consider a query that searches for products based on category ID, where the category ID can be a comma-separated list of integers.

SELECT * 
FROM   Products 
WHERE  Category_ID IN (@categoryids) 

To parameterize this query using an integer array CategoryIDs, we need to loop through the array and create a parameter for each category ID.

int[] categoryIDs = new int[] { 238, 1138, 1615, 1616, 1617 };

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.CommandText = "SELECT * FROM Products WHERE Category_ID IN (";
comm.CommandText += string.Join(",", parameters) + ")";

Query with LIKE Condition

Now let's add a LIKE condition that searches for products with names that contain a specific string, possibly with special characters.

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

To parameterize this query, we create a parameter for the Name value and use concatenation to search for the name within the query.

string Name = "someone";

SqlCommand comm = conn.CreateCommand();

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";

By using this approach, we can create fully parameterized queries that handle both IN and LIKE conditions with different parameter types, ensuring the security and performance of our database access.

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