There is an issue with the .NET 6 SqlDataAdapter class. Issue affects batch updates with output parameters.

[slawomir-zurek]

I recently encountered a problem while migrating a solution based on SQLDataAdapter from .NET Framework 4.8 to .NET 6. The issue concerns, in my opinion, the incorrect way of passing data types during data saving with the UpdateBatchSize setting > 1 - the data types specified in SqlParameter are not preserved, which causes validation errors for parameters 'String[0]: the Size property has an invalid size of 0.'.

Below I quote a thread (from last year) about this exact problem, which unfortunately has not been escalated to an official report. I believe this is quite a serious issue.
Best regards
Sławek

dotnet/runtime#80950 :

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.

Originally posted by @rgb4321 in dotnet/runtime#80950

[kf-gonzalez]

@slawomir-zurek Can you try if this is as issue as well by running it against Microsoft.Data.SqlClient?