使用dapper.net进行批量插入的最佳方法

Question

我使用以下代码将记录插入到Server 2014中的表中

using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["myConnString"]))
{

   conn.Execute("INSERT statement here", insertList);

}

insertList是一个包含100万项的列表。我在i5桌面上测试了这个插入，在同一台机器上将100万条记录插入Server大约花费了65分钟。我不知道dapper是怎么在幕后做插入的。我当然不想打开和关闭数据库连接一百万次！

这是在dapper中进行批量插入的最佳方法吗?还是应该尝试其他方法，或者使用企业库使用普通的ADO.Net？

编辑

事后看来，我知道使用ADO.Net会更好，我的问题也会改头换面。我仍然想知道这是dapper所能做的最好的，还是我错过了一种更好的方法来做dapper本身？

Answer 1

在Ehsan Sajjad注释的基础上，其中一种方法是编写一个存储过程，该存储过程具有用户定义的表类型的自述参数。

假设您想要批量插入由名称和姓氏组成的联系人，您将这样做: 1)创建一个表类型：

CREATE TYPE [dbo].[MyTableType] AS TABLE(
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL
)
GO

2)现在创建一个存储的proc，它使用上面的表类型：

CREATE PROC [dbo].[YourProc]
/*other params here*/
@Names AS MyTableType READONLY
AS
/* proc body here 
 */
GO

3)在.NET方面，将参数作为System.Data.SqlDbType.Structured传递--这通常涉及创建内存中的数据表，然后向表中添加行，然后使用这个DataTable对象作为@Names参数。注意: DataTable被认为是内存密集型的--小心并分析代码，以确保它不会导致服务器上的资源问题。

扩展解决方案使用这里概述的方法：https://stackoverflow.com/a/9947259/190476解决方案用于删除，但也可以用于插入或更新。

Answer 2

第一个选择应该是SQL大容量复制，因为它不受SQL注入的影响。

但是，有一种方法可以大大提高性能。您可以将多个插入合并到一个SQL中，并且只有一个调用而不是多个调用。所以，不是这样的：

​

​

你可以拥有这个：

​

​

批量插入用户的代码如下所示：

public async Task InsertInBulk(IList<string> userNames)
{
    var sqls = GetSqlsInBatches(userNames);
    using (var connection = new SqlConnection(ConnectionString))
    {
        foreach (var sql in sqls)
        {
            await connection.ExecuteAsync(sql);
        }
    }
}

private IList<string> GetSqlsInBatches(IList<string> userNames)
{
    var insertSql = "INSERT INTO [Users] (Name, LastUpdatedAt) VALUES ";
    var valuesSql = "('{0}', getdate())";
    var batchSize = 1000;

    var sqlsToExecute = new List<string>();
    var numberOfBatches = (int)Math.Ceiling((double)userNames.Count / batchSize);

    for (int i = 0; i < numberOfBatches; i++)
    {
        var userToInsert = userNames.Skip(i * batchSize).Take(batchSize);
        var valuesToInsert = userToInsert.Select(u => string.Format(valuesSql, u));
        sqlsToExecute.Add(insertSql + string.Join(',', valuesToInsert));
    }

    return sqlsToExecute;
}

完整的文章和性能比较可以在这里获得：http://www.michalbialecki.com/2019/05/21/bulk-insert-in-dapper/

Answer 3

最好的免费插入方式是像亚历克斯和安德烈亚斯建议的那样直接使用SqlBulkCopy类。

免责声明：我是项目Dapper Plus的所有者

此项目不是免费的，但支持以下操作：

• BulkInsert
• BulkUpdate
• BulkDelete
• BulkMerge

通过使用映射和允许输出值(如标识列)。

// CONFIGURE & MAP entity
DapperPlusManager.Entity<Order>()
                 .Table("Orders")
                 .Identity(x => x.ID);

// CHAIN & SAVE entity
connection.BulkInsert(orders)
          .AlsoInsert(order => order.Items);
          .Include(x => x.ThenMerge(order => order.Invoice)
                         .AlsoMerge(invoice => invoice.Items))
          .AlsoMerge(x => x.ShippingAddress);