SqlDataAdapter gives parameter name invalid error when setting batch size

batch-processing, c#, sqldataadapter

Solution

After decompiling the SqlDataAdapter and using the stack trace, I came to this line.

if (!SqlCommandSet.SqlIdentifierParser.IsMatch(sqlParameter.ParameterName))
    throw ADP.BadParameterName(sqlParameter.ParameterName);

So it turns out, normally you can omit the @ from a SqlParameter name, but when doing this batch, it requires it. Very strange. The correct line becomes

command.Parameters.Add("@Id", SqlDbType.Int).SourceColumn = "Id";

Problem

I'm trying out the batch inserting/updating of SqlDataAdapter. When I set UpdateBatchSize = 1, it works, but setting it to 2 gives the exception "Specified parameter name 'Id' is not valid.". ``` using (var sqlDataAdapter = new SqlDataAdapter { UpdateBatchSize = 2 }) using (var connection = new SqlConnection("Data Source=server;Initial Catalog=DB;Integrated Security=True")) using (var command = new SqlCommand("INSERT INTO Test (Id) VALUES (@Id)", connection) { UpdatedRowSource = UpdateRowSource.None }) { command.Parameters.Add("Id", SqlDbType.Int).SourceColumn = "Id"; sqlDataAdapter.InsertCommand = command; var table = new DataTable("Test"); table.Columns.Add("Id"); table.Rows.Add(1); table.Rows.Add(2); sqlDataAdapter.Update(table); } ```

Original source