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.
NOTE: For async method, see async action
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