// 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.Linq.Expressions;

using System.Data.SqlClient;

using Dapper;

using Z.Dapper.Plus;

​

public class Program

{   

    public static void Main()

    {

        CreateDatabase();

        // MAP

        DapperPlusManager.Entity<Execution>().Table("Executions").Identity(x => x.ID)

            .AfterAction((kind, execution) =>

                         {

                             if (kind == DapperPlusActionKind.Insert || kind == DapperPlusActionKind.Merge)

                             {

                                 if (execution.Orders != null)

                                 {

                                     execution.Orders.ForEach(x => x.ExecutionID = execution.ID);

                                 }

​

                                 if (execution.Fills != null)

                                 {

                                     execution.Fills.ForEach(x => x.ExecutionID = execution.ID);

                                 }

                             }

                         });

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

        DapperPlusManager.Entity<Fill>().Table("Fills");

​

        // SEED

        {

            var executions = new List<Execution>();

            {

                var execution = new Execution() { ColumnInt = 1, Modified = DateTime.Now.AddDays(-2)};

                execution.Orders = new List<Order>() { new Order() { ColumnInt = 1, Modified = DateTime.Now.AddDays(-2) } };

                execution.Fills = new List<Fill>() { new Fill() { ColumnInt = 1, Modified = DateTime.Now.AddDays(-2) } };

​

                executions.Add(execution);

            }

​

            Expression<Func<Execution, object>> test = (x) => new { x.Modified };

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

            conn.Open();

            var trans = conn.BeginTransaction();

​

            trans.UseBulkOptions(o =>

                                 {

                                     o.LambdaIgnoreOnMergeMatchedAndOneNotConditionExpression = test;

                                 })

                .BulkMerge(executions)

                .AlsoBulkMerge(e => e.Orders)

                .AlsoBulkMerge(e => e.Fills);

​

            trans.Commit();

            FiddleHelper.WriteTable("1 - Executions Before", conn.Query<Execution>("SELECT * FROM Executions"));

            FiddleHelper.WriteTable("2 - Orders Before", conn.Query<Order>("SELECT * FROM Orders"));

            FiddleHelper.WriteTable("3 - Fills Before", conn.Query<Fill>("SELECT * FROM Fills"));

        }

​

        // TEST

        {

            var executions = new List<Execution>();

            {

                var execution = new Execution() {ID = 1, ColumnInt = 2, Modified = DateTime.Now};

                execution.Orders = new List<Order>() { new Order() { ID = 1, ColumnInt = 1, Modified = DateTime.Now } };

                execution.Fills = new List<Fill>() { new Fill() { ID = 1, ColumnInt = 2, Modified = DateTime.Now } };

​

                executions.Add(execution);

            }

​

            Expression<Func<Execution, object>> test = (x) => new { x.Modified };

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

            conn.Open();

            var trans = conn.BeginTransaction();

​

            trans.UseBulkOptions(o =>

                                 {

                                     o.LambdaIgnoreOnMergeMatchedAndOneNotConditionExpression = test;

                                 })

                .BulkMerge(executions)

                .AlsoBulkMerge(e => e.Orders)

                .AlsoBulkMerge(e => e.Fills);

​

            trans.Commit();

            FiddleHelper.WriteTable("4 - Executions Before", conn.Query<Execution>("SELECT * FROM Executions"));

            FiddleHelper.WriteTable("5 - Orders Before", conn.Query<Order>("SELECT * FROM Orders"));

            FiddleHelper.WriteTable("6 - Fills Before", conn.Query<Fill>("SELECT * FROM Fills"));

        }

    }

    public class Execution

    {

        public int ID { get; set; }

        public int ColumnInt { get; set; }

        public DateTime Modified { get; set; }

        public List<Order> Orders { get; set; }

        public List<Fill> Fills { get; set; }

    }

​

    public class Order

    {

        public int ID { get; set; }

        public int ColumnInt { get; set; }

        public DateTime Modified { get; set; }

        public int ExecutionID { get; set; }

    }

​

    public class Fill

    {

        public int ID { get; set; }

        public int ColumnInt { get; set; }

        public DateTime Modified { get; set; }

        public int ExecutionID { get; set; }

    }

    public static void CreateDatabase()

    {

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

        {

            connection.Open();

            using (var command = connection.CreateCommand())

            {

                command.CommandText = @"   

CREATE TABLE [dbo].[Executions](

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

    [ColumnInt] [INT] NULL,

    [Modified] [DATETIME2](7) NULL,

 CONSTRAINT [PK_Executions] PRIMARY KEY CLUSTERED 

(

    [ID] ASC

))

​

CREATE TABLE [dbo].[Orders](

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

    [ColumnInt] [INT] NULL,

    [Modified] [DATETIME2](7) NULL,

 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 

(

    [ID] ASC

))

​

CREATE TABLE [dbo].[Fills](

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

    [ColumnInt] [INT] NULL,

    [Modified] [DATETIME2](7) NULL,

 CONSTRAINT [PK_Fills] PRIMARY KEY CLUSTERED 

(

    [ID] ASC

))

 ";

                command.ExecuteNonQuery();

            }

        }

    }

}