Dapper Bulk Insert Fastest Way in Dapper to Insert Multiple Rows
Description
The Dapper Plus BulkInsert
extension method lets you insert a large number of entities in your database.
// Easy to use connection.BulkInsert(orders); // Easy to customize connection.UseBulkOptions(options => options.InsertIfNotExists = true) .BulkInsert(orders);
Performance Comparison
Operations | 1,000 Entities | 2,000 Entities | 5,000 Entities |
---|---|---|---|
Execute (Many) | 1,200 ms | 2,400 ms | 6,000 ms |
BulkInsert | 50 ms | 55 ms | 75 ms |
HINT:A lot of factors might affect the benchmark time such as index, column type, latency, throttling, etc.
Bulk Insert Async
To perform an asynchronous bulk insert in Dapper Plus, you will need to call the BulkInsertAsync
method or use the ActionAsync method.
In this C# example, we will create a list of products and insert them into our database asynchronously.
await connection.BulkInsertAsync(products);
Bulk Insert Entity
The Dapper Plus BulkInsert method allows inserting a single or multiple entities of the same type.
//Insert a single order. connection.BulkInsert(order); //Insert multiple orders. connection.BulkInsert(order1, order2, order3);
Bulk Insert IEnumerable
The Dapper Plus BulkInsert method allows inserting a single enumerable or multiple enumerable of entities of the same type.
//Insert a list of orders. connection.BulkInsert(orders); //Insert multiple list of orders. connection.BulkInsert(orders1, orders2, orders3);
Bulk Insert with "One to One" Relation
The Dapper Plus BulkInsert method allows inserting a related item with a "One to One" relation.
//Insert an order and the related invoice. connection.BulkInsert(order, order => order.Invoice); //Insert a list of orders and the related invoice to every order. connection.BulkInsert(orders, order => order.Invoice);
Bulk Insert with "One to Many" Relation
The Dapper Plus BulkInsert method allow inserting related items with a "One to Many" relation.
//Insert an order and all related items. connection.BulkInsert(order, order => order.Items); //Insert a list of orders and all related items to every order. connection.BulkInsert(orders, order => order.Items);
Bulk Insert with "Mixed" Relation
The Dapper Plus BulkInsert method allows inserting related item(s) with any relation.
//Insert an order, all related items, and the related invoice. connection.BulkInsert(order, order => order.Items, order => order.Invoice); //Insert a list of orders, all related items to every order, and the related invoice to every order. connection.BulkInsert(orders, order => order.Items, order => order.Invoice);
Bulk Insert Chain Action
The Dapper Plus BulkInsert method allows chaining multiple bulk action methods.
//Insert an order and all related items. Insert an invoice and all related invoice items. connection.BulkInsert(order, order => order.Items) .BulkInsert(invoice, invoice => invoice.Items); //Insert a list of orders and all related items to every order. Insert a list of invoices and //all related items to every invoice. connection.BulkInsert(orders, order => order.Items) .BulkInsert(invoices, invoice => invoice.Items);
ZZZ Projects