Best way to do bulk inserts with dapper.net

Question

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

using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["myConnString"])) { conn.Execute("INSERT statement here", insertList); } 

insertList is a list with 1 million items. I tested this insertion on the i5 desktop, and it took about 65 minutes to insert a million records into SQL Server on the same computer. I'm not sure how dapper does insertion behind the scenes. Of course, I do not 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 simple ADO.Net using the Enterprise library?

EDIT

Looking back, I know that using ADO.Net will be better, so I rephrase my question. I would still like to know if this can be the best dapper can do, or have I missed the best way to do this in dapper itself?

Answer 1

If performance is what you are after then, I would recommend using SqlBulkCopy instead of embedding Dapper. See here for performance comparison: http://www.ikriv.com/dev/db/SqlInsert/SqlInsert.html

Answer 2

Based on a comment by Ehsan Sajjad, one way is to record a stored procedure that has a READONLY parameter that is a custom table type .

Suppose you want to insert paste contacts consisting of a first and last name, here is how you do 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 saved process that uses the specified 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 a data table in memory, then adding rows to it, and then using this DataTable as an @Names parameter. NOTE. A DataTable is considered memory intensive - be careful and comment on your code to make sure that it does not cause a resource problem on your server.

ALTENATIVE SOLUTION Use the approach described here: fooobar.com/questions/7782 / ... The solution is designed for DELETE, but can be adapted to be inserted or updated.

Answer 3

The best free paste method with excellent performance is to use the SqlBulkCopy class directly, as suggested by Alex and Andreas.

Disclaimer : I am the owner of the Dapper Plus project

This project is not free, but supports the following operations:

• BulkInsert
• Bulkupdate
• Bulkdelete
• Bulkmerge

Using mapping and allowing the output of values ​​as 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); 

Answer 4

The first choice should be SQL Bulk Copy , because it is safe from SQL injection.

However, there is a way to significantly improve performance. You can combine multiple inserts into one SQL and have only one call instead of several. So instead:

You can have this:

The code for bulk user insertion might 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; } 

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