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?
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