带有2000多个参数的ADO.NET批处理插入 [英] ADO.NET Batch Insert with over 2000 parameters

问题描述

我正在使用企业库，但是想法是一样的.我有一个SqlStringCommand和sql

I'm using Enterprise library, but the idea is the same. I have a SqlStringCommand and the sql

是使用StringBuilder构造的，形式为

is constructed using StringBuilder in the forms of

"insert into table (column1, column2, column3) values (@param1-X, @param2-X, @parm3-X)"+" " 

其中"X"代表大约700行的"for循环"

where "X" represents a "for loop" about 700 rows

StringBuilder sb = new StringBuilder();
for(int i=0; i<700; i++)
{
   sb.Append("insert into table (column1, column2, column3) values (@param1-"+i+", @param2-"+i, +",@parm3-"+i+") " );
}

接着构造一个命令对象，将所有带有值的参数注入其中.

followed by constructing a command object injecting all the parameters w/ values into it.

从本质上讲，700个带有3个参数的行，对于这个一个sql"语句，我最终得到2100个参数.

Essentially, 700 rows with 3 parameters, I ended up with 2100 parameters for this "one sql" Statement.

运行了好几天，突然我收到了这个错误

It ran fine for about a few days and suddenly I got this error

===============================================================

A severe error occurred on the current command. The results, if any, should be discarded.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) 
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) 
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) 
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) 
at System.Data.SqlClient.SqlCommand.InternalExecuteNon

任何指针都将不胜感激.

Any pointers are greatly appreciated.

推荐答案

不确定，但请查看以下内容:

Not sure, but check this out: Hitting the 2100 parameter limit (SQL Server) when using Contains()

这篇关于带有2000多个参数的ADO.NET批处理插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！