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
BEGININSERT 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();}}
}