Dapper Plus - Bulk Insert

Description

INSERT entities using the Bulk Operation.

• Insert single
• Insert many
• Insert with relation (One to One)
• Insert with relation (One to Many)

Example - Insert Single

INSERT a single entity with Bulk Operation.

DapperPlusManager.Entity<Customer>().Table("Customers"); 
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.BulkInsert(new List<Customer>() { new Customer() { CustomerName = "ExampleBulkInsert", ContactName = "Example Name :" +  1}});
}

Try it: .NET Core | .NET Framework

Example - Insert Many

INSERT many entities with Bulk Operation.

DapperPlusManager.Entity<Customer>().Table("Customers"); 
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.BulkInsert(customers);
}

Try it: .NET Core | .NET Framework

Example - Insert with relation (One to One)

INSERT entities with a one to one relation with Bulk Operation.

DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{    
    connection.BulkInsert(suppliers).ThenForEach(x => x.Product.SupplierID = x.SupplierID).ThenBulkInsert(x => x.Product);
}

Try it: .NET Core | .NET Framework

Example - Insert with relation (One to Many)

INSERT entities with a one to many relations with Bulk Operation.

DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID); 
DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);     
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{    
    connection.BulkInsert(suppliers).ThenForEach(x => x.Products.ForEach(y => y.SupplierID =  x.SupplierID)).ThenBulkInsert(x => x.Products);
}

Try it: .NET Core | .NET Framework

Real-Life Scenarios

Insert and keep identity value

Your entity has an identity property, but you want to force it to insert a specific value instead. The InsertKeepIdentity option allows you to keep the identity value of your entity.

DapperPlusManager.Entity<Customer>().Table("Customers"); 
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.UseBulkOptions(options => options.InsertKeepIdentity = true).BulkInsert(customers);
}

Try it: .NET Core | .NET Framework

Insert without returning the identity value

By default, the BulkInsert method already returns the identity when inserting.

However, such behavior impacts performance. For example, when the identity must be returned, a temporary table is created in SQL Server instead of directly using SqlBulkCopy into the destination table.

You can improve your performance by turning off the AutoMapOutput option.

DapperPlusManager.Entity<Customer>().Table("Customers"); 
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.UseBulkOptions(options => options.AutoMapOutputDirection = false).BulkInsert(customers);
    FiddleHelper.WriteTable("1 - Customers (from list)", customers);
}

Try it: .NET Core | .NET Framework