// Bulk Operations

// Doc: https://bulk-operations.net/audit-mode

​

// @nuget: Z.BulkOperations

// @nuget: System.Data.SqlClient -Version 4.6.0.0

​

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Linq;

using Z.BulkOperations;

​

public class Program

{   

    public static void Main()

    {

        CreateDatabase();

        SeedDatabase();

        Execute();

    }

    public static void Execute()

    {

        // We will execute a BulkMerge on a list that contains 1 new customer and 2 existing customers.

        var list = new List<Customer>();

        list.Add(new Customer() { Name = "Customer_C_Inserted", Description = "Description_Customer_C_Inserted", IsActive = false });

        list.Add(new Customer() { CustomerID = 1, Name = "Customer_A_Updated", Description = "Description_Customer_A_Updated", IsActive = false });

        list.Add(new Customer() { CustomerID = 2, Name = "Customer_B_Updated", Description = "Description_Customer_B_Updated", IsActive = false });

​

​

        // Connection

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

        connection.Open();

        // Execute

        List<AuditEntry> auditEntries = new List<AuditEntry>();         

        using (var bulk = new BulkOperation(connection))

        {

            bulk.DestinationTableName = "Customer";

            bulk.UseAudit = true;           

​

            bulk.AuditMode = AuditModeType.ExcludeAll; 

            bulk.ColumnMappings.Add(

                new ColumnMapping<Customer>("CustomerID", true)

                {

                    AuditMode = ColumnMappingAuditModeType.Include

                }

            );

            bulk.ColumnMappings.Add(

                new ColumnMapping<Customer>("Name")

                {

                    AuditMode = ColumnMappingAuditModeType.Include

                }

            );

            bulk.BulkMerge(list);

            auditEntries = bulk.AuditEntries;

        }

​

        // Result

        FiddleHelper.WriteTable(auditEntries.SelectMany(x => x.Values));

    }

    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 void CreateDatabase()

    {

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

        {

            connection.Open();

            using (var command = connection.CreateCommand())

            {

                command.CommandText = @"   

CREATE TABLE [dbo].[Customer] (

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

    [Name]        NVARCHAR (MAX) NULL,

    [Description] NVARCHAR (MAX) NULL,

    [IsActive]    BIT            NULL,

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

);

";

                command.ExecuteNonQuery();

            }

        }

    }

    public static void SeedDatabase()

    {

        var list = new List<Customer>();

        list.Add(new Customer() { Name = "Customer_A", Description = "Description_Customer_A", IsActive = false });

        list.Add(new Customer() { Name = "Customer_B", Description = "Description_Customer_B", IsActive = false });

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

        {

            connection.Open();

            using (var bulk = new BulkOperation(connection))

            {

                bulk.DestinationTableName = "Customer";

                bulk.BulkInsert(list);

            }

        }

    }

}