best way to do bulk inserts using dapper.net

user20358 picture user20358 · Mar 4, 2015 · Viewed 46k times · Source

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?

Answer

Alex Marshall picture Alex Marshall · Jul 8, 2016

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