Best way to do bulk inserts using dapper.net

Question

I am using the following code to insert records to a table in SQL Server 2014

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

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

}

The insertList is a list that has 1 million items in it. I tested this insert on a i5 desktop and it took about 65 minutes to insert a million records to SQL Server on the same machine. I am not sure how dapper is doing the inserts behind the scenes. I certainly dont want to open and close the database connection a million times!

Is this the best way to do bulk inserts in dapper or should I try something else or go with plain ADO.Net using Enterprise library?

EDIT

In hindsight, I know using ADO.Net will be better, so will rephrase my question. I still would like to know if this is the best that dapper can do or am I missing a better way to do it in dapper itself?

Solution 1

Building on Ehsan Sajjad's comment, one of the ways is to write a stored procedure that has a READONLY parameter of a user-defined TABLE type.

Say you want to bulk insert contacts that consist of a first name and last name, this is how you would go about it: 1) Create a table type:

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

2) Now create a stored proc that uses the above table type:

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

3) On the .NET side, pass the parameter as System.Data.SqlDbType.Structured This usually involves creating an in-memory data-table, then adding rows to it and then using this DataTable object as the @Names parameter. NOTE: The DataTable is considered to be memory intensive - be careful and profile your code to be sure that it does not cause resource issues on your server.

ALTENATIVE SOLUTION Use the approach outlined here: https://stackoverflow.com/a/9947259/190476 The solution is for DELETE but can be adapted for an insert or update as well.

Solution 2

The first choice should be SQL Bulk Copy, cause it's safe from SQL injection.

However, there is a way to drastically improve performance. You could merge multiple inserts into one SQL and have only one call instead of multiple. So instead of this:

You can have this:

Code for inserting Users in bulk can look like this:

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;
}

Whole article and performance comparison is available here: http://www.michalbialecki.com/2019/05/21/bulk-insert-in-dapper/

Solution 3

The best free way to insert with excellent performance is using the SqlBulkCopy class directly as Alex and Andreas suggested.

Disclaimer: I'm the owner of the project Dapper Plus

This project is not free but supports the following operations:

• BulkInsert
• BulkUpdate
• BulkDelete
• BulkMerge

By using mapping and allowing to output value like identity columns.

// 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);   

Comments

[Arsen Mkrtchyan]

@JonathanMagnan, not clear if Dapper Plus has free version, and how it differ from Pro version?

[Jonathan Magnan]

Hello @ArsenMkrtchyan, there is no "FREE" version. You can, however, try it for free for an unlimited time until you are production ready. We will soon revisit all our products to make it clear.

[Arsen Mkrtchyan]

ah, so for going in prod, anyway product should be bought?

[Jonathan Magnan]

Yes, the product must be bought for the production. The trial ends at the end of every month, so you simply need to download it once per month to extend the trial which work perfectly for all non-production environment.

[sinitram]

@JonathanMagnan I suppose you use in you library OUTPUT Clause and than use merge into for inserting. And merge statement always locks the tables. Does it causes often locks in the database? I just did not find any explanation of how it works in your site.

[brianc]

RE:DapperPlus - The license fees start at $799/yr for one developer. A contractor built some software using Dapper Plus without being aware of it. For people who get it from Nuget there is no way to know that it is only a trial and the verbiage is misleading stating "free and prime features". There are no free features. " Features: BulkInsert, BulkDelete, BulkUpdate, BulkMerge, and more! Support: SQL Server, MySQL, Oracle, PostgreSQL, SQLite, and more! Online Example: dotnetfiddle.net/ltIqrC Include free and prime features."

[Damien Sawyer]

There's an open source project with bulk inserts here. It's not really that hard. github.com/KostovMartin/Dapper.Bulk

[Kiquenet]

And how-to not insert duplicate keys in table ? Search "key" in table before insert...

[Kiquenet]

And how-to not insert duplicate keys in table ? Search "key" in table before insert...

[Uwe Keim]

Isn't this prone to SQL injection?

[Don Rolling]

He said Dapper.Net