参数化查询与LIKE和IN条件 [英] Parameterized Queries with LIKE and IN conditions

问题描述

参数化查询在.net总是看起来像这样的例子：

Parameterized Queries in .Net always look like this in the examples:

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

但我跑成砖墙努力做到以下几点：

But I'm running into a brick wall trying to do the following:

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（不带引号）
• 名称是一个字符串，可能与单引号和其他不良字符

什么是正确的语法呢？

推荐答案

假设你有一个整数数组的类别ID和名称是一个字符串。诀窍是创建命令文本，以便您可以进入所有的类ID作为单独的参数，并构建模糊匹配的名称。做前者，我们使用一个循环通过@ PN-1，其中N是类别ID阵列中的数量来构造参数名@ P0的序列。然后，我们构造了一个参数，它与相关的类别ID为每个命名参数的值添加到命令。然后，我们用串联上的名称与查询本身，允许在名称的模糊搜索。

Let's say that you have your category ids in an integer array and Name is a string. The trick is to create the command text to allow you to enter all of your category ids as individual parameters and construct the fuzzy match for name. To do the former, we use a loop to construct a sequence of parameter names @p0 through @pN-1 where N is the number of category ids in the array. Then we construct a parameter and add it to the command with the associated category id as the value for each named parameter. Then we use concatenation on the name in the query itself to allow the fuzzy search on name.

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 + '%'";

这是一个完全参数化查询，应该让你的DBA高兴。我怀疑，因为这些都是整数，但它不会是太大的安全风险，只是为了直接与价值观构建的命令文本，同时仍然参数化的名称。如果你的类ID是一个字符串数组，只是分裂逗号的数组，每个转换为整数，并将其存储在整数数组。

This is a fully parameterized query that should make your DBA happy. I suspect that since these are integers, though it would not be much of a security risk just to construct the command text directly with the values, while still parameterizing the name. If your category ids are in a string array, just split the array on commas, convert each to an integer, and store it in the integer array.

注意：我说的数组并使用它的例子，但它应该为任何集合，虽然您的迭代可能会有所不同。

Note: I say array and use it in the example, but it should work for any collection, although your iteration will probably differ.

从<一个原始的想法href="http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9">http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9

这篇关于参数化查询与LIKE和IN条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！