// Dapper Plus

// Doc: https://dapper-plus.net/save-audit-history-in-a-database

​

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

        SeedDatabase();

        // Mapping

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

                                         .Identity(x => x.CustomerID);

        DapperPlusManager.Entity<AuditLog>().Table("AuditLog")

                                         .Identity(x => x.Id);

        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());

        // Execute

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

        connection.UseBulkOptions(options => 

        { 

            options.UseAudit = true;

            options.AuditEntries = auditEntries; 

        })

        .BulkMerge(list);       

        SaveAuditLogs(auditEntries);

​

        // Result

        FiddleHelper.WriteTable(connection.Query<AuditLog>("SELECT * FROM AuditLog"));

    }

    private static void SaveAuditLogs(List<AuditEntry> auditEntries)

    {

        List<AuditLog> auditLogs = new List<AuditLog>();

        foreach (var auditEntry in auditEntries)

        {

            AuditLog log = new AuditLog();

            log.Action = auditEntry.Action.ToString();                       

            log.TableName = auditEntry.TableName;

            log.Values = string.Join("|", auditEntry.Values.Select(x => x.ColumnName + ";" + (x.OldValue ?? "") + ";" + (x.NewValue ?? "")));

            log.Date = auditEntry.Date;

            log.UserId = "my-user-id";

            auditLogs.Add(log);

        }

​

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

        connection.BulkInsert<AuditLog>(auditLogs);

    }

    public class Customer

    {

        public int CustomerID { get; set; }

        public string Name { get; set; }

        public string Description { get; set; }

        public Boolean IsActive { get; set; }

    }

    public class AuditLog

    {

        public int Id { get; set; }

        public string Action { get; set; }

        public string TableName { get; set; }

        public string Values { get; set; }

        public DateTime Date { get; set; }

        public string UserId { get; set; }

    }

    public static List<Customer> GenerateListCustomer() 

    {

        var customers = new List<Customer>();

        for (int i = 0; i < 2; 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].[Customer] (

    [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)

);

​

CREATE TABLE [dbo].[AuditLog] (

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

    [Action]     NVARCHAR (MAX) NULL,

    [TableName]  NVARCHAR (MAX) NULL,

    [Values]     NVARCHAR (MAX) NULL,

    [Date]       DATETIME       NOT NULL,

    [UserId]     NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_dbo.AuditLogs] PRIMARY KEY CLUSTERED ([Id] 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 });

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

        connection.BulkInsert(list);

    }

}