SQL 带有 LIKE 和 IN 条件的参数化查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/303149/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parameterized Queries with LIKE and IN conditions
提问by Tom Ritter
Parameterized Queries in .Net always look like this in the examples:
.Net 中的参数化查询在示例中始终如下所示：
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;
Where
在哪里
- CategoryIDs is a comma separated list of numbers "123,456,789" (without quotes)
- Name is a string, possibly with single quotes and other bad characters
- CategoryIDs 是逗号分隔的数字列表“123,456,789”（不带引号）
- Name 是一个字符串，可能带有单引号和其他坏字符
What's the right syntax for this?
什么是正确的语法？
回答by tvanfosson
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.
假设您将类别 ID 放在一个整数数组中，而 Name 是一个字符串。诀窍是创建命令文本以允许您输入所有类别 ID 作为单独的参数并构建名称的模糊匹配。为了实现前者，我们使用循环来构造参数名称@p0 到@pN-1 的序列，其中 N 是数组中类别 id 的数量。然后我们构造一个参数并将其添加到命令中，并使用关联的类别 id 作为每个命名参数的值。然后我们在查询本身的名称上使用串联，以允许对名称进行模糊搜索。
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";
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.
这是一个完全参数化的查询，应该会让您的 DBA 满意。我怀疑由于这些是整数，尽管直接使用值构造命令文本并同时仍然参数化名称不会有太大的安全风险。如果您的类别 id 在字符串数组中，只需将数组拆分为逗号，将每个数组转换为整数，然后将其存储在整数数组中。
Note:I say array and use it in the example, but it should work for any collection, although your iteration will probably differ.
注意：我说数组并在示例中使用它，但它应该适用于任何集合，尽管您的迭代可能会有所不同。
Original idea from http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9
来自http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9 的原创想法
回答by TcKs
You need "%" in value of sql parameter.
您需要 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 + "%";
回答by Tomalak
This approach will not work. Period.
这种方法是行不通的。时期。
The IN clause expects a list of parameters itself, so when you bind oneparameter to it, you have the chance to pass in onevalue.
IN 子句本身需要一个参数列表，因此当您将一个参数绑定到它时，您就有机会传入一个值。
Build your statement string dynamically, with the exact amount of individual IN clause placeholders you intend to pass in, and then add parameters and bind values to them in a loop.
使用您打算传入的确切数量的单个 IN 子句占位符动态构建您的语句字符串，然后在循环中添加参数并将值绑定到它们。
回答by B.A.Hammer
not sure if this is the right way but it is a way I have done it in the Before
不确定这是否是正确的方式，但这是我在之前所做的一种方式
list templist = new list
列表 templist = 新列表
comm.Parameters.Add("@categoryids", SqlDbType.varchar); comm.Parameters["@categoryids"].value = string.join(",",templist.toarray())
comm.Parameters.Add("@categoryids", SqlDbType.varchar); comm.Parameters["@categoryids"].value = string.join(",",templist.toarray())
