// Dapper Plus

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

​

// @nuget: Dapper -Version 1.60.6

// @nuget: Z.Dapper.Plus

​

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Diagnostics;

using Dapper;

using Z.Dapper.Plus;

​

public class Program

{

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

    public static void Main()

    {

        CreateDatabase();

        DapperPlusManager.Entity<Order>().Table("Orders");

        JustInTime_Compile();

​

        // Benchmark

        var BenchmarkResults = new List<BenchmarkResult>();

        var clockExecuteMany = new Stopwatch();

        var clockSingleInsert = new Stopwatch();        

        var clockBulkInsert = new Stopwatch();      

        // Generate X orders

        var orders = GenerateOrders(1000);

​

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

        // Execute (Many)

        clockExecuteMany.Start();

        connection.Execute("INSERT INTO Orders (Number, TotalPrice, TotalQuantity) VALUES (@NUmber, @TotalPrice, @TotalQuantity)", orders);

        clockExecuteMany.Stop();

        BenchmarkResults.Add(new BenchmarkResult() { Action = "Execute (Many)", Entities = orders.Count, Performance = clockExecuteMany.ElapsedMilliseconds + " ms" });

        // SingleInsert

        clockSingleInsert.Start();

        orders.ForEach(x => connection.SingleInsert(x));

        clockSingleInsert.Stop();   

        BenchmarkResults.Add(new BenchmarkResult() { Action = "SingleInsert", Entities = orders.Count, Performance = clockSingleInsert.ElapsedMilliseconds + " ms" });

        // BulkInsert

        clockBulkInsert.Start();

        connection.BulkInsert(orders);

        clockBulkInsert.Stop(); 

        BenchmarkResults.Add(new BenchmarkResult() { Action = "BulkInsert", Entities = orders.Count, Performance = clockBulkInsert.ElapsedMilliseconds + " ms" });

​

        FiddleHelper.WriteTable(BenchmarkResults);

    }

    public class Order 

    {

        public int OrderID { get; set; }

        public string Number { get; set; }

        public decimal TotalPrice { get; set; }

        public int TotalQuantity { get; set; }

    }

    public static List<Order> GenerateOrders(int count)

    {

        var orders = new List<Order>();

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

        {

            orders.Add(new Order() { Number = "Order_" + i, TotalPrice = 1 + i * 1.1m, TotalQuantity = 1 + i });

        }

        return orders;

    }

    public static void JustInTime_Compile()

    {

        var orders = GenerateOrders(20);

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

        // Execute (Many)

        connection.Execute("INSERT INTO Orders (Number, TotalPrice, TotalQuantity) VALUES (@NUmber, @TotalPrice, @TotalQuantity)", orders);

​

        // SingleInsert (Free)

        orders.ForEach(x => connection.SingleInsert(x));

        // BulkInsert

        connection.BulkInsert(orders);

        // Clean

        connection.Execute("DELETE FROM Orders");

    }

    public static void CreateDatabase()

    {

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

        {

            connection.Open();

            using (var command = connection.CreateCommand())

            {

                command.CommandText = @"   

CREATE TABLE [Orders]

(

    [OrderID] [INT] IDENTITY(1,1) NOT NULL,

    [Number] [VARCHAR](20) NULL,

    [TotalPrice] [Money] NULL,

    [TotalQuantity] [int] NULL,

    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 

    (

        [OrderID] ASC

    )

) ";

                command.ExecuteNonQuery();

            }

        }

    }

    public class BenchmarkResult

    {

        public string Action { get; set; }

        public int Entities { get; set; }

        public string Performance { get; set; }

    }

}