.net ORM框架dapper批量插入

.NET ORM 框架 Dapper 批量插入全解析

在 .NET 开发中，与数据库交互是常见需求。Dapper 作为轻量级的 ORM（对象关系映射）库，在简化数据库交互方面表现出色。今天我们就来深入探讨 Dapper 实现批量插入的几种方法。

为什么需要批量插入

在实际业务场景中，当需要向数据库插入大量数据时，如果采用单条插入的方式，会频繁与数据库进行交互，导致性能下降。而批量插入可以将多个插入操作合并为一次，减少数据库交互次数，从而显著提高性能。

方法一：Execute 方法与 Table - Valued Parameters (TVP)

适用场景

这种方法适用于支持 TVP 的数据库，例如 SQL Server。

实现步骤

创建用户定义的表类型：在 SQL Server 中创建一个用户定义的表类型，用于存储要插入的数据。

CREATE TYPE dbo.MyTableType AS TABLE
(Id INT,Name NVARCHAR(50)
);

创建存储过程：编写一个存储过程，接收用户定义的表类型作为参数，并将数据插入到目标表中。

CREATE PROCEDURE dbo.InsertMyTable@MyTable dbo.MyTableType READONLY
AS
BEGININSERT INTO MyTable (Id, Name)SELECT Id, Name FROM @MyTable;
END

C# 代码实现：在 C# 中使用 Dapper 调用存储过程进行批量插入。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;public class MyEntity
{public int Id { get; set; }public string Name { get; set; }
}public class Program
{public static void Main(){string connectionString = "connection_string";using (IDbConnection dbConnection = new SqlConnection(connectionString)){List<MyEntity> entities = new List<MyEntity>{new MyEntity { Id = 1, Name = "Name1" },new MyEntity { Id = 2, Name = "Name2" }};var table = new DataTable();table.Columns.Add("Id", typeof(int));table.Columns.Add("Name", typeof(string));foreach (var entity in entities){table.Rows.Add(entity.Id, entity.Name);}string sql = "EXEC dbo.InsertMyTable @MyTable";var param = new { MyTable = table.AsTableValuedParameter("dbo.MyTableType") };dbConnection.Execute(sql, param);}}
}

注意事项

AsTableValuedParameter 方法是 Dapper.Contrib 或其他扩展库的一部分，你可能需要安装相应的 NuGet 包。

独特见解

这种方法将数据封装在用户定义的表类型中，通过存储过程进行插入，使得数据库操作更加安全和高效。但需要在数据库端进行额外的配置，增加了一定的维护成本。

方法二：SqlBulkCopy 高效批量插入

适用场景

对于大量数据的批量插入，SqlBulkCopy 是最佳选择。

代码示例

using (var connection = new SqlConnection(connectionString))
{connection.Open();using (var bulkCopy = new SqlBulkCopy(connection)){bulkCopy.DestinationTableName = "MyTable";bulkCopy.ColumnMappings.Add("Name", "Name");bulkCopy.ColumnMappings.Add("Age", "Age");var table = new DataTable();table.Columns.Add("Name", typeof(string));table.Columns.Add("Age", typeof(int));table.Rows.Add("Alice", 25);table.Rows.Add("Bob", 30);table.Rows.Add("Charlie", 35);bulkCopy.WriteToServer(table);}
}

优点

性能极高，适合处理大量数据。它直接将数据批量写入数据库，避免了多次 SQL 语句的执行，大大提高了插入效率。

独特见解

SqlBulkCopy 是专门为批量插入设计的，在处理大量数据时优势明显。但它只能用于 SQL Server 数据库，缺乏一定的跨数据库兼容性。

方法三：使用事务批量插入

实现思路

通过事务将多个插入操作打包，确保原子性。如果其中一个插入操作失败，整个事务将回滚，保证数据的一致性。

代码示例

using (var connection = new SqlConnection(connectionString))
{connection.Open();using (var transaction = connection.BeginTransaction()){try{var sql = "INSERT INTO MyTable (Name, Age) VALUES (@Name, @Age)";var users = new[]{new { Name = "Alice", Age = 25 },new { Name = "Bob", Age = 30 },new { Name = "Charlie", Age = 35 }};connection.Execute(sql, users, transaction);transaction.Commit();}catch (Exception ex){transaction.Rollback();Console.WriteLine($"Error: {ex.Message}");}}
}

优点

确保所有插入操作要么全部成功，要么全部失败，保证了数据的完整性。

独特见解

事务机制在处理数据插入时非常重要，尤其是在对数据一致性要求较高的场景中。但事务的使用会增加一定的性能开销，需要根据实际情况进行权衡。

方法四：使用自定义扩展方法

实现思路

结合 Dapper 和 SqlBulkCopy 实现一个高效的批量插入扩展方法。

代码示例

public static class DapperExtensions
{public static void BulkInsert<T>(this IDbConnection connection, string tableName, IEnumerable<T> data){using (var bulkCopy = new SqlBulkCopy(connection.ConnectionString)){bulkCopy.DestinationTableName = tableName;var table = new DataTable();var properties = typeof(T).GetProperties();foreach (var prop in properties){table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);bulkCopy.ColumnMappings.Add(prop.Name, prop.Name);}foreach (var item in data){var row = table.NewRow();foreach (var prop in properties){row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;}table.Rows.Add(row);}bulkCopy.WriteToServer(table);}}
}// 使用示例
using (var connection = new SqlConnection(connectionString))
{var users = new[]{new User { Name = "Alice", Age = 25 },new User { Name = "Bob", Age = 30 },new User { Name = "Charlie", Age = 35 }};connection.BulkInsert("MyTable", users);
}