Step-by-Step Guide to Integrating Audit Trail in ASP.NET Core Using ADO.NET and Raw SQL

14 minute read

Introduction The Audit Trail stands out as a critical feature in enterprise applications, primarily serving essential roles in security and fraud transaction detection. An audit trail is a chronological record of changes or activities performed in a system, providing a detailed history of events. In the context of software applications, including ASP.NET, an audit trail captures and logs various actions such as data modifications, user interactions, and system activities. This article delves into a comprehensive guide on implementing Audit Trail functionality in an ASP.NET Core application using SQL Server with ADO.NET.

Why it is important? It helps organizations maintain a record of user activity within their systems, which can be used

To monitor user actions and detect suspicious activities
To track changes and identify the root cause of issues
To ensure compliance with regulatory requirements
Records changes to critical data elements, ensuring accuracy and consistency
Provides a detailed history of events for forensic analysis
Meets legal and regulatory requirements by maintaining a detailed audit trail
Offers historical data for reporting and analysis, supporting decision-making
Frud detection etc.

In this article, we will discuss how you can implement an audit trail feature using Entity Framework plus

Tools and Technology Used

ASP.net core Web API
Visual C#
ADO.NET
SQL Server

Step 1: Create Database and related tables in MS SQL Server

Create a database name AuditLogDB
Create a table name Product to store Product information
Create a AuditEntry and AuditEntryProperty table to store audit information.

Product Table

CREATE TABLE [dbo].[Products](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

AuditEntry Table

CREATE TABLE [dbo].[AuditEntry](
	[AuditEntryID] [int] IDENTITY(1,1) NOT NULL,
	[EntitySetName] [nvarchar](255) NULL,
	[EntityTypeName] [nvarchar](255) NULL,
	[State] [int] NOT NULL,
	[StateName] [nvarchar](255) NULL,
	[CreatedBy] [nvarchar](255) NULL,
	[CreatedDate] [datetime2](7) NOT NULL,
	[Discriminator] [nvarchar](max) NOT NULL,
	[AppplicationName] [nvarchar](max) NULL,
 CONSTRAINT [PK_AuditEntry] PRIMARY KEY CLUSTERED 
(
	[AuditEntryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

AuditEntryProperty Table

CREATE TABLE [dbo].[AuditEntryProperty](
	[AuditEntryPropertyID] [int] IDENTITY(1,1) NOT NULL,
	[AuditEntryID] [int] NOT NULL,
	[RelationName] [nvarchar](255) NULL,
	[PropertyName] [nvarchar](255) NULL,
	[OldValue] [nvarchar](max) NULL,
	[NewValue] [nvarchar](max) NULL,
	[Discriminator] [nvarchar](max) NOT NULL,
	[AppplicationName] [nvarchar](max) NULL,
 CONSTRAINT [PK_AuditEntryProperty] PRIMARY KEY CLUSTERED 
(
	[AuditEntryPropertyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditEntryProperty]  WITH CHECK ADD  CONSTRAINT [FK_AuditEntryProperty_AuditEntry_AuditEntryID] FOREIGN KEY([AuditEntryID])
REFERENCES [dbo].[AuditEntry] ([AuditEntryID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[AuditEntryProperty] CHECK CONSTRAINT [FK_AuditEntryProperty_AuditEntry_AuditEntryID]
GO

Step 2: Create a asp.net core web api project name AuditLog.API

Step 3: Install the following nuget packages in the project.

System.Data.SqlClient

Step 4: Create a Model class name Product in Models folder.

Product class to store Product Information

Product.cs

namespace AuditLog.API.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; } = String.Empty;
        public decimal Price { get; set; } = 0;
        public int Quantity { get; set; } = 0;
    }
}

Step 5: Create a DBConnector class in Persistence folder to create connection to the database

DBConnector.cs

using System.Data.SqlClient;

namespace AuditLog.API.Persistence
{
    // This class is used to create a connection to the database.
    public class DBConnector : IDisposable
    {
        public SqlConnection connection;

        public DBConnector(string connectionString)
        {
            connection = new SqlConnection(connectionString);
            this.connection.Open();
        }

        public void Dispose()
        {
            connection.Close();
        }
    }
}

Step 6: Add connection string in appsettings.json file

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=AuditLogDB;User Id=sa;Password=YourPassword;TrustServerCertificate=True;"
  },
  "AllowedHosts": "*"
}

Step 7: Create classes and enums for Audit Trail in AuditTrail folder

Create ApplicationEnum, StateName, EnumExtesions class in AuditTrail/Enums folder

ApplicationEnum.cs

using System.ComponentModel;

namespace AuditLog.API.AuditTrail.Enums
{
    // This enum is used to identify the names of applications that is being audited.
    public enum ApplicationEnum
    {
        [Description("AuditLog.API")]
        AuditLogAPP,

        [Description("ECommerce Application")]
        ECommerceAPP
    }
}

StateName.cs

namespace AuditLog.API.AuditTrail.Enums
{
    // State of the operation performed on the entity.
    public enum StateName
    {
        EntityAdded,
        EntityModified,
        EntityDeleted
    }
}

EnumExtensions.cs

using System.ComponentModel;
using System.Reflection;

namespace AuditLog.API.AuditTrail.Enums
{
    // This enum is used to identify the names of applications that is being audited.
    public static class EnumExtensions
    {
        public static string GetApplication(this Enum value)
        {
            FieldInfo? fieldInfo = value.GetType().GetField(value.ToString());

            DescriptionAttribute? attribute = fieldInfo?.GetCustomAttribute<DescriptionAttribute>();

            return attribute != null ? attribute.Description : value.ToString();
        }

        public static string GetEntity(this Enum value)
        {
            FieldInfo? fieldInfo = value.GetType().GetField(value.ToString());

            DescriptionAttribute? attribute = fieldInfo?.GetCustomAttribute<DescriptionAttribute>();

            return attribute != null ? attribute.Description : value.ToString();
        }
    }
}

Create IAuditTrail Interface in AuditTrail/Interface folder

IAuditTrail.cs

namespace AuditLog.API.AuditTrail.Interfaces
{
    public interface IAuditTrail<T>
    {
        void Insert(T entity);
        void Update(T oldEntity, T newEntity);
        void Delete(T oldEntity);
    }
}

Create AuditTrail class in AuditTrail/Implementaion folder

AuditTrail.cs

using AuditLog.API.AuditTrail.Enums;
using AuditLog.API.AuditTrail.Interfaces;
using AuditLog.API.Persistence;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace AuditLog.API.AuditTrail.Implementations
{
    // This class is used to insert audit log into database.
    // This is the core class of the AuditTrail project.
    public class AuditTrail<T> : IAuditTrail<T> where T : class
    {
        private DBConnector _connector;
        public AuditTrail(DBConnector dBConnector)
        {
            _connector = dBConnector;
        }

        public void Insert(T entity)
        {
            if (entity == null)
            {
                return;
            }


            Type type = typeof(T);
            PropertyInfo[] propertyInfos = type.GetProperties();
            string applicationName = ApplicationEnum.AuditLogAPP.GetApplication();

            var cmd = _connector.connection.CreateCommand() as SqlCommand;
            SqlTransaction transaction = _connector.connection.BeginTransaction("");
            cmd.Transaction = transaction;


            try
            {

                // Insert into AuditEntry table
                cmd.CommandText = @"INSERT INTO [AuditEntry] (EntitySetName, EntityTypeName, State, StateName, CreatedBy, CreatedDate, Discriminator, AppplicationName) 
                                    OUTPUT inserted.AuditEntryID
                                    VALUES (@EntitySetName, @EntityTypeName, @State,@StateName, @CreatedBy, @CreatedDate, @Discriminator, @AppplicationName);
                                    SELECT SCOPE_IDENTITY();";

                cmd.Parameters.AddWithValue("@EntitySetName", type.Name);
                cmd.Parameters.AddWithValue("@EntityTypeName", type.Name);
                cmd.Parameters.AddWithValue("@State", StateName.EntityAdded); // 0 for insert
                cmd.Parameters.AddWithValue("@StateName", StateName.EntityAdded.ToString()); // EntityAdded for insert
                cmd.Parameters.AddWithValue("@CreatedBy", "mahedee"); // It will come from session
                cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
                cmd.Parameters.AddWithValue("@Discriminator", "CustomAuditEntry"); // CustomAuditEntry Model
                cmd.Parameters.AddWithValue("@AppplicationName", applicationName); // Name of the application

                int auditEntryId = Convert.ToInt32(cmd.ExecuteScalar());

                cmd.Parameters.Clear();


                // Insert into AuditEntryProperty table
                // Values of each property

                cmd.CommandText = @"INSERT INTO [AuditEntryProperty] (AuditEntryID, RelationName, PropertyName, OldValue, NewValue, Discriminator, AppplicationName) 
                                    VALUES (@AuditEntryID, @RelationName, @PropertyName, @OldValue, @NewValue, @DiscriminatorProperty, @AppplicationNameProperty);
                                    SELECT SCOPE_IDENTITY();";

                cmd.Parameters.Add(new SqlParameter("@AuditEntryID", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@RelationName", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@PropertyName", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@OldValue", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@NewValue", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@DiscriminatorProperty", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@AppplicationNameProperty", SqlDbType.NVarChar));


                foreach (PropertyInfo propertyInfo in propertyInfos)
                {
                    cmd.Parameters["@AuditEntryID"].Value = auditEntryId;
                    cmd.Parameters["@RelationName"].Value = DBNull.Value;
                    cmd.Parameters["@PropertyName"].Value = propertyInfo.Name; // property name
                    cmd.Parameters["@OldValue"].Value = DBNull.Value; // Null for insert
                    cmd.Parameters["@NewValue"].Value = propertyInfo.GetValue(entity); // property value
                    cmd.Parameters["@DiscriminatorProperty"].Value = "CustomAuditEntryProperty";
                    cmd.Parameters["@AppplicationNameProperty"].Value = applicationName;
                    cmd.ExecuteScalar();
                }

                // Commit transaction
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
        }

        public void Update(T oldEntity, T newEntity)
        {
            if (oldEntity is null || newEntity is null)
            {
                return;
            }

            // Get the properties from type
            Type type = typeof(T);
            PropertyInfo[] propertyInfos = type.GetProperties();
            string applicationName = ApplicationEnum.AuditLogAPP.GetApplication();

            var cmd = _connector.connection.CreateCommand() as SqlCommand;
            SqlTransaction transaction = _connector.connection.BeginTransaction("");
            cmd.Transaction = transaction;


            try
            {

                // Insert into AuditEntry
                cmd.CommandText = @"INSERT INTO [AuditEntry] (EntitySetName, EntityTypeName, State, StateName, CreatedBy, CreatedDate, Discriminator, AppplicationName) 
                                    OUTPUT inserted.AuditEntryID
                                    VALUES (@EntitySetName, @EntityTypeName, @State,@StateName, @CreatedBy, @CreatedDate, @Discriminator, @AppplicationName);
                                    SELECT SCOPE_IDENTITY();";

                cmd.Parameters.AddWithValue("@EntitySetName", type.Name);
                cmd.Parameters.AddWithValue("@EntityTypeName", type.Name);
                cmd.Parameters.AddWithValue("@State", StateName.EntityModified); // 1 for update
                cmd.Parameters.AddWithValue("@StateName", StateName.EntityModified.ToString()); // EntityAdded for update // Come from an enum
                cmd.Parameters.AddWithValue("@CreatedBy", "mahedee"); // It will come from session
                cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
                cmd.Parameters.AddWithValue("@Discriminator", "CustomAuditEntry"); // CustomAuditEntry Model
                cmd.Parameters.AddWithValue("@AppplicationName", applicationName); // Name of the application // Get from config enum

                int auditEntryId = Convert.ToInt32(cmd.ExecuteScalar());

                cmd.Parameters.Clear();


                // Insert into AuditEntryProperty
                // Values of each property

                cmd.CommandText = @"INSERT INTO [AuditEntryProperty] (AuditEntryID, RelationName, PropertyName, OldValue, NewValue, Discriminator, AppplicationName) 
                                    VALUES (@AuditEntryID, @RelationName, @PropertyName, @OldValue, @NewValue, @DiscriminatorProperty, @AppplicationNameProperty);
                                    SELECT SCOPE_IDENTITY();";

                cmd.Parameters.Add(new SqlParameter("@AuditEntryID", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@RelationName", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@PropertyName", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@OldValue", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@NewValue", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@DiscriminatorProperty", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@AppplicationNameProperty", SqlDbType.NVarChar));


                foreach (PropertyInfo propertyInfo in propertyInfos)
                {


                    cmd.Parameters[0].Value = auditEntryId;
                    cmd.Parameters[1].Value = DBNull.Value;
                    cmd.Parameters[2].Value = propertyInfo.Name; // property name
                    cmd.Parameters[3].Value = propertyInfo.GetValue(oldEntity); // old value
                    cmd.Parameters[4].Value = propertyInfo.GetValue(newEntity); // new value
                    cmd.Parameters[5].Value = "CustomAuditEntryProperty";
                    cmd.Parameters[6].Value = applicationName;
                    cmd.ExecuteScalar();
                }

                // Commit transaction
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
        }

        public void Delete(T oldEntity)
        {
            if (oldEntity == null)
            {
                return;
            }


            // Get the properties from type
            Type type = typeof(T);
            PropertyInfo[] propertyInfos = type.GetProperties();
            string applicationName = ApplicationEnum.AuditLogAPP.GetApplication();

            var cmd = _connector.connection.CreateCommand() as SqlCommand;
            SqlTransaction transaction = _connector.connection.BeginTransaction("");
            cmd.Transaction = transaction;


            try
            {

                // Insert into AuditEntry


                cmd.CommandText = @"INSERT INTO [AuditEntry] (EntitySetName, EntityTypeName, State, StateName, CreatedBy, CreatedDate, Discriminator, AppplicationName) 
                                    OUTPUT inserted.AuditEntryID
                                    VALUES (@EntitySetName, @EntityTypeName, @State,@StateName, @CreatedBy, @CreatedDate, @Discriminator, @AppplicationName);
                                    SELECT SCOPE_IDENTITY();";

                cmd.Parameters.AddWithValue("@EntitySetName", type.Name);
                cmd.Parameters.AddWithValue("@EntityTypeName", type.Name);
                cmd.Parameters.AddWithValue("@State", StateName.EntityDeleted); // 2 for delete
                cmd.Parameters.AddWithValue("@StateName", StateName.EntityDeleted.ToString()); // EntityAdded for insert
                cmd.Parameters.AddWithValue("@CreatedBy", "mahedee"); // It will come from session
                cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
                cmd.Parameters.AddWithValue("@Discriminator", "CustomAuditEntry"); // CustomAuditEntry Model
                cmd.Parameters.AddWithValue("@AppplicationName", applicationName); // Name of the application

                int auditEntryId = Convert.ToInt32(cmd.ExecuteScalar());

                cmd.Parameters.Clear();


                // Insert into AuditEntryProperty
                // Values of each property

                cmd.CommandText = @"INSERT INTO [AuditEntryProperty] (AuditEntryID, RelationName, PropertyName, OldValue, NewValue, Discriminator, AppplicationName) 
                                    VALUES (@AuditEntryID, @RelationName, @PropertyName, @OldValue, @NewValue, @DiscriminatorProperty, @AppplicationNameProperty);
                                    SELECT SCOPE_IDENTITY();";

                cmd.Parameters.Add(new SqlParameter("@AuditEntryID", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@RelationName", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@PropertyName", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@OldValue", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@NewValue", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@DiscriminatorProperty", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlParameter("@AppplicationNameProperty", SqlDbType.NVarChar));


                foreach (PropertyInfo propertyInfo in propertyInfos)
                {

                    cmd.Parameters["@AuditEntryID"].Value = auditEntryId;
                    cmd.Parameters["@RelationName"].Value = DBNull.Value;
                    cmd.Parameters["@PropertyName"].Value = propertyInfo.Name; // property name
                    cmd.Parameters["@OldValue"].Value = propertyInfo.GetValue(oldEntity); // property value
                    cmd.Parameters["@NewValue"].Value = DBNull.Value; // Null for delete
                    cmd.Parameters["@DiscriminatorProperty"].Value = "CustomAuditEntryProperty";
                    cmd.Parameters["@AppplicationNameProperty"].Value = applicationName;
                    cmd.ExecuteScalar();
                }

                // Commit transaction
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
        }
    }
}

Step 8: Create two Interface IProductRepository and IReportingRepository in Repositories/Interfaces folder

IProductRepository.cs

using AuditLog.API.Models;

namespace AuditLog.API.Repositories.Interfaces
{
    public interface IProductRepository
    {
        Task<IEnumerable<Product>> GetProducts();
        Task<Product> GetProduct(int id);
        Task<Product> AddProduct(Product product);
        Task<Product> UpdateProduct(Product product);
        Task<Product> DeleteProduct(int id);
    }
}

IReportingRepository.cs

namespace AuditLog.API.Repositories.Interfaces
{
    public interface IReportingRepository
    {
        // Get all the changes for a particular entity response using Dynamic type
        Task<IEnumerable<dynamic>> GetChangeLogDynamic(string EntityName);
    }
}

Step 9: Create two Repository class name ProductRepository and ReportingRepository in Repositories/Implementations folder

ProductRepository.cs

using AuditLog.API.AuditTrail.Interfaces;
using AuditLog.API.Models;
using AuditLog.API.Persistence;
using AuditLog.API.Repositories.Interfaces;
using System.Data;
using System.Data.SqlClient;

namespace AuditLog.API.Repositories.Implementations
{
    public class ProductRepository : IProductRepository
    {
        private DBConnector _connector;
        private IAuditTrail<Product> _auditTrail;
        public ProductRepository(DBConnector dBConnector, IAuditTrail<Product> auditTrail)
        {
            _connector = dBConnector;
            _auditTrail = auditTrail;
        }
        public Task<Product> AddProduct(Product model)
        {
            try
            {   
                if(this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                SqlTransaction transaction = this._connector.connection.BeginTransaction("");
                cmd.Transaction = transaction;
                cmd.CommandText = @"INSERT INTO Products (Name, Price, Quantity) 
                OUTPUT inserted.Id                
                VALUES (@Name, @Price, @Quantity); SELECT SCOPE_IDENTITY();";
                cmd.Parameters.AddWithValue("@Name", model.Name);
                cmd.Parameters.AddWithValue("@Price", model.Price);
                cmd.Parameters.AddWithValue("@Quantity", model.Quantity);

                // cmd.ExecuteScalar() returns the first column of the first row in the result
                // set returned by the query. Additional columns or rows are ignored.
                int insertedId = Convert.ToInt32(cmd.ExecuteScalar());
                if (insertedId > 0)
                {
                    transaction.Commit();
                }

                model.Id = insertedId;

                // Add audit trail
                _auditTrail.Insert(model);
                return Task.FromResult(model);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
            return null;
        }

        public Task<Product> GetProduct(int id)
        {
            var product = new Product();
            try
            {
                if (this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                cmd.CommandText = @"SELECT Id, Name, Price, Quantity FROM Products WHERE Id = @Id";
                cmd.Parameters.AddWithValue("@Id", id);
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    product.Id = Convert.ToInt32(reader["Id"]);
                    product.Name = reader["Name"] != DBNull.Value ? reader["Name"].ToString() : null;
                    product.Price = Convert.ToDecimal(reader["Price"]);
                    product.Quantity = Convert.ToInt32(reader["Quantity"]);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }

            // Task.FromResult is a helper method that creates a Task that's completed successfully
            // with the specified result.
            return Task.FromResult(product);
        }

        public async Task<IEnumerable<Product>> GetProducts()
        {
            var products = new List<Product>();

            try
            {
                if (this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                cmd.CommandText = @"SELECT Id, Name, Price, Quantity FROM Products";
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    var product = new Product()
                    {
                        Id = Convert.ToInt32(reader["Id"]),
                        Name = reader["Name"] != DBNull.Value ? reader["Name"].ToString() : null,
                        Price = Convert.ToDecimal(reader["Price"]),
                        Quantity = Convert.ToInt32(reader["Quantity"])
                    };
                    products.Add(product);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
            return products;
        }

        public Task<Product> UpdateProduct(Product product)
        {
            Product oldEntity = GetProduct(product.Id).Result;

            try
            {
                if(this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                SqlTransaction transaction = this._connector.connection.BeginTransaction("");
                cmd.Transaction = transaction;
                cmd.CommandText = @"UPDATE Products SET Name = @Name, Price = @Price, Quantity = @Quantity WHERE Id = @Id";
                cmd.Parameters.AddWithValue("@Id", product.Id);
                cmd.Parameters.AddWithValue("@Name", product.Name);
                cmd.Parameters.AddWithValue("@Price", product.Price);
                cmd.Parameters.AddWithValue("@Quantity", product.Quantity);

                int effectedRow = cmd.ExecuteNonQuery();
                if (effectedRow > 0)
                {
                    transaction.Commit();
                }

                // Update audit trail
                _auditTrail.Update(oldEntity, product);
                return Task.FromResult(product);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
        }

        public Task<Product> DeleteProduct(int id)
        {
            Product oldEntity = GetProduct(id).Result;

            try
            {
                if (this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                SqlTransaction transaction = this._connector.connection.BeginTransaction("");
                cmd.Transaction = transaction;
                cmd.CommandText = @"DELETE FROM Products WHERE Id = @Id";
                cmd.Parameters.AddWithValue("@Id", id);

                int effectedRow = cmd.ExecuteNonQuery();
                if (effectedRow > 0)
                {
                    transaction.Commit();
                }

                // Add audit trail
                _auditTrail.Delete(oldEntity);
                return Task.FromResult(oldEntity);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
        }
    }
}

ReportingRepository.cs

using AuditLog.API.AuditTrail.Interfaces;
using AuditLog.API.Models;
using AuditLog.API.Persistence;
using AuditLog.API.Repositories.Interfaces;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;

namespace AuditLog.API.Repositories.Implementations
{
    public class ReportingRepository : IReportingRepository
    {
        private DBConnector _connector;
        public ReportingRepository(DBConnector dBConnector) 
        {
            _connector = dBConnector;
        }
        public Task<IEnumerable<dynamic>> GetChangeLogDynamic(string EntityName)
        {
            try
            {
                if (this._connector.connection.State == ConnectionState.Closed)
                    this._connector.connection.Open();
                var cmd = this._connector.connection.CreateCommand() as SqlCommand;
                cmd.CommandText = @"  SELECT ae.AuditEntryID, ae.EntityTypeName, ae.StateName, ae.CreatedDate, aep.AuditEntryPropertyID, aep.PropertyName, aep.OldValue, aep.NewValue, aep.AppplicationName
                                  FROM AuditEntry ae INNER JOIN AuditEntryProperty aep
                                  ON ae.AuditEntryID = aep.AuditEntryID
                                  WHERE ae.EntityTypeName = @EntityName
                                  ORDER by ae.CreatedDate";
                cmd.Parameters.AddWithValue("@EntityName", EntityName);
                var reader = cmd.ExecuteReader();
                var dt = new DataTable();
                dt.Load(reader);
                var list = new List<dynamic>();
                foreach (DataRow row in dt.Rows)
                {
                    var expandoObject = new ExpandoObject() as IDictionary<string, object>;
                    foreach (DataColumn col in dt.Columns)
                    {
                        expandoObject.Add(col.ColumnName, row[col]);
                    }
                    list.Add(expandoObject);
                }
                return Task.FromResult(list.AsEnumerable());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // close connection
                this._connector.Dispose();
            }
            return null;
        }
    }
}

Step 10: Create two interfaces name IProductService and IReportingService in Services/Interfaces folder

IProductService.cs

using AuditLog.API.Models;

namespace AuditLog.API.Services.Interfaces
{
    public interface IProductService
    {
        Task<IEnumerable<Product>> GetProducts();
        Task<Product> GetProduct(int id);
        Task<Product> AddProduct(Product product);
        Task<Product> UpdateProduct(Product product);
        Task<Product> DeleteProduct(int id);
    }
}

IReportingService.cs

namespace AuditLog.API.Services.Interfaces
{
    public interface IReportingService
    {
        // Get all the changes for a particular entity response using Dynamic type
        Task<IEnumerable<dynamic>> GetChangeLogDynamic(string EntityName);
    }
}

Step 11: Create two service classes name ProductService and ReportingService in Services/Implementation folder

ProductService.cs

using AuditLog.API.Models;
using AuditLog.API.Repositories.Interfaces;
using AuditLog.API.Services.Interfaces;

namespace AuditLog.API.Services.Implementations
{
    public class ProductService : IProductService
    {
        IProductRepository _productRepository;
        public ProductService(IProductRepository productRepository)
        {
            _productRepository = productRepository;
        }
        public Task<Product> AddProduct(Product product)
        {
            return _productRepository.AddProduct(product);
        }

        public Task<Product> GetProduct(int id)
        {
            return _productRepository.GetProduct(id);
        }

        public Task<IEnumerable<Product>> GetProducts()
        {
            return _productRepository.GetProducts();
        }

        public Task<Product> UpdateProduct(Product product)
        {
            return _productRepository.UpdateProduct(product);
        }

        public Task<Product> DeleteProduct(int id)
        {
            return _productRepository.DeleteProduct(id);
        }
    }
}

ReportingService.cs

using AuditLog.API.Repositories.Interfaces;
using AuditLog.API.Services.Interfaces;

namespace AuditLog.API.Services.Implementations
{
    public class ReportingService : IReportingService
    {
        private IReportingRepository _reportingRepository;
        public ReportingService(IReportingRepository reportingRepository)
        {
            _reportingRepository = reportingRepository;
        }
        public Task<IEnumerable<dynamic>> GetChangeLogDynamic(string EntityName)
        {
            return _reportingRepository.GetChangeLogDynamic(EntityName);
        }
    }
}

Step 12: Configure Program class as follows.

Program.cs

using AuditLog.API.AuditTrail.Implementations;
using AuditLog.API.AuditTrail.Interfaces;
using AuditLog.API.Persistence;
using AuditLog.API.Repositories.Implementations;
using AuditLog.API.Repositories.Interfaces;
using AuditLog.API.Services.Implementations;
using AuditLog.API.Services.Interfaces;


var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();


string connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddTransient(_ => new DBConnector(connectionString));

builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped<IProductService, ProductService>();
builder.Services.AddScoped<IReportingRepository, ReportingRepository>();
builder.Services.AddScoped<IReportingService, ReportingService>();
builder.Services.AddScoped(typeof(IAuditTrail<>), typeof(AuditTrail<>));

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Step 13: Create two Controller class ProductsController and ReportingController in Controllers folder.

ProductsController.cs

using AuditLog.API.Models;
using AuditLog.API.Persistence;
using AuditLog.API.Services.Interfaces;
using Microsoft.AspNetCore.Mvc;

namespace AuditLog.API.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductsController : ControllerBase
    {
        private DBConnector _connector;
        private readonly IProductService _productService;

        public ProductsController( DBConnector connector, IProductService productService)
        {
            _connector = connector;
            _productService = productService;
        }

        // GET: api/Products
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Product>>> GetProducts()
        {
            var products = await _productService.GetProducts();

            if (products == null)
            {
                return NotFound();
            }
            return Ok(products);
        }

        // GET: api/Products/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Product>> GetProduct(int id)
        {
            var product = await _productService.GetProduct(id);
            if (product == null)
            {
                return NotFound();
            }
            return Ok(product);
        }

        // PUT: api/Products/5
        [HttpPut("{id}")]
        public async Task<IActionResult> PutProduct(int id, Product product)
        {
            if (id != product.Id)
            {
                return BadRequest();
            }
            var result = await _productService.UpdateProduct(product);
            if (result == null)
            {
                return NotFound();
            }
            return Ok(result);
        }

        // POST: api/Products
        [HttpPost]
        public async Task<ActionResult<Product>> PostProduct(Product product)
        {
            var result = await _productService.AddProduct(product);
            if (result == null)
            {
                return NotFound();
            }
            return Ok(result);
        }

        // DELETE: api/Products/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteProduct(int id)
        {
            var result = await _productService.DeleteProduct(id);

            if (result == null)
            {
                return NotFound();
            }
            return Ok(result);
        }
    }
}

ReportingController.cs

using AuditLog.API.Services.Interfaces;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace AuditLog.API.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ReportingController : ControllerBase
    {
        private IReportingService _reportingService;
        public ReportingController(IReportingService reportingService)
        {
            _reportingService = reportingService;
        }
        [HttpGet("GetChangeLogDynamic/{EntityName}")]
        public async Task<IActionResult> GetChangeLogDynamic(string EntityName)
        {
            var result = await _reportingService.GetChangeLogDynamic(EntityName);
            return Ok(result);
        }
    }
}

Step 14: Run the application and check the swagger.

Now run application create, update and delete Customer entity using swagger
You will see audit trail logs in AuditEntry and AuditEntryProperty table.
You can also view a sample report using Reporting controller. In GetChangeLog end point use “Product” as entity name.

Source code

Twitter Facebook LinkedIn

Mahedee Hasan

Step-by-Step Guide to Integrating Audit Trail in ASP.NET Core Using ADO.NET and Raw SQL

You May Also Enjoy

Boost Your Productivity - Setting Up and Customizing Windows Terminal with Git Bash and Oh My Posh

How to Configure SSL and HTTPS for RabbitMQ in Docker

Step-by-Step Guide: Deploying an ASP.NET Core Application with Azure Kubernetes Services, Azure SQL Server, and Azure Container Registry

Understanding Different Kinds of Kubernetes Resources