Dapper provides the Execute method for inserting data. However, a database roundtrip is required for every data you insert. It’s a perfect solution when you need to insert only 1 data, but it becomes very inefficient as soon as you insert multiple data.
The fastest way of inserting multiple data is by using the Dapper Plus third-party library.
Depending on the provider, performance can be increased by up to 75x faster and more (Online Benchmark)
Getting Started
NuGet Package: https://www.nuget.org/packages/Z.Dapper.Plus/
Documentation: Dapper Plus – Bulk Insert
Dapper BulkInsert
The Dapper Plus BulkInsert method is a great way to insert multiple data rows into an existing table quickly. It is an easy-to-use method that takes a list of objects or records and performs a bulk inserts into a database.
- It makes
BulkInsertan extremely efficient way to add data into a database in C#. - It dramatically reduces the amount of code needed and minimizes chances for errors.
- Unlike Dapper, you don’t need to write any SQL but simply pass your entities in parameters:
// Easy to use
connection.BulkInsert(products);
// Easy to customize
connection.UseBulkOptions(options => options.InsertIfNotExists = true)
.BulkInsert(products);
Dapper BulkInsert with Global Context Mapping
BulkInsert with Global Context Mapping is an ideal choice when you need to keep the identity value of your entities during a bulk import. Dapper Plus provides a DapperPlusManager class for defining your mapping globally.
In addition to being fast, you can map your entity more than once with a mapping key to add some options for a specific scenario.
For example, you want to keep the identity value for a specific import:
// Global Mapping requires to be mapped globally only once
DapperPlusManager.Entity<Customer>("Customer_KeepIdentity").KeepIdentity(true);
// ...code...
connection.BulkInsert("Customer_KeepIdentity", customers);
Dapper BulkInsert with Instance Context Mapping
BulkInsert also allows you to map and set options at runtime using instance context mapping.
For example, you call a method that specifies at runtime if the identity value should be kept or generated by the database:
public void CustomBulkInsert<T>(List<T> entities, bool keepIdentity) where T: class
{
var context = new DapperPlusContext();
context.Entity<T>().KeepIdentity(keepIdentity);
context.BulkInsert(entities);
}
Dapper BulkInsert With Related Entities
The BulkInsert provides an easy way to insert related entities into the database. This method eliminates the need to manually write SQL statements to insert each entity separately, saving time and removing complexity. With BulkInsert, you can quickly insert multiple entities that share a one-to-one or one-to-many relationship into the database in one operation.
The following example shows how to insert related entities with a one-to-many relationship using the BulkInsert method.
DapperPlusManager
.Entity<Product>()
.Table("Products")
.Identity(x => x.ProductID);
DapperPlusManager
.Entity<Category>()
.Table("Categories")
.Identity(x => x.CategoryID, true); // the categoryID will be automatically propagated
// ...code...
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
// Insert categories first, then all related products
connection.BulkInsert(categories)
.ThenBulkInsert(x => x.Products);
}
FAQ Chat