Got "Specified parameter name 'Parameter1' is not valid." when doing batch update with SqlDataAdapter #26024
Description
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]
GOHere 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;
}
}