// Dapper Plus

// Doc: https://dapper-tutorial.net/bulk-merge

​

// @nuget: Dapper -Version 1.60.6

// @nuget: Z.Dapper.Plus

// @nuget: System.Data.SqlClient

​

using Dapper;

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using Z.Dapper.Plus;

using System.Linq;

public class Program

{       

    public class Supplier 

    {

        public int SupplierID {get;set;}

        public string SupplierName {get;set;}

        public Product Product {get;set;}

    }

    public class Product 

    {

        public int ProductID {get;set;}

        public string ProductName {get;set;}

        public int SupplierID {get;set;}

    }

    public static void Main()

    {

        List<Supplier> suppliers = new List<Supplier>() { new Supplier() { SupplierName = "ExampleSupplierBulkMerge", Product = new Product() { ProductName = "ExampleProductBulkMerge" }  }};

        Console.WriteLine("ID of the new Supplier, before Merge : " + suppliers.First().SupplierID);    

​

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

        {

            suppliers.AddRange(connection.Query<Supplier, Product, Supplier>("Select TOP 1 A.SupplierID, A.SupplierName, B.ProductID, B.ProductName FROM Suppliers as A inner join Products as B on B.SupplierID =A.SupplierID where productID = 24",(supplier, product) => { supplier.Product = product; return supplier; },splitOn: "ProductID").ToList());   

        }

        suppliers.ForEach(x => x.SupplierName = "ExampleSupplierBulkMerge" );

        suppliers.ForEach(x => x.Product.ProductName = "ExampleProductBulkMerge" );

        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.BulkMerge(suppliers).ThenForEach(x => x.Product.SupplierID = x.SupplierID).ThenBulkMerge(x => x.Product);

        }   

        Console.WriteLine("ID of the new Supplier, after Merge : " + suppliers.First().SupplierID);     

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

        {

            FiddleHelper.WriteTable(connection.Query<Supplier>("Select TOP 10 * FROM Suppliers WHERE SupplierName = 'ExampleSupplierBulkMerge'").ToList());

            FiddleHelper.WriteTable(connection.Query<Product>("Select TOP 10 * FROM Products WHERE ProductName = 'ExampleProductBulkMerge'").ToList());

            // For see all Colum of Table, with one row.

            FiddleHelper.WriteTable(connection.QueryFirstOrDefault("Select * FROM Suppliers WHERE SupplierName = 'ExampleSupplierBulkMerge' order by SupplierID"));

            // For see all Colum of Table, with one row.

            FiddleHelper.WriteTable(connection.QueryFirstOrDefault("Select * FROM Products WHERE ProductName = 'ExampleProductBulkMerge'  order by ProductID"));    

        }

    }

}