best way to do bulk inserts using dapper.net

.net bulkinsert c# dapper sql-server

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?

Expert Answer

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 provides easy support for 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);   

Popular Answer

If performance is what you're after then I'd recommend using SqlBulkCopy rather than inserting using Dapper. See here for some performance comparisons: http://www.ikriv.com/dev/db/SqlInsert/SqlInsert.html



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why