SqlDataAdapter.Update - output parameters broken

[rgb4321]

There is an issue with the .NET 6 SqlDataAdapter class (and possibly .NET 5/.NET Core) that was not present in .NET Framework 4.8. The issue affects batch updates with output parameters. Consider the following code:

table.Columns.Add("Id", typeof(int));
table.Rows.Add(new object[] { 1 });
table.Rows.Add(new object[] { 2 });

SqlConnection connection = new SqlConnection("<insert connection string here>");
connection.Open();

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.UpdateBatchSize = 2;
adapter.InsertCommand = new SqlCommand("<insert stored procedure name here>");
adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
adapter.InsertCommand.Connection = connection;
adapter.InsertCommand.Parameters.Add("@id", SqlDbType.Int, 0, "Id"). Direction = ParameterDirection.Output;

adapter. Update(table);

When the above code runs under .NET Framework 4.8 it completes successfully. When the above code runs under .NET 6, the following exception is thrown on the last line:

  HResult=0x80131509
  Message=String[0]: the Size property has an invalid size of 0.
  Source=System.Data.Common
  StackTrace:
   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
   at AdoNetIssueNet6.Program.Main(String[] args) in D:\Source\DOTNET\TestProjects\AdoNetIssueNet6\Program.cs:line 26

Note that the call to SqlDataAdapter.Update throws the above exception prior to actually calling the stored procedure. Besides the initial call to SqlConnection.Open, SQL Profiler doesn’t show any other activity from the client.

I eventually determined that .NET is somehow switching the SqlDbType from Int (or BigInt or probably every other type) to NVarChar. So one workaround is to specify a Size that can accommodate the max string length for the intended data type. A better workaround that doesn't require converting NVarChar back to the intended data type is to change the parameter direction from Output to InputOutput.

Any efforts to address this issue would be greatly appreciated.

[krjaction50]

We are having the exact same issue porting our .NET 4.8 framework code to .NET 6. Is there any workaround to this issue. It's a real show stopper for us.

[rgb4321]

Hi, I provided a couple workarounds at the end of my post but I agree that it's a pretty serious bug that should be fixed. Surprised you're the first person to comment. If you could up-vote my post it might help bring more attention to the issue. :-)

[krjaction50]

Yes we are right in the middle of a port from .NET 4.8 to .NET 6 and all was going well until this. We use UpdateBatchSize = 0 and UpdateRowSource.None to do batch inserts when we do not care about the ID returned from insert proc. We use UpdateBatchSize = 1 when we are doing single inserts where we need the ID back from the insert proc to use for other table inserts. We use a DAL that has this all built in to work like this. Luckily we have the source code so I will try some of your workarounds. This is a huge behavior change so I am too surprised more people have not ran into this.

[krjaction50]

The direction InputOutput workaround didn't work for me. Same exception. What did work was leaving out ID OUTPUT parameter from the parameters collection when UpdateBatchSize != 1. This is the case where we do not care about the IDs returned from the inserts when doing bulk inserts from the insert stored proc. Since this logic was located in the DAL central code location, it was easy to implement. More testing required but it looks good so far.

[slawomir-zurek]

Unfortunately, after a year, the error still exists and indeed interferes with migrating our solutions to newer platforms. We will work around the problem by trying to conditionally set UpdateBatchSize to 1 for newer runtimes. But this is clearly performance degradation.

[hez2010]

I would suggest you opening an issue for this instead of a discussion, otherwise related people won't be tagged.