// Dapper Plus

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

​

// @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 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 void Main()

    {

        List<Supplier> suppliers = new List<Supplier>() { new Supplier() { SupplierName = "ExampleSupplierBulkInsert", Products = new List<Product> 

        { new Product() {ProductName = "ExampleProductBulkInsert", Unit = "1"},new Product() {ProductName = "ExampleProductBulkInsert", Unit = "2"} ,new Product() {ProductName = "ExampleProductBulkInsert", Unit = "3"}  }}};

        FiddleHelper.WriteTable(suppliers);

        FiddleHelper.WriteTable(suppliers.FirstOrDefault().Products);

        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.BulkInsert(suppliers).ThenForEach(x => x.Products.ForEach(y => y.SupplierID =  x.SupplierID)).ThenBulkInsert(x => x.Products);

        }

        suppliers = null;

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

        {

            var supplierDictionary = new Dictionary<int, Supplier>();

            suppliers = connection.Query<Supplier, Product, Supplier>("Select A.SupplierID, A.SupplierName, B.ProductID, B.ProductName FROM Suppliers as A inner join Products as B on B.SupplierID =A.SupplierID where A.SupplierName = 'ExampleSupplierBulkInsert'",

            (supplier, product) => 

            {  

                Supplier supplierEntry;

                    if (!supplierDictionary.TryGetValue(supplier.SupplierID, out supplierEntry))

                    {

                        supplierEntry = supplier;

                        supplierEntry.Products = new List<Product>();

                        supplierDictionary.Add(supplier.SupplierID, supplierEntry);

                    }

​

                    supplierEntry.Products.Add(product);

                    return supplierEntry;

                }, splitOn: "ProductID").Distinct().ToList();

            FiddleHelper.WriteTable(suppliers);

            FiddleHelper.WriteTable(connection.Query<Product>("select * from Products where ProductName = 'ExampleProductBulkInsert'"));

            FiddleHelper.WriteTable(connection.QueryFirstOrDefault("select * from Products where ProductName = 'ExampleProductBulkInsert' order by ProductID"));

        }

    }

}