Dapper Tutorial Dapper Plus - Bulk Insert
Description
INSERT entities using the Bulk Operation.
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