ADO.NET Batch Insert with over 2000 parameters

Question

Hello all,

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

is constructed using StringBuilder in the forms of

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

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.

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.

Answer 1

Not sure, but check this out: http://stackoverflow.com/questions/656167/hitting-the-2100-parameter-limit-sql-server-when-using-contains

Answer 2

Have a look at SQLBulkCopy. It will probably offer a better solution to your problem than your current approach.

Answer 3

I think you can resolve this one of many ways

1. Batch inserts for every 100 records

2. Do each insert as it's own command with a transaction wrapped around all inserts.

3. Use SQL Bulk Copy

4. Use SSIS for this: ETL tasks are best done using ETL tools. Since you are using SQL Server, you can easily load up data files into SQL Server using SSIS. You can build an SSIS package and execute it from within your C# code.