C# .NET 8 SQL 批量插入 Dapper、BulkCopy 和表值参数

介绍

在 .NET 应用程序中处理大型数据集通常需要将数据高效地插入到 SQL Server 中。本文探讨了使用 C# 和 .NET 8 批量插入数据的三种流行技术：Dapper、SqlBulkCopy 和表值参数 (TVP)。我们将比较它们的性能、易用性和对不同场景的适用性。

1. Dapper Bulk Insert

Dapper 是一个轻量级 ORM（对象关系映射器），它在原始 SQL 和功能齐全的 ORM（如 Entity Framework）之间实现了平衡。虽然 Dapper 本身不支持批量插入，但可以对其进行扩展以高效执行批量插入。

执行

要使用 Dapper 进行批量插入，通常在单个事务中执行多个插入语句。以下是示例。

using Dapper;
using System.Data.SqlClient;
using System.Collections.Generic;

public async Task BulkInsertDapperAsync(IEnumerable<MyData> data, string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        using (var transaction = connection.BeginTransaction())
        {
            string sql = "INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2)";
            await connection.ExecuteAsync(sql, data, transaction: transaction);
            transaction.Commit();
        }
    }
}

2. SqlBulkCopy

SqlBulkCopy 是一个内置的 .NET 类，专门用于将批量数据传输到 SQL Server。它提供了这里讨论的三种方法中最快的性能，因为它直接写入数据库。

执行

以下是使用 SqlBulkCopy 的方法。

using System.Data;
using System.Data.SqlClient;

public async Task BulkInsertSqlBulkCopyAsync(DataTable dataTable, string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();

        using (var bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "MyTable";
            await bulkCopy.WriteToServerAsync(dataTable);
        }
    }
}

要使用 SqlBulkCopy，您需要将数据转换为 DataTable 或使用 IDataReader。

3. 表值参数 (TVP)

TVP 允许您将表作为参数传递给存储过程或 SQL 命令。它们提供了一种灵活而高效的方式来执行批量插入，尤其是当您需要在服务器端验证或操作数据时。

执行

首先，在 SQL Server 中定义一个用户定义表类型。

CREATE TYPE MyDataType AS TABLE
(
    Column1 INT,
    Column2 NVARCHAR(50)
);

接下来，创建一个存储过程来处理插入。

CREATE PROCEDURE InsertMyData
    @MyData MyDataType READONLY
AS
BEGIN
    INSERT INTO MyTable (Column1, Column2)
    SELECT Column1, Column2 FROM @MyData;
END

最后，实现使用 TVP 的 C# 代码。

using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Threading.Tasks;

public async Task BulkInsertTVPAsync(IEnumerable<MyData> data, string connectionString)
{
    var dataTable = new DataTable();
    dataTable.Columns.Add("Column1", typeof(int));
    dataTable.Columns.Add("Column2", typeof(string));

    foreach (var item in data)
    {
        dataTable.Rows.Add(item.Column1, item.Column2);
    }

    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        using (var command = new SqlCommand("InsertMyData", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            var tvpParameter = command.Parameters.AddWithValue("@MyData", dataTable);
            tvpParameter.SqlDbType = SqlDbType.Structured;
            await command.ExecuteNonQueryAsync();
        }
    }
}

性能比较

每种方法的性能可能因数据集大小和应用程序的具体要求而异。一般来说。

• SqlBulkCopy 是原始批量数据插入最快、最有效的方法。
• TVP 提供了灵活性，并且对于需要服务器端数据验证或操作的场景非常有效。
• Dapper 易于使用，并且能够与现有的基于 Dapper 的项目集成，但对于非常大的数据集，它可能不如 SqlBulkCopy 或 TVP 那么快。

结论

选择正确的方法将数据批量插入 SQL Server 取决于您的具体需求。

• 使用 SqlBulkCopy 可获得原始数据传输的最高性能。
• 当您需要灵活性和服务器端处理时，请使用表值参数。
• 为了简单起见，请在以 Dapper 为中心的代码库中工作时使用 Dapper。

通过了解每种方法的优势和用例，您可以做出明智的决定来优化 .NET 应用程序的性能和可维护性。