Table Auditing (Created By, Creation Date, Last Modified By, Last Modification Date)

Introduction

Here, I will explain how to make your database auditable by adding the four generic properties (CreatedBy, CreationDate, LastModifiedBy, LastModificationDate), and also I will explain how to fill those properties from one place in code instead of filling them everytime you insert / update your entity. Also, the following code can be written on running projects.

Using the Code

First of all, here's the SQL query you have to run on your database to add the four columns (CreatedBy, CreationDate, LastModifiedBy, LastModificationDate).

Declare @createdByColumnName VarChar(255);
Declare @lastModifiedByByColumnName VarChar(255);
Declare @creationDateColumnName VarChar(255);
Declare @lastModificationDateColumnName VarChar(255);
   
SET @createdByColumnName = N'CreatedBy';
SET @lastModifiedByByColumnName = N'LastModifiedBy';
SET @creationDateColumnName = N'CreationDate';
SET @lastModificationDateColumnName = N'LastModificationDate';
   
Declare @alterStatement VarChar(1000);
Declare @tableName nvarchar(1000);
DECLARE db_cursor CURSOR FOR
SELECT name from sys.all_objects -- where [ your tables conditions like "[type] = 'U' 
                                 -- and [name] like 'Table_%'" ];

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @tableName

WHILE @@FETCH_STATUS = 0   
BEGIN   
   
IF NOT EXISTS(SELECT * FROM sys.columns 
        WHERE [name] = @createdByColumnName AND [object_id] = OBJECT_ID(@tableName))
        BEGIN
            SELECT @alterStatement = 'ALTER TABLE ' +@tableName 
            +' ADD ' + @createdByColumnName + ' nvarchar(50) null'
            Exec (@alterStatement)
        END
 
 IF NOT EXISTS(SELECT * FROM sys.columns 
        WHERE [name] = @lastModifiedByByColumnName AND [object_id] = OBJECT_ID(@tableName))
        BEGIN
            SELECT @alterStatement = 'ALTER TABLE ' +@tableName 
            +' ADD ' + @lastModifiedByByColumnName + ' nvarchar(50) null'
            Exec (@alterStatement)
        END
   
IF NOT EXISTS(SELECT * FROM sys.columns 
        WHERE [name] = @creationDateColumnName AND [object_id] = OBJECT_ID(@tableName))
        BEGIN
            SELECT @alterStatement = 'ALTER TABLE ' +@tableName 
            +' ADD ' + @creationDateColumnName + ' datetime null'
            Exec (@alterStatement)
        END
   
IF NOT EXISTS(SELECT * FROM sys.columns 
        WHERE [name] = @lastModificationDateColumnName AND [object_id] = OBJECT_ID(@tableName))
        BEGIN
            SELECT @alterStatement = 'ALTER TABLE ' +@tableName 
            +' ADD ' + @lastModificationDateColumnName + ' datetime null'
            Exec (@alterStatement)
        END
    
    FETCH NEXT FROM db_cursor INTO @tableName
        
   END   
CLOSE db_cursor   
DEALLOCATE db_cursor

Then all your database mapping objects have to implement the following Interface:

public interface IAuditableObject
{
    string CreatedBy { get; set; }
    string LastModifiedBy { get; set; }
    DateTime? CreationDate { get; set; }
    DateTime? LastModificationDate { get; set; }
}

So your context entity has be like this:

public partial class Employee : IAuditableObject
    {
        public int Id {get; set; }

        public string Name {get; set; }

        public string CreatedBy { get; set; }
        public string LastModifiedBy { get; set; }
        public DateTime? CreationDate { get; set; }
        public DateTime? LastModificationDate { get; set; }
    }

Then put this class in your project or in any Utilities Project:

    public static class AuditingUtilities
    {
        public static void AddAuditingData(IEnumerable<DbEntityEntry> dbEntityEntries)
        {
            try
            {
                foreach (var entry in dbEntityEntries)
                {
                    if (entry.Entity as IAuditableObject != null)
                    {
                      
                        if (entry.State == EntityState.Added)
                        {
                            (entry.Entity as IAuditableObject).CreatedBy = 
                            (string.IsNullOrEmpty(HttpContext.Current.User.Identity.Name)
                                && !HttpContext.Current.User.Identity.IsAuthenticated) ? 
                                "Api" : HttpContext.Current.User.Identity.Name;
                            (entry.Entity as IAuditableObject).CreationDate = DateTime.Now;
                        }
                        else if (entry.State == EntityState.Modified)
                        {
                            (entry.Entity as IAuditableObject).LastModifiedBy = 
                            (string.IsNullOrEmpty(HttpContext.Current.User.Identity.Name)
                                && !HttpContext.Current.User.Identity.IsAuthenticated) ? 
                                "Api" : HttpContext.Current.User.Identity.Name;
                            (entry.Entity as IAuditableObject).LastModificationDate = DateTime.Now;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //Handle saving auditing data exception.
            }            
        }
    }
}

Till now, your properties are located in database and context, but nothing is going to be saved till you put the following line in your context class and Save Changes:

public  override int SaveChanges()
       {
           // this method handle any exception so no need to put it in try
           AuditingUtilities.AddAuditingData(this.ChangeTracker.Entries());

           return base.SaveChanges();
       }

Enjoy!