Insert many rows with Dapper

Question

what's the best solution to insert many rows into the SQL Server with Dapper? When I have got about 8k records my databaseconnection timeouts and fails.

In my application at the end stage I have got lets say a list of tables, where each table got list of rows.

What i do is creating transaction and foreaching on each table and then foreaching on each row and conn.CreateCommand, filling parameters and executeNonQuery. So if I got 9k records I am actually doing 9k executeNonQuery operations.

Is there any better option for me?

i would investigate using SqlBulkCopy with dapper. Maybe this references could help you: [Bulk inserts taking longer than expected using Dapper](https://stackoverflow.com/questions/10689779), [How to use SQL Bulk Copy with Dapper .Net ?](https://stackoverflow.com/questions/29070108) — DaniCE, Nov 02 '17 at 15:48

score 4 · Accepted Answer · answered Nov 03 '17 at 07:34

Agree with DaniCE, SqlBulkCopy is the way to go here. Been in that situation lately where I did most of the data work with dapper but with larger amounts of data, in my case millions of records, Dapper was for once not my best friend.

    private void BulkCopy(SqlConnection sqlConnection,string tableName, DataTable dataTable)
    {
        using (var bulkCopy = new SqlBulkCopy(sqlConnection))
        {
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = 50000;
            bulkCopy.BulkCopyTimeout = 60; //seconds

            bulkCopy.WriteToServer(dataTable);
        }
    }

Thanks man, never heard about bulk insert neither SqlBuklCopy class. Just made some research about this topic after your post, and thats excatly what I need!! Thanks again !! — CSharpBeginner, Nov 04 '17 at 18:24

score 0 · Answer 2 · answered Nov 03 '17 at 14:22

The best free way is without a doubt SqlBulkCopy as @JFM and @DaniCE suggested

Disclaimer: I'm the owner of the project Dapper Plus

This library is NOT FREE but provides easy support for the following operations:

BulkInsert
BulkUpdate
BulkDelete
BulkMerge
BulkSynchronize

By using mapping and allow 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);

connection.BulkInsert(models).AlsoBulkInsert(model => model.SelectMany(c => c.Variables)); If you notice AlsoBulkInsert is a list, in your example it's not. Am I missing a using statement? — MIKE, May 02 '18 at 15:52

Insert many rows with Dapper

2 Answers2