// @nuget: Dapper -Version 1.60.6

// @nuget: Z.Dapper.Plus

​

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 string ContactName {get;set;}

        public List<Product> Products {get;set;}

    }

    public class Product 

    {

        public int ProductID {get;set;}

        public string ProductName {get;set;}

        public string Unit {get;set;}

        public int SupplierID {get;set;}

    }

    public static List<Supplier> suppliers {get;set;}

    public static void Main()

    {

        suppliers = new List<Supplier>();

        StepMapping();

        StepBulkInsert();

        //After Insert

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

        { 

            FiddleHelper.WriteTable(connection.Query<Product>("select * from Products where ProductName like'%Bulk%'")); 

        }

        StepBulkUpdate();       

        //After Update

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

        {           

            FiddleHelper.WriteTable(connection.Query<Supplier>("Select A.SupplierID, A.SupplierName, A.ContactName FROM Suppliers as A where A.SupplierName = 'BULKUPDATE'"));

            FiddleHelper.WriteTable(connection.Query<Product>("select * from Products where ProductName like'%Bulk%'")); 

        }

        StepBulkMerge();

        //After Merge 

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

        { 

            FiddleHelper.WriteTable(connection.Query<Supplier>("Select A.SupplierID, A.SupplierName, A.ContactName FROM Suppliers as A where A.SupplierName = 'BULKUPDATE'"));

            FiddleHelper.WriteTable(connection.Query<Product>("select * from Products where ProductName like'%Bulk%'")); 

        }

        SteBulkDelete();

        //After Delete

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

        {           

            Console.WriteLine( "Suplier After Delete : " + connection.Query<Supplier>("Select A.SupplierID, A.SupplierName, A.ContactName FROM Suppliers as A where A.SupplierName = 'BULKUPDATE'").ToList().Count);

            Console.WriteLine( "Product After Delete : " + connection.Query<Product>("select * from Products where ProductName = 'BULKUPDATE'").Count());

        }

    }

    public static void StepMapping() 

    {

        // STEP MAPPING

        DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);

        DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);

    }

    public static void StepBulkInsert() 

    {

        suppliers.Add(  new Supplier() { SupplierName = "BulkInsert", ContactName = "BulkInsert", Products = new List<Product> 

        { new Product() {ProductName = "BulkInsert", Unit = "BulkInsert"},new Product() {ProductName = "BulkInsert", Unit = "BulkInsert"} ,new Product() {ProductName = "BulkInsert", Unit = "BulkInsert"}  }});

        // STEP BULKINSERT

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

        {

            connection.BulkInsert(suppliers).ThenForEach(x => x.Products.ForEach(y => y.SupplierID =  x.SupplierID)).ThenBulkInsert(x => x.Products);

        }

    }

    public static void StepBulkMerge() 

    {

        suppliers.Add(new Supplier() { SupplierName = "BulkMerge", ContactName = "BulkMerge", Products = new List<Product>()

        { new Product() { ProductName = "BulkMerge", Unit = "BulkMerge"},  new Product() { ProductName = "BulkMerge" , Unit = "BulkMerge"} ,  new Product() { ProductName = "BulkMerge", Unit = "BulkMerge" }     

        }});

        suppliers.ForEach(x => 

        {

            x.SupplierName = "BULKMERGE";

            x.Products.ForEach(y => y.ProductName = "BULKMERGE");

        }); 

        // STEP BULKMERGE

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

        {

            connection.BulkMerge(suppliers).ThenForEach(x => x.Products.ForEach(y => y.SupplierID =  x.SupplierID)).ThenBulkMerge(x => x.Products);

        }

    }

    public static void StepBulkUpdate() 

    { 

        suppliers.ForEach(x => 

        {

            x.SupplierName = "BULKUPDATE";

            x.Products.ForEach(y => y.ProductName = "BULKUPDATE");

        }); 

        // STEP BULKUPDATE

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

        {

            connection.BulkUpdate(suppliers, x => x.Products);

        }

    }

    public static void SteBulkDelete() 

    {

        // STEP BULKDELETE

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

        {

            connection.BulkDelete(suppliers.SelectMany(x => x.Products)).BulkDelete(suppliers);

        }

    }

}