Got "Specified parameter name 'Parameter1' is not valid." when doing batch update with SqlDataAdapter

[xiaoyumu]

Hi guys,

I have recently using the System.Data.SqlClient of version 4.5.0-preview2-26406-04 to test if the batch update is supported in the latest build (Accoding to (https://docs.microsoft.com/en-us/dotnet/api/system.data.common.dbdataadapter.updatebatchsize?view=netcore-2.1#System_Data_Common_DbDataAdapter_UpdateBatchSize)) But if I set the UpdateBatchSize property of the SqlDataAdapter to a value lager than 1, like 2 or 10, an exception will throw with following message and stack trace. If I disable batch update by set UpdateBatchSize to 1 or just leave it unset using its default value, the code works (but without batching).

Exception: Specified parameter name 'Parameter1' is not valid.
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)

The program is running at a Win10Pro box with Visual Studio 2017 Community Edition.

Microsoft Visual Studio Community 2017
Version 15.5.6
VisualStudio.15.Release/15.5.6+27130.2027
SDK: Microsoft.NETCore.App Version: 2.1.0-preview2-26406-04

Project Target Framework

<PropertyGroup>
    <TargetFramework>netcoreapp2.1</TargetFramework>
</PropertyGroup>

Assuming that there is a SQL Server instance running at localhost with a database named Demo, and there is a table named BatchDemoTable.

-- Create Table
USE [Demo]
GO
 
CREATE TABLE [dbo].[BatchDemoTable](
	[TransactionNumber] [int] IDENTITY(1,1) NOT NULL,
	[Level] [nvarchar](50) NOT NULL,
	[Message] [nvarchar](500) NOT NULL,
	[EventTime] [datetime] NOT NULL,
 CONSTRAINT [PK_BatchDemoTable] PRIMARY KEY CLUSTERED 
(
	[TransactionNumber] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, 
FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

Here is the full code that always generates this exception, it basically trying to insert multiple entities into a table which I want to do it in batch.

class Program
{
    static void Main(string[] args)
    {
        try
        {
            ExecuteNonQueries();
            Console.WriteLine("Succeeded.");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            Console.WriteLine(ex.StackTrace);
        }

        Console.ReadKey();
    }

    public class EventInfo
    {
        public string Level { get; set; }
        public string Message { get; set; }
        public DateTime EventTime { get; set; }

        public EventInfo()
        {
            EventTime = DateTime.Now;
        }
    }

    public static void ExecuteNonQueries()
    {
        var entities = new List<EventInfo>
        {
            new EventInfo {Level = "L1", Message = "Message 1"},
            new EventInfo {Level = "L2", Message = "Message 2"},
            new EventInfo {Level = "L3", Message = "Message 3"},
            new EventInfo {Level = "L4", Message = "Message 4"},
        };

        var connectionString = "Server=Localhost;DataBase=Demo;Integrated Security=SSPI;";
        var sql = "INSERT INTO dbo.BatchDemoTable(Level, Message, EventTime)  VALUES(@Level, @Message, @EventTime)";

        using (var connection = new SqlConnection(connectionString))
        {
            var adapter = new SqlDataAdapter();
            var cmd = new SqlCommand(sql, connection);
            cmd.Parameters.Add(new SqlParameter("@Level", SqlDbType.NVarChar, 50, "Level"));
            cmd.Parameters.Add(new SqlParameter("@Message", SqlDbType.NVarChar, 500, "Message"));
            cmd.Parameters.Add(new SqlParameter("@EventTime", SqlDbType.DateTime, 0, "EventTime"));
            cmd.UpdatedRowSource = UpdateRowSource.None;

            adapter.InsertCommand = cmd;
            adapter.UpdateBatchSize = 2;

            adapter.Update(ConvertToTable(entities));
        }
    }

    private static DataTable ConvertToTable(List<EventInfo> entities)
    {
        var table = new DataTable(typeof(EventInfo).Name);

        table.Columns.Add("Level", typeof(string));
        table.Columns.Add("Message", typeof(string));
        table.Columns.Add("EventTime", typeof(DateTime));

        foreach (var entity in entities)
        {
            var row = table.NewRow();
            row["Level"] = entity.Level;
            row["Message"] = entity.Message;
            row["EventTime"] = entity.EventTime;
            table.Rows.Add(row);
        }

        return table;
    }
}

[karelz]

@xiaoyumu did it work ok on .NET Core 2.0? Can you please post minimal repro code? (to avoid confusion)
Also, I assume it fails always for you, it is not an intermittent failure. Is that correct?

[xiaoyumu]

@karelz Thanks for the reply, I've updated the description at above with full code (sorry about the code fragment earlier :) ). Yes, It fails always.
And on .NET Core 2.0, with System.Data.SqlClient version 4.4.3, it simply throw an unsupported exception like below:
Exception Message: Specified method is not supported.
Stack Trace:

   at System.Data.Common.DbDataAdapter.TerminateBatching()
   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 ConsoleApp1.Program.ExecuteNonQueries()

[danmoseley]

@keeratsingh, @afsanehr, @David-Engel could you please evaluate whether this is needed for 2.1?

[AfsanehR-zz]

Hi @xiaoyumu, I am able to repro the issue. Will update you here with more progress. Thanks!

[karelz]

@keeratsingh @saurabh500 do you consider it 2.2 only, or is it blocking 2.1? (I didn't see any explanation for the move to 2.2)

[David-Engel]

Batch update was not promised for 2.1. The feature/customer request which brought this code over was satisfied and the customer who requested it is happy with the functionality (they are not using batch update) so this issue does not need to delay 2.1.

[wengxk]

Today i got the same problem. It does not seem to be resolve.

[David-Engel]

@wengxk The fix only went into the master branch and was not ported to 2.x. The next scheduled release out of master is 3.0 which appears to be scheduled for Q1 2019: https://github.com/dotnet/core/blob/master/roadmap.md#upcoming-ship-dates

[sdrapkin]

Thx for fixing this one. The "4.7.0-preview" on Nuget includes these fixes.

TinyORM will be coming to NetStandard2.0 with full batching support now.

[dbarwikowski]

Same issue in .net framework 4.6.1