使用ADO.NET的批处理操作

Question

我正在尝试使用ADO.NET进行批处理插入。到目前为止，我发现使用SqlDataAdapter可能是一种方法。

我已经创建了包含一个表、三个列和存储过程的数据库，它将把行插入到这个表中。

表PropertyTable

Name: VARCHAR(10)
Number: INT
IsActive: BIT

存储过程InsertPropertyTable

CREATE PROCEDURE InsertPropertyTable
    @Par1 VARCHAR(10),
    @Par2 INT,
    @Par3 BIT
AS
BEGIN
    INSERT INTO dbo.PropertyTable
    VALUES (@Par1, @Par2, @Par3)
END
GO

C#代码：

namespace InsertManyTest.Console
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    class Program
    {
        static void Main(string[] args)
        {
            var connString = "Server=.;Database=Test;Trusted_Connection=True;";
            using (var connection = new SqlConnection(connString))
            {
                var adapter = new SqlDataAdapter("", connection);

                var dataTable = new DataTable();
                dataTable.Columns.AddRange(new[]
                {
                    new DataColumn("Par1", typeof(string)),
                    new DataColumn("Par2", typeof(int)),
                    new DataColumn("Par3", typeof(bool))
                });

                adapter.InsertCommand = new SqlCommand("InsertPropertyTable", connection);
                adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
                adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

                adapter.DeleteCommand = new SqlCommand();
                adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
                adapter.UpdateCommand = new SqlCommand();
                adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
                adapter.SelectCommand = new SqlCommand();
                adapter.SelectCommand.UpdatedRowSource = UpdateRowSource.None;

                adapter.InsertCommand.Parameters.Add("@Par1", SqlDbType.VarChar,10, "Par1");
                adapter.InsertCommand.Parameters.Add("@Par2", SqlDbType.Int, 1, "Par2");
                adapter.InsertCommand.Parameters.Add("@Par3", SqlDbType.Bit, 1, "Par3");

                CreateRow(dataTable);
                CreateRow(dataTable);
                CreateRow(dataTable);
                CreateRow(dataTable);

                adapter.UpdateBatchSize = 2;
                adapter.Update(dataTable);
            }
        }

        private static void CreateRow(DataTable dataTable)
        {
            var row = dataTable.NewRow();
            FillRow(row);
            dataTable.Rows.Add(row);
        }

        private static void FillRow(DataRow row)
        {
            row["Par1"] = "Asd";
            row["Par2"] = 1;
            row["Par3"] = true;
        }
    }
}

当UpdateBatchSize设置为默认值(1)时，它正在工作，但为每个插入进行往返操作。我想避免这种情况，因此我已经将UpdateBatchSize设置为更大的值，但之后我得到了一些奇怪的例外：

System.ArgumentException：“指定的参数名'Parameter1‘无效。”

来自adaper.Update(...);

我预期此更新将生成如下内容：

sp_exec InsertPropertyTable 'Asd', 1;
sp_exec InsertPropertyTable 'Asd', 1;
sp_exec InsertPropertyTable 'Asd', 1;
sp_exec InsertPropertyTable 'Asd', 1;

它将在单次往返数据库中执行。

更新:我在堆栈溢出上发现了这个问题，作者也在做类似的事情。在他的例子中，它是有效的，他与其他批量插入的方法进行了比较

使用SP进行批量插入

2008年博文

更新：

我是个哑巴。我是在.net核心2.2中测试这个，而不是在.net 4.6.1上测试。它不在.net核心2.2上工作，但它在.net 4.6.1上工作。

我没有看到预期的结果，因为sql服务器分析器中有几行。每一行都包含单个exec ...。

Answer 1

似乎你可以利用表值参数。

基本上，您可以在Server端创建新的自定义类型，它反映了要发送到存储过程的数据结构(一行)。然后，使用该类型通知存储过程接受的参数的类型。一旦在存储过程中接收到，您就可以像任何其他表一样查询表值参数:例如，选择其中的所有内容并插入到实际表中。

ref1：https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

ref2：https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters