Batch insert/update calls with a list parameter into a single operation/request to database in Dapper

Based off this code on Dapper's github page, I created a bulk insert statement. enter image description here

Dapper split up the query into multiple individual SQL insert statements (as seen in SQL profiler).

Is it possible to instruct it to change multiple operations into a single operation to reduce the number of DB trips without having to manually create multiple insert statements like below?

insert into test (a,b) values (b,c);
insert into test (a,b) values (d,e);
insert into test (a,b) values (f,g);

1 answer

  • answered 2018-04-17 13:24 Jonathan Magnan

    Is it possible to instruct it to change multiple operations into a single operation

    No

    There is generally two answer to allow perform bulk operations:

    SqlBulkCopy

    As @iSR5 answered in the comment section: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy

    You will not find anything faster than this.

    Dapper Plus

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

    This project is not free but offers all bulk operations:

    • BulkInsert
    • BulkUpdate
    • BulkDelete
    • BulkMerge

    (Use under the hood SqlBulkCopy)

    And some more options such as outputting identity values:

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