[.NET] Dapper plus - Bulk actions

 .NET    Dapper    Dapper plus   Dapper Contrib  

▌Introduction

---

Dapper Plus provide high-efficient Bulk Actions (Insert, Update, Delete, and Merge) for .NET.

We will create some sample codes for practicing the actions.

▌Environment

---

▋Dapper 1.50.2

▋Dapper Plus 1.2.1

▋Dapper Contrib 1.50.0

▌Implement

---

▋Install packages

▋Dapper

▋Z.Dapper.Plus

▋Dapper.Contrib

▋Tables

Use the following SQL to create the tables in your database.

--Products CREATE TABLE Products (    Id    INT IDENTITY(1,1) NOT NULL,    Title VARCHAR(20) NOT NULL,    Price DECIMAL,    CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED    (         [Id] ASC    ) ) --ProductDetails --Products 1<->1 ProductOrders CREATE TABLE ProductDetails (    ProductId      INT NOT NULL,    Author         VARCHAR(100),    PublicationOn  CHAR(4),    CONSTRAINT [PK_dbo.ProductDetails] PRIMARY KEY CLUSTERED    (         [ProductId] ASC    ) ) ALTER TABLE ProductDetails WITH CHECK ADD  CONSTRAINT [FK_dbo.ProductDetails_dbo.Products_Id] FOREIGN KEY([ProductId]) REFERENCES [dbo].[Products] ([Id]) ON DELETE CASCADE GO --ProductOrders --Products 1<->* ProductOrders CREATE TABLE ProductOrders (    Id        INT IDENTITY(1,1) NOT NULL,    ProductId INT NOT NULL,    Unit      INT NOT NULL,    Buyer     VARCHAR(20),    CONSTRAINT [PK_dbo.ProductOrders] PRIMARY KEY CLUSTERED    (         [Id] ASC    ) ) ALTER TABLE ProductOrders WITH CHECK ADD  CONSTRAINT [FK_dbo.ProductOrders_dbo.Products_Id] FOREIGN KEY([ProductId]) REFERENCES [dbo].[Products] ([Id]) ON DELETE CASCADE GO

▋DAO

▋Product.cs

[Table("Products")] public class Product {         [Write(false)]         public int Id { get; set; }         public string Title { get; set; }         public decimal Price { get; set; }         public ProductDetail Detail { get; set; }         public List<ProductOrder> Orders { get; set; }         public Product()         {             this.Detail = new ProductDetail();             this.Orders = new List<ProductOrder>();         } }

▋ProductOrder.cs

[Table("ProductOrders")] public class ProductOrder {         [Write(false)]         public int Id { get; set; }         public int ProductId { get; set; }         public int Unit { get; set; }         public string Buyer { get; set; } }

▋ProuductDetail.cs

[Table("ProductDetails")] public class ProductDetail {         public int ProductId { get; set; }         public string Author { get; set; }         public string PublicationOn { get; set; } }

▋Sql Connection Factory

public static class SqlConnectionFactory {         private static string _connStr = "Data Source=.;Initial Catalog=JB;Integrated Security=True";         public static SqlConnection Create()         {             return new SqlConnection(_connStr);         } }

▋Set Mapping

Remember to set Identity column mapping, or the identity value won’t be wrote back when doing insert.

#region Table Mapping //We had set [Table] attribute so we can skip table-mapping //DapperPlusManager.Entity<Product>().Table("Products"); //DapperPlusManager.Entity<ProductDetail>().Table("ProductDetails"); //DapperPlusManager.Entity<ProductOrder>().Table("ProductOrders"); #endregion #region Entity Mapping DapperPlusManager.Entity<Product>()                  .Identity(x => x.Id)                  .Ignore(x => x.Detail)                  .Ignore(x => x.Orders); DapperPlusManager.Entity<ProductOrder>()                    .Identity(x => x.Id); #endregion

▋Bulk Insert

▋Bulk insert single

using (var cn = SqlConnectionFactory.Create()) {         cn.Open();         cn.BulkInsert(prod); }

▋Bulk insert one-to-one

//Product prod = ... //ProductDetail detail = ... using (var cn = SqlConnectionFactory.Create()) {        cn.Open();        cn.BulkInsert(prod)                     .ThenForEach(x => detail.ProductId = x.Id)                     .ThenBulkInsert(x => detail); }

Notice that we have to set the Product Id to ProductDetail object’s property: ProductId, after the Product object is inserted.

▋Bulk insert one-to-one (List)

//List<Product> prods = ... using (var cn = SqlConnectionFactory.Create()) {                 cn.Open();      cn.BulkInsert(prods)               .ThenForEach(x => x.Detail.ProductId = x.Id)               .ThenBulkInsert(x => x.Detail); }

▋Bulk insert one-to-many

//Product prod = ... //List<ProductOrder> orders = ... using (var cn = SqlConnectionFactory.Create()) {      cn.Open();      cn.BulkInsert(prod)                     .ThenForEach(x => orders.ForEach( o => o.ProductId =  x.Id))                     .ThenBulkInsert(x => orders); }

▋Bulk insert one-to-many (List)

//List<Product> prods = ... using (var cn = SqlConnectionFactory.Create()) {        cn.Open();        cn.BulkInsert(prods)                     .ThenForEach(x => x.Orders.ForEach(o => o.ProductId = x.Id))                     .ThenBulkInsert(x => x.Orders); }

▋Bulk Update

▋Bulk update single

using (var cn = SqlConnectionFactory.Create()) {      cn.Open();      cn.BulkUpdate(prod); }

▋Bulk update one-to-one

//Product prod = ... //Update prod and prod.Detail ... using (var cn = SqlConnectionFactory.Create()) {       cn.Open();       cn.BulkUpdate(prod, x => x.Detail); }

▋Bulk update one-to-one (List)

//List<Product> prods = ... //Update prods ... using (var cn = SqlConnectionFactory.Create()) {         cn.Open();         cn.BulkUpdate(prods, x => x.Detail);         //Also we can write like this         //cn.BulkUpdate(prods).ThenBulkUpdate(x => x.Detail); }

▋Bulk update one-to-many      

//Product prod = ... //Update prod... using (var cn = SqlConnectionFactory.Create()) {        cn.Open();        cn.BulkUpdate(prod, x => x.Orders);        //Also we can write like this        //cn.BulkUpdate(prod).ThenBulkUpdate(x => x.Orders); }

▋Bulk update one-to-many (List)

//List<Product> prods = ... //UPdate prods... using (var cn = SqlConnectionFactory.Create()) {        cn.Open();        cn.BulkUpdate(prods, x => x.Orders);        //Also we can write like this        //cn.BulkUpdate(prods)        //    .ThenBulkUpdate(x => x.Orders); }

▋Bulk Delete

▋Bulk delete single

using (var cn = SqlConnectionFactory.Create()) {       cn.Open();       cn.BulkDelete(prod); }

▋Bulk delete one-to-one

//Product prod = ... //From database using (var cn = SqlConnectionFactory.Create()) {     cn.Open();     cn.BulkDelete(prod.Detail)                 .BulkDelete(prod); }

▋Bulk delete one-to-one (List)

//List<Product> prods = ... //From database using (var cn = SqlConnectionFactory.Create()) {      cn.Open();      cn.BulkDelete(prods.Select(x => x.Detail))                     .BulkDelete(prods); }

▋Bulk delete one-to-many

//Product prod = ... //From database using (var cn = SqlConnectionFactory.Create()) {        cn.Open();        cn.BulkDelete(prod.Orders)                     .BulkDelete(prod); }

▋Bulk delete one-to-many (List)

//List<Product> prods = ... //From database using (var cn = SqlConnectionFactory.Create()) {     cn.Open();     cn.BulkDelete(prods.SelectMany(x=>x.Orders))                     .BulkDelete(prods); }

▋Bulk Merge

▋Bulk merge single

using (var cn = SqlConnectionFactory.Create()) { cn.Open(); cn.BulkMerge(prod); }

▋Bulk merge one-to-one

using (var cn = SqlConnectionFactory.Create()) {     cn.Open();     cn.BulkMerge(prod)                     .ThenForEach(x => x.Detail.ProductId = x.Id)                     .ThenBulkMerge(x => x.Detail); }

▋Bulk merge one-to-one (List)

using (var cn = SqlConnectionFactory.Create()) {    cn.Open();    cn.BulkMerge(prods)                     .ThenForEach(x => x.Detail.ProductId = x.Id)                     .ThenBulkMerge(x => x.Detail); }

▋Bulk merge one-to-many

//Product prod = ... using (var cn = SqlConnectionFactory.Create()) {      cn.Open();      cn.BulkMerge(prod)                     .ThenForEach(x => x.Orders.ForEach(o=>o.ProductId = x.Id))                     .ThenBulkMerge(x => x.Orders); }

▋Bulk merge one-to-many (List)

//List<Product> prods = ... using (var cn = SqlConnectionFactory.Create()) {       cn.Open();       cn.BulkMerge(prods)                     .ThenForEach(x => x.Orders.ForEach(o => o.ProductId = x.Id))                     .ThenBulkMerge(x => x.Orders); }

▌Reference

---

▋Dapper Tutorials & Examples - C# Micro ORM