Canarys | IT Services

Blogs

ASP.NET: Audit Trail Implementation using Entity Framework

Canarys

,
, , ,
Share

When you are working with certain projects which involves the Customer records, you might need to track of each and every addition/change made to system along with who create, modify and delete the records. 

Here I am going to show you how to implement the Audit Trail using Entity Framework in ASP.NET. Entity Framework keeps track of entity objects when we have modified them. Here I am going to use the ‘ObjectStateManager’ which is EF’s caching management object to access the entity states and their changes.

Firstly create the AuditLog table using below script, which stores the audit type (Insert/Update/Delete), DB table name, primary key, column name, old value, new value, action date and the UserId who make the change.

SQL Table

Create TABLE [dbo].[AuditLog] (

       [Id] [uniqueidentifier] NOT NULL Primary Key,

       [AuditType] [char](1) NOT NULL,

       [TableName] [nvarchar](150) NOT NULL,

       [PK] [nvarchar](50) NOT NULL,

       [ColumnName] [nvarchar](100) NULL,

       [OldValue] [nvarchar](max) NULL,

       [NewValue] [nvarchar](max) NULL,

       [Date] [datetime] NOT NULL,

       [UserId] [uniqueidentifier] NOT NULL

)

As EF creates the Entity Data Model in the form of partial class for your DB entities, follow the same approach by created the DB entities partial class by extending ObjectContext class.

In the next step, override the SaveChanges() method of the EF as shown below:

Partial Class

    public partial class CompanyEntities : ObjectContext

    {    

        public override int SaveChanges(SaveOptions options)

        {

            return base.SaveChanges(options);

        }       

    }

We will have one custom class to capture the changed entities from the EF as shown below:

CustomLog Class

    public class CustomLog

    {

        public Guid Id { get; set; }

        public string Action { get; set; }

        public string TableName { get; set; }

        public string PrimaryKey { get; set; }

        public string ColumnName { get; set; }

        public string OldValue { get; set; }

        public string NewValue { get; set; }

        public DateTime Date { get; set; }

        public Guid UserId { get; set; }

    }

Now we will write a method to access the entries which are eligible to log in the database table. Inside the method we will get the Added, Modified and Deleted entities from the ObjectStateManager. From each entity we will read the Original Values and Current Values, and store them to CustomLog class.

Here is the full length code of getting the list of changed entities in EF:

Audit Entities

        public static List<CustomLog> GetLogEntries(ObjectStateManager entities)

        {

            List<CustomLog> listLogs = new List<CustomLog>();

            var entries = entities.GetObjectStateEntries(EntityState.Added | EntityState.Modified | EntityState.Deleted);

            foreach (var entry in entries)

            {

                var tableName = entry.Entity.GetType().Name;

                var pk = GetPrimaryKeys(entry);

                if (entry.State == EntityState.Added)

                {

                    var currentEntry = entities.GetObjectStateEntry(entry.EntityKey);

                    var currentValues = currentEntry.CurrentValues;                  

                    for (var i = 0; i < currentValues.FieldCount; i++)

                    {

                        var propName = currentValues.DataRecordInfo.FieldMetadata[i].FieldType.Name;

                        var newValue = currentValues[propName].ToString();

                        var log = new CustomLog()

                        {

                            Id = Guid.NewGuid(),

                            Action = "I",

                            TableName = tableName,

                            PrimaryKey = pk,

                            ColumnName = propName,

                            OldValue = null,

                            NewValue = newValue,

                            Date = DateTime.Now.ToString(),

                            UserId = Session[UserId].ToString()

                        };

                        listLogs.Add(log);

                    }

                }

                else if (entry.State == EntityState.Modified)

                {

                    var currentEntry = entities.GetObjectStateEntry(entry.EntityKey);

                    var currentValues = currentEntry.CurrentValues;

                    var originalValues = currentEntry.OriginalValues;

                    var properties = currentEntry.GetModifiedProperties();

                    foreach (var propName in properties)

                    {

                        var oldValue = originalValues[propName].ToString();

                        var newValue = currentValues[propName].ToString();

                        if (oldValue == newValue) continue;

                        var log = new CustomLog()

                        {

                            Id = Guid.NewGuid(),

                            Action = "M",

                            TableName = tableName,

                            PrimaryKey = pk,

                            ColumnName = propName,

                            OldValue = oldValue,

                            NewValue = newValue,

                            Date = DateTime.Now.ToString(),

                            UserId = Session[UserId].ToString()

                        };

                        listLogs.Add(log);

                    }

                }

                else if (entry.State == EntityState.Deleted)

                {

                    var currentEntry = entities.GetObjectStateEntry(entry.EntityKey);

                    var originalValues = currentEntry.OriginalValues;

                    for (var i = 0; i < originalValues.FieldCount; i++)

                    {

                        var oldValue = originalValues[i].ToString();

                        var log = new CustomLog()

                        {

                            Id = Guid.NewGuid(),

                            Action = "D",

                            TableName = tableName,

                            PrimaryKey = pk,

                            ColumnName = null,

                            OldValue = oldValue,

                            NewValue = null,

                            Date = DateTime.Now.ToString(),

                            UserId = Session[UserId].ToString()

                        };

                        listLogs.Add(log);

                    }

                }

            }

            return listLogs;

        }

 

        private static string GetPrimaryKeys(ObjectStateEntry entry)

        {

            string pk = string.Empty;

            if (entry.EntityKey == null || entry.EntityKey.EntityKeyValues == null || entry.EntityKey.EntityKeyValues.Length == 0) return "N/A";

            foreach (var keyValue in entry.EntityKey.EntityKeyValues)

            {

                pk += string.Format("{0}={1};", keyValue.Key, keyValue.Value);

            }

            return pk;

        }

Finally make a call to the above method from the overloaded SaveChanges() method as shown below:

Save Audit Info

        public override int SaveChanges(SaveOptions options)

        {

            List<CustomLog> listLogs = AuditTrail.GetLogEntries(this.ObjectStateManager);

            foreach (var log in listLogs)

            {

                var auditLog = new AuditLog() //fill the AuditLog entity of EF

                {

                    Id = log.Id,

                    AuditType = log.Action,

                    TableName = log.TableName,

                    PK = log.PrimaryKey,

                    ColumnName = log.ColumnName,

                    OldValue = log.OldValue,

                    NewValue = log.NewValue,

                    Date = log.Date,

                    UserId = log.UserId

                };

                this.AuditLogs.AddObject(auditLog); //store audit details into DB table

            }

            return base.SaveChanges(options);

        }

That’s all for now…! I hope you got the idea of doing the Audit Trail using the ObjectStateManager’ of ObjectContext. In my next post I will try to explain doing the same using DbContext.

Thanks a lot for reaching here and reading my article…..!

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Reach Us

With Canarys,
Let’s Plan. Grow. Strive. Succeed.

Enquire Now

Copyright © 2024 Canarys Automations Limited. All Rights Reserved.