// Dapper Plus

// Doc: https://dapper-tutorial.net/dapper-insert-and-update-in-bulk

​

// @nuget: Dapper -Version 1.60.6

// @nuget: Z.Dapper.Plus

​

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Linq;

using Dapper;

using Z.Dapper.Plus;

using Z.BulkOperations;

​

public class Program

{   

    public static void Main()

    {

        CreateDatabase();

        DapperPlusManager.Entity<Customer>().Table("Customers")

                                         .Identity(x => x.CustomerID);

        var list = GenerateListCustomer();

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

        {       

            connection.BulkInsert(list); 

        }

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

        {

            var customersToUpdate = connection.Query<Customer>("Select TOP 3 * FROM CUSTOMERS").ToList();

            customersToUpdate.ForEach(x => x.Name += "_ExampleBulkUpdate");

            var newCustomers = new List<Customer>()

            {

                new Customer() { Name ="New_Customer_1", Description = "Description", IsActive = true },

                new Customer() { Name ="New_Customer_2", Description = "Description", IsActive = true }

            };

            connection.BulkMerge(customersToUpdate, newCustomers); 

            FiddleHelper.WriteTable(connection.Query<Customer>("Select * FROM CUSTOMERS").ToList());

        }

    }

    public class Customer

    {

        public int CustomerID { get; set; }

        public string Name { get; set; }

        public string Description { get; set; }

        public Boolean IsActive { get; set; }

    }

    public static List<Customer> GenerateListCustomer() 

    {

        var customers = new List<Customer>();

        for (int i = 0; i < 5; i++)

        {

            customers.Add(new Customer() { Name ="Customer_" + i, Description = "Description", IsActive = true });

        }

        return customers;

    }

    public static void CreateDatabase()

    {

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

        {

            connection.Open();

            using (var command = connection.CreateCommand())

            {

                command.CommandText = @"   

CREATE TABLE [dbo].[Customers] (

    [CustomerID]  INT            IDENTITY (1, 1) NOT NULL,

    [Name]        NVARCHAR (MAX) NULL,

    [Description] NVARCHAR (MAX) NULL,

    [IsActive]    BIT            NOT NULL,

    CONSTRAINT [PK_dbo.Customers] PRIMARY KEY CLUSTERED ([CustomerID] ASC)

);

";

                command.ExecuteNonQuery();

            }

        }

    }

}