如何使用“WHERE [columnname] IN [values]”语句运行包含字符串值列表的 SQL 查询ASP.NET 中的格式？

Question

我有一个在 ASP.NET 页面中运行的 SQL 查询。最终解析的 SQL 需要包含 WHERE [columnname] IN [values] 格式的字符串值列表。例如，最终查询可能如下所示：

SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE IN ('ABC','DEF','GHI','JKL', /* etc */);

但是，WHERE 子句中的字符串值需要是动态的。通常我使用参数化查询来使我的代码方便且安全，因此从概念上讲我想做这样的事情：

String[] productCodes = { "ABC", "DEF", "GHI", "JKL" };
SqlCommand cmd = "SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE IN (@ProductCodes)";
cmd.Parameters.Add("@ProductCodes", productCodes);

但是，.NET 中似乎不存在这种功能。我应该如何实施这个？我可以在数组上使用 foreach 循环，并使用单个值作为每个值的参数运行查询，但数组中可能有一百个左右不同的值，而且单独查询它们似乎效率非常低。

我读过另一个问题，其中有人提出了强类型 int 参数的解决方案，但是当与字符串值一起使用时，该方法会让我对 SQL 注入感到紧张，特别是因为客户端很可能能够影响输入值。

您将如何实现此查询功能？

编辑数据库详细信息：

数据库是 SQL Server 2005。很抱歉忘记提及这一点。

Answer 1

创建基本 sql 语句作为一种格式，并动态添加参数，然后在循环中设置值。

String[] productCodes = { "ABC", "DEF", "GHI", "JKL" };
string sqlFormat = "SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE IN ({0})";
var @params = productCodes.Select((id, index) => String.Format("@id{0}", index)).ToArray();
var sql = String.Format(sqlFormat, string.Join(",", @params));

using(var cmd = new DbCommand(sql))
{
    for (int i = 0; i < productCodes.Length; i++)
        cmd.Parameters.Add(new Parameter(@params[i], DbType.String, productCodes[i]));
    // execute query
}

Answer 2

只是一个想法：

String[] productCodes = { "ABC", "DEF", "GHI", "JKL" };
SqlCommand cmd = "SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE IN ("; 

for (int i=0;i<productCodes.Length;i++) {
  cmd.CommandText += "@" + productCodes[i] + ",";
  cmd.Parameters.Add("@" + productCodes[i], productCodes[i]);
}
cmd.CommandText = cmd.CommandText.SubString(0, cmd.CommandText.Length-1);
cmd.CommandText += ");"

可能不是最好的方法，但我想这就是我尝试的方法。

Answer 3

根据记录，“不单独”查询数组中的大约一百个值也可能效率低下（尽管不像与 SQL Server 进行一百次左右的往返那样低效）。但是，参数化查询会有所帮助，因为您可以准备相同的查询并执行它多次。存储过程可能会更好。

您有机会使用 Linq，或者您是 .NET-3.5 之前的版本吗？

如果您不能使用 Linq，并且您绝对必须走这条路，请尝试以下操作：

SqlCommand cmd = "SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE = @ProductCode";
cmd.Prepare();
List<string> results;
foreach (string code in productCodes)
{
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@ProductCodes", DbType.VarChar).Value = code;
    cmd.ExecuteQuery();
    // Add code here to add the returned values to the results list.  It's been
    // a while since I've used ADO.NET, and I don't have time to look it up
    // at the moment...
}

然后，您就得到了结果列表。

Answer 4

您正在寻找表值参数。但是，这些不是'直到 ASP.NET 被广泛采用之后，SQL Server 才可用，因此 ASP.NET 对它们的支持是有限的。

我建议将其视为构建购物车。用户将商品添加到购物车，并且有时您希望显示购物车中的所有商品。在这种情况下，自然的解决方案是购物车本身位于数据库表中。您无需将卡片中的项目下拉为“IN (?)”指令的一部分，而是将其构建为指令的子查询：“WHERE X IN (SELECT X FROM ShoppingCart WHERE UserID= @ UserID AND SessionKey= @SessionKey)”。即使您添加了数百个项目，用户的速度也只会如此之快，并且每次插入的负载分布得相当均匀。

当然，您可能正在构建购物车以外的其他东西，但是您的查询几乎总是属于以下三个类别之一：

1. 列表中的项目是由用户手动选择的，在这种情况下，您仍然可以拥有每个项目选择结果会产生一个新的数据库记录，该记录又可以在子查询中使用
2. 它是数据库中已经可用的数据，在这种情况下，您应该仍然能够使用子查询（如果不能，那么可能是时候了在某处添加“类别”列，以便您可以）。
3. 它是硬编码到您的应用程序中的数据，在这种情况下，您也可以将其编码到您的查询中。

罕见的例外是当查询由另一个机器源触发时，或者您可能还有很多不愿意重新编写的代码。努力使这成为可能。
因此，如果出于某种原因，这种方法不适合您，可以在此处找到有关 sql server 主题的标准文章（包括一些替代方案）：
http://www.sommarskog.se/arrays-in-sql.html

这篇文章确实是该主题的标准著作，非常值得您花时间。