Параметризованные запросы с условиями LIKE и IN

Question

Параметризованные запросы в.Net всегда выглядят так в примерах:

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

Но я врезаюсь в кирпичную стену, пытаясь сделать следующее:

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;

куда

• CategoryIDs - это разделенный запятыми список чисел "123 456 789" (без кавычек)
• Имя - это строка, возможно, с одинарными кавычками и другими плохими символами

Какой правильный синтаксис для этого?

Answer 1

Допустим, у вас есть идентификаторы вашей категории в целочисленном массиве, а Name - это строка. Хитрость заключается в том, чтобы создать текст команды, который позволит вам ввести все идентификаторы вашей категории в качестве отдельных параметров и создать нечеткое совпадение для имени. Чтобы сделать первое, мы используем цикл для создания последовательности имен параметров от @p0 до @pN-1, где N - количество идентификаторов категорий в массиве. Затем мы создаем параметр и добавляем его в команду со связанным идентификатором категории в качестве значения для каждого именованного параметра. Затем мы используем конкатенацию по имени в самом запросе, чтобы разрешить нечеткий поиск по имени.

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

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

Это полностью параметризованный запрос, который должен сделать вашего администратора базы данных счастливым. Я подозреваю, что, поскольку они являются целыми числами, хотя для безопасности не составит большого труда просто создать текст команды непосредственно со значениями, в то же время параметризуя имя. Если идентификаторы вашей категории находятся в строковом массиве, просто разбейте массив на запятые, преобразуйте каждый в целое число и сохраните его в массиве целых чисел.

Примечание: я говорю "массив" и использую его в примере, но он должен работать для любой коллекции, хотя ваша итерация, вероятно, будет отличаться.

Оригинальная идея от http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9

Answer 2

Вам нужно "%" в значении параметра sql.

SqlCommand comm = new SqlCommand("SELECT * FROM Products WHERE Category_ID IN (@categoryid1, @categoryid2) OR name LIKE @name", conn);
comm.Parameters.Add("@categoryid1", SqlDbType.Int);
comm.Parameters["@categoryid1"].Value = CategoryID[0];
comm.Parameters.Add("@categoryid2", SqlDbType.Int);
comm.Parameters["@categoryid2"].Value = CategoryID[1];
comm.Parameters.Add("@name", SqlDbType.Int);
comm.Parameters["@name"].Value = "%" + Name + "%";

Answer 3

Этот подход не будет работать. Период.

Предложение IN ожидает сам список параметров, поэтому, когда вы привязываете к нему один параметр, у вас есть возможность передать одно значение.

Создайте строку оператора динамически с точным количеством отдельных заполнителей предложения IN, которые вы намереваетесь передать, а затем добавьте к ним параметры и свяжите значения в цикле.

Answer 4

Не уверен, что это правильный путь, но я сделал это раньше

список templist = новый список

comm.Parameters.Add("@categoryids", SqlDbType.varchar); comm.Parameters["@categoryids"].value = string.join(",",templist.toarray())