// Dapper Plus

// Doc: https://dapper-plus.net/overview

​

// @nuget: Dapper

// @nuget: Microsoft.Data.SqlClient

// @nuget: Z.Dapper.Plus

​

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

using System.Linq;

using Microsoft.Data.SqlClient;

using Dapper;

using Z.Dapper.Plus;

​

public class Program

{   

    public static void Main()

    {

        // Map your entity

        DapperPlusManager.Entity<Customer>("KeepIdentity") // We will learn about mapping key such as "KeepIdentity" later

            .UseBulkOptions(options => {

                options.InsertKeepIdentity = true;

                options.MergeKeepIdentity = true;

            })

            .Identity(x => x.CustomerID, true);     

        DapperPlusManager.Entity<Order>()

            .Identity(x => x.OrderID, true);        

        DapperPlusManager.Entity<OrderItem>()

            .Identity(x => x.OrderItemID, true);

        DapperPlusManager.Entity<Invoice>()

            .Identity(x => x.InvoiceID, true);      

        DapperPlusManager.Entity<InvoiceItem>()

            .Identity(x => x.InvoiceItemID, true);

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

        connection.CreateTable<Customer>();

        connection.CreateTable<Order>();

        connection.CreateTable<OrderItem>();

        connection.CreateTable<Invoice>();

        connection.CreateTable<InvoiceItem>();

        // Seed customer

        connection.SingleInsert("KeepIdentity", new Customer() { CustomerID = 13, Name = "Jonathan Magnan" });

        // Get new customers, orders and invoices from an external system

        var newCustomerOrders = GetNewCustomerOrders();

        // Save new customers and orders and invoices

        connection.BulkMerge("KeepIdentity", newCustomerOrders)

                  .Include(c => c.ThenBulkInsert(customer => customer.Orders)

                                 .AlsoBulkInsert(order => order.Items))

                  .Include(c => c.ThenBulkInsert(customer => customer.Invoices)

                                 .AlsoBulkInsert(invoice => invoice.Items));                  

        FiddleHelper.WriteTable("1 - Customers", connection.Query<Customer>("SELECT * FROM [Customer]"));

        FiddleHelper.WriteTable("2 - Orders", connection.Query<Order>("SELECT * FROM [Order]"));

        FiddleHelper.WriteTable("3 - OrderItems", connection.Query<OrderItem>("SELECT * FROM [OrderItem]"));

        FiddleHelper.WriteTable("4 - Invoice", connection.Query<Invoice>("SELECT * FROM [Invoice]"));

        FiddleHelper.WriteTable("5 - InvoiceItems", connection.Query<InvoiceItem>("SELECT * FROM [InvoiceItem]"));

    }

    public static List<Customer> GetNewCustomerOrders()

    {

        var customers = new List<Customer>();

        // customer #1

        customers.Add(new Customer() { CustomerID = 13, Name = "Jonathan Magnan", Orders = new List<Order>() {

            new Order() { State = "New", Items = new List<OrderItem>() {

                new OrderItem() { ProductName = "Dapper Plus", Quantity = 9 },

                new OrderItem() { ProductName = "C# Eval Expression", Quantity = 14 }   

            }}},

            Invoices = new List<Invoice>() {

            new Invoice() { TotalPrice = 2198, Items = new List<InvoiceItem>() {

                new InvoiceItem() { UnitPrice = 155.44m, Quantity = 9 },

                new InvoiceItem() { UnitPrice = 47.07m, Quantity = 14 } 

            }}}

        });

        // customer #2

        customers.Add(new Customer() { CustomerID = 33, Name = "ZZZ Projects", Orders = new List<Order>() {

            new Order() { State = "New", Items = new List<OrderItem>() {

                new OrderItem() { ProductName = "Entity Framework Extensions", Quantity = 19 }  

            }}},

            Invoices = new List<Invoice>() {

            new Invoice() { TotalPrice = 1799, Items = new List<InvoiceItem>() {

                new InvoiceItem() { UnitPrice = 94.68m, Quantity = 19 }

            }}}

        });

        return customers;

    }

    [Table("Customer")]

    public class Customer

    {

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public int CustomerID { get; set; }

        public string Name { get; set; }

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

        public List<Invoice> Invoices { get; set; }

    }

    [Table("Order")]

    public class Order

    {

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public int OrderID { get; set; }

        public int CustomerID { get; set; }

        public string State { get; set; }   

        public List<OrderItem> Items { get; set; }

    }

    [Table("OrderItem")]

    public class OrderItem

    {

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public int OrderItemID { get; set; }

        public int OrderID { get; set; }

        public string ProductName { get; set; }

        public int Quantity { get; set; }

    }

    [Table("Invoice")]

    public class Invoice

    {

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public int InvoiceID { get; set; }

        public int CustomerID { get; set; }

        public decimal TotalPrice { get; set; }

        public List<InvoiceItem> Items { get; set; }

    }

    [Table("InvoiceItem")]

    public class InvoiceItem

    {

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public int InvoiceItemID { get; set; }

        public int InvoiceID { get; set; }

        public decimal UnitPrice { get; set; }

        public int Quantity { get; set; }

    }

}