CODEWITHSUNDEEP
c#sql-serverormdapper
h-rai
h-rai

Reputation: 3964

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

Upvotes: 0

Views: 1001

Answers (1)

Jonathan Magnan
Jonathan Magnan

Reputation: 11337

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

EDIT: answer comment

SqlBulkCopy can only be used with MS SQL Server though. Isn't it?

Exactly, SqlBulkCopy is only compatible with SQL Server. Our library supports multiple providers:

  • SQL Server
  • SQL Compact
  • Oracle
  • MySql
  • PostgreSQL
  • SQLite
  • Firebird

Upvotes: 1

Related Questions