// Dapper Plus

// Doc: https://dapper-plus.net/bulk-insert

​

// @nuget: Dapper

// @nuget: Microsoft.Data.SqlClient

// @nuget: Z.Dapper.Plus

​

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

using System.Linq;

using System.Diagnostics;

using Microsoft.Data.SqlClient;

using Dapper;

using Z.Dapper.Plus;

​

public class Program

{   

    public static List<BenchmarkResult> BenchmarkResults = new List<BenchmarkResult>();

    public static void Main()

    {

        // Map your entity

        DapperPlusManager.Entity<Product>()

            .Table("Product")

            .Identity(x => x.ProductID);

        var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer());

        connection.CreateTable<Product>();

        JustInTime_Compile(connection);

        var products = GenerateProducts(2000);

        var clockDapper = new Stopwatch();

        var clockDapperPlus = new Stopwatch();

        // Dapper

        {

            // seed the database

            connection.BulkInsert(products.Take(products.Count / 2));

            clockDapper.Start();

            connection.Execute(@"

MERGE INTO Product AS Target

USING (SELECT @ProductID AS ProductID, @Name AS Name, @Description AS Description,

              @Column1 AS Column1, @Column2 AS Column2, @Column3 AS Column3,

              @Column4 AS Column4, @Column5 AS Column5, @Column6 AS Column6,

              @Column7 AS Column7, @Column8 AS Column8, @Column9 AS Column9) AS Source

ON Target.ProductID = Source.ProductID

WHEN MATCHED THEN

    UPDATE SET

        Target.Name = Source.Name,

        Target.Description = Source.Description,

        Target.Column1 = Source.Column1,

        Target.Column2 = Source.Column2,

        Target.Column3 = Source.Column3,

        Target.Column4 = Source.Column4,

        Target.Column5 = Source.Column5,

        Target.Column6 = Source.Column6,

        Target.Column7 = Source.Column7,

        Target.Column8 = Source.Column8,

        Target.Column9 = Source.Column9

WHEN NOT MATCHED BY TARGET THEN

    INSERT (Name, Description, Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9)

    VALUES (Source.Name, Source.Description, Source.Column1, Source.Column2, Source.Column3, Source.Column4, Source.Column5, Source.Column6, Source.Column7, Source.Column8, Source.Column9);

            ", products);

            clockDapper.Stop();

​

            BenchmarkResults.Add(new BenchmarkResult() { Action = "Dapper", Entities = products.Count, Performance = clockDapper.ElapsedMilliseconds + " ms" });

            // reset the database

            connection.BulkDelete(products);

        }

        // Dapper Plus

        {

            // seed the database

            connection.BulkInsert(products.Take(products.Count / 2));

            // BulkMerge

            clockDapperPlus.Start();

            connection.BulkMerge(products);

            clockDapperPlus.Stop();

​

            var timeFaster = Math.Round((double)clockDapper.ElapsedMilliseconds / clockDapperPlus.ElapsedMilliseconds, 2);

            var reducedPercent = Math.Round((double)(clockDapper.ElapsedMilliseconds - clockDapperPlus.ElapsedMilliseconds) / clockDapper.ElapsedMilliseconds, 2) * 100;

            BenchmarkResults.Add(new BenchmarkResult() { Action = "Dapper Plus", Entities = products.Count, Performance = $"{clockDapperPlus.ElapsedMilliseconds} ms", TimeFaster = $"{timeFaster}x faster than Dapper", ReducedPercent = $"Time reduced by {reducedPercent}% compared to Dapper" });

            // reset the database

            connection.BulkDelete(products);

        }

        FiddleHelper.WriteTable(BenchmarkResults);

    }

    public static List<Product> GenerateProducts(int count)

    {

        var products = new List<Product>();

        for(int i = 0; i < count; i++)

        {

            if(i % 3 == 0)

            {

                products.Add(new Product() { Name = "Dapper Plus", Description = @"Use <a href=""https://dapper-plus.net/"" target=""_blank"">Dapper Plus</a> to extend your IDbConnection with high-performance bulk operations.", Column1 = i.ToString(), Column2 = i.ToString(), Column3 = i.ToString(), Column4 = i.ToString(), Column5 = i.ToString(), Column6 = i.ToString(), Column7 = i.ToString(), Column8 = i.ToString(), Column9 = i.ToString() });

            }

            else if(i % 3 == 1)

            {

                products.Add(new Product() { Name = "C# Eval Expression", Description = @"Use <a href=""https://eval-expression.net/"" target=""_blank"">C# Eval Expression</a> to compile and execute C# code at runtime.", Column1 = i.ToString(), Column2 = i.ToString(), Column3 = i.ToString(), Column4 = i.ToString(), Column5 = i.ToString(), Column6 = i.ToString(), Column7 = i.ToString(), Column8 = i.ToString(), Column9 = i.ToString() });

            }

            else if (i % 3 == 2)

            {

                products.Add(new Product() { Name = "Entity Framework Extensions", Description = @"Use <a href=""https://entityframework-extensions.net/"" target=""_blank"">Entity Framework Extensions</a> to extend your DbContext with high-performance bulk operations.", Column1 = i.ToString(), Column2 = i.ToString(), Column3 = i.ToString(), Column4 = i.ToString(), Column5 = i.ToString(), Column6 = i.ToString(), Column7 = i.ToString(), Column8 = i.ToString(), Column9 = i.ToString() });

            }

        }

​

        return products;

    }

    public static void JustInTime_Compile(SqlConnection connection)

    {

        var products = GenerateProducts(20);

        // Dapper

        {

            connection.Execute(@"

MERGE INTO Product AS Target

USING (SELECT @ProductID AS ProductID, @Name AS Name, @Description AS Description,

              @Column1 AS Column1, @Column2 AS Column2, @Column3 AS Column3,

              @Column4 AS Column4, @Column5 AS Column5, @Column6 AS Column6,

              @Column7 AS Column7, @Column8 AS Column8, @Column9 AS Column9) AS Source

ON Target.ProductID = Source.ProductID

WHEN MATCHED THEN

    UPDATE SET

        Target.Name = Source.Name,

        Target.Description = Source.Description,

        Target.Column1 = Source.Column1,

        Target.Column2 = Source.Column2,

        Target.Column3 = Source.Column3,

        Target.Column4 = Source.Column4,

        Target.Column5 = Source.Column5,

        Target.Column6 = Source.Column6,

        Target.Column7 = Source.Column7,

        Target.Column8 = Source.Column8,

        Target.Column9 = Source.Column9

WHEN NOT MATCHED BY TARGET THEN

    INSERT (Name, Description, Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9)

    VALUES (Source.Name, Source.Description, Source.Column1, Source.Column2, Source.Column3, Source.Column4, Source.Column5, Source.Column6, Source.Column7, Source.Column8, Source.Column9);

            ", products);

        }

        // Dapper Plus

        {

            connection.BulkMerge(products);

        }

    }

    [Table("Product")]

    public class Product

    {

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public int ProductID { get; set; }

        public string Name { get; set; }

        public string Description { get; set; }

        public string Column1 { get; set; }

        public string Column2 { get; set; }

        public string Column3 { get; set; }

        public string Column4 { get; set; }

        public string Column5 { get; set; }

        public string Column6 { get; set; }

        public string Column7 { get; set; }

        public string Column8 { get; set; }

        public string Column9 { get; set; }

    }

    public class BenchmarkResult

    {

        public string Action { get; set; }

        public int Entities { get; set; }

        public string Performance { get; set; }     

        public string TimeFaster { get; set; }

        public string ReducedPercent { get; set; }

    }

}