// Dapper Plus

// Doc: https://www.learndapper.com/dapper-query/selecting-multiple-results

​

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

    {

        CreateDatabaseAndDataSeeding();

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

        string sql = @"

SELECT * FROM [Order] WHERE OrderID = @orderID;

SELECT * FROM [OrderItem] WHERE OrderID = @orderID;

        ";

         using (var multi = connection.QueryMultiple(sql, new {orderID = 1}))

         {

             var order = multi.ReadFirst<Order>();

             var orderItems = multi.Read<OrderItem>().ToList();

             FiddleHelper.WriteTable("Order", new List<Order>() { order});

             FiddleHelper.WriteTable("OrderItem", orderItems);

         }

    }

    public static void CreateDatabaseAndDataSeeding()

    {

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

        var context = new DapperPlusContext(connection);

        // Map your entity

        context.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);     

        context.Entity<Order>()

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

        context.Entity<OrderItem>()

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

​

        // CREATE Table (from Dapper Plus)

        connection.CreateTable<Customer>();

        connection.CreateTable<Order>();

        connection.CreateTable<OrderItem>();

​

        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 = "Entity Framework Extensions", Quantity = 19 }  

            }

                        }}});

​

        // 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 = "C# Eval Expression", Quantity = 14 }

            }

        }}});

        context.BulkMerge("KeepIdentity", customers)

            .AlsoBulkInsert(customer => customer.Orders)

            .AlsoBulkInsert(customer => customer.Orders.SelectMany(y => y.Items));

    }

    [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; }

    }

    [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; }

    }

}