C# sql查询中的最大参数数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/845931/
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
maximum number of parameters in sql query
提问by Mariusz
I do experiment with LINQ since some time. Typical method to enumerate through a collection and change some of its properties in my code would look like:
一段时间以来，我一直在尝试 LINQ。在我的代码中枚举集合并更改其某些属性的典型方法如下所示：
ATDataContext dc = new ATDataContext(Settings.connection_string);
int[] col = ListViewClass.getListViewSelectedPositionTags(listView);
try
{
foreach (var item in col)
{
var ctx = (from r in dc.MailingLists
where r.ID == item
select r).Single();
ctx.Excluded = 'Y';
ctx.ExcludedComments = reason;
}
dc.SubmitChanges();
}
Some time a have got an advice to do this by... seems like much smarter way:
有一段时间，有人建议这样做……似乎是更聪明的方法：
var ctx = from r in dc.MailingLists
where col.Contains(r.ID)
select r;
foreach (var item in ctx)
{
item.Excluded = 'Y';
item.ExcludedComments = reason;
}
dc.SubmitChanges();
Iit makes sense on so many levels and I love this solution. It's smart and faster than the first one.
它在很多层面上都有意义，我喜欢这个解决方案。它比第一个更聪明，速度更快。
I have used this solution in a production environment for some time.
我已经在生产环境中使用了这个解决方案有一段时间了。
What was my surprise after few weeks when searching an application log files and see this:
在搜索应用程序日志文件并看到以下内容几周后，我感到惊讶的是什么：
"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RCP request. The maximum is 2100."
“传入的表格数据流 (TDS) 远程过程调用 (RPC) 协议流不正确。此 RCP 请求中提供的参数太多。最大值为 2100。”
The LINQ to SQL converts where col.Contains(r.ID)to INclause looking something like:
WHERE ID IN (@p1, @p1, @p2 … )
LINQ to SQL 转换where col.Contains(r.ID)为IN看起来像这样的子句：
WHERE ID IN (@p1, @p1, @p2 ... )
The colcollection reached (in my case) more than 2100 elements and the query failed to perform. I have done some research on the problem and what I ended up is:
在col达到（对我来说）收藏超过2100件和查询未能履行。我对这个问题做了一些研究，结果是：
“… Maximum number of parameters in the sql query is 2100. There is more limitations, like the fact that the whole query string cannot be longer than 8044 characters.”
“...... sql 查询中的最大参数数为 2100。还有更多限制，例如整个查询字符串不能超过 8044 个字符。”
I have loved the second solution so much. I am so disappointed with these hard-coded limitations of the SQL Server.
我非常喜欢第二种解决方案。我对 SQL Server 的这些硬编码限制感到非常失望。
Did I miss something? Is there anything I can do to be able to use the “where col.Contains(r.ID)” version?
我错过了什么？我能做些什么才能使用“where col.Contains(r.ID)”版本？
Regards Mariusz
问候马里乌斯
ps. (I use Win XP, C# with LINQ and SQL 2005 Express).
附：（我使用 Win XP、C# 和 LINQ 和 SQL 2005 Express）。
采纳答案by Marc Gravell
The limits are hard-coded:
限制是硬编码的：
- Parameters per stored procedure 2,100
- Parameters per user-defined function 2,100
- 每个存储过程的参数 2,100
- 每个用户定义函数的参数 2,100
I wrote some code before that split the Containsquery into batches and combined the results... see here for more.
我之前写了一些代码，将Contains查询拆分成批次并合并结果......更多信息请参见此处。
