03Jul

ASP.NET: Audit Trail Implementation using Entity Framework

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 ObjectStateManagerof 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…..!

 

Related

What are Frames? How to handle frames in Selenium WebDriver with C#?

IFrame (FullForm: Inline Frame) is an HTML document that is included in another HTML document and is...

Read More >

What is Synchronization? Handling Synchronization in Selenium WebDriver using C#:

Synchronization meaning: when two or more components involved to perform any action, we expect these...

Read More >

Sending Test reports by Email using Office 365, Gmail

Wouldn’t it be great if Test Report are sent automatically across team as soon the Test Execut...

Read More >

Token Based Authentication for Web API's

Securing ASP.NET Web API using Custom Token Based AuthenticationProviding a security to the Web API&...

Read More >

Entity Framework CodeFirst Approach

Entity Framework Code First Approach Entity Framework is an enhancement to an existing ADO.Net ...

Read More >

Create Restful service using Web API

What is Asp.Net Web API?Asp.Net Web API is a framework for building HTTP services that can be consum...

Read More >

Asynchronous actions in MVC 5

What does it mean?Asynchronous actions allow developers to handle more concurrent requests and can b...

Read More >

ELMAH Integration in ASP.NET MVC Application

ELMAH(Error Logging Modules And Handlers)What is ELMAH?ELMAH (Error Logging Modules and Handlers) is...

Read More >

Dependency Injection on SignalR

                &nbs...

Read More >

Create custom project templates in Visual Studio

Visual studio installation comes with the various predefined project templates, and we can use one o...

Read More >

Share

Comments

This produces a concurrency error on the base.SaveChanges() call. It says it is affecting 5 rows instead of 1, then 10 instead of 1, and so on. Every time I try it, the number increases by 5 (even after restarting the program).
9/14/2019 1:51:56 AM | Reply

Post a Comment

  • Recent
  • Popular
  • Tag
Monthly Archive
Subscribe
Name

Contact Us
  • *
  • *