best way to do bulk inserts using dapper.net [closed]

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?

Answer 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.

Answer 2

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