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

Mina Talaat Ghaly

Rate me:

4.68/5 (11 votes)

29 Jan 2015CPOL

27.3K

Add the auditing properties with one SQL query and fill them from one place.

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

SQL

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Mina Talaat Ghaly

Software Developer LinkDev

United States

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

if (this.GetValidationErrors().Count() == 0) { ObjectContext ctx = ((IObjectContextAdapter)this).ObjectContext; //get the current user name... string userID = System.Security.Principal.WindowsIdentity.GetCurrent().Name; userID = userID.Substring(userID.IndexOf('\\') + 1); //remove domain //loop over the modified and added objects foreach (ObjectStateEntry ose in ctx.ObjectStateManager .GetObjectStateEntries(EntityState.Added | EntityState.Modified) .Where(e => e.Entity is IAuditInfo)) { switch (ose.State) { case EntityState.Added: ((IAuditInfo)ose.Entity).CreatedOn = DateTime.Now; ((IAuditInfo)ose.Entity).CreatedBy = userID; break; case EntityState.Modified: ((IAuditInfo)ose.Entity).LastUpdatedOn = DateTime.Now; ((IAuditInfo)ose.Entity).LastUpdatedBy = userID; break; case EntityState.Deleted: case EntityState.Detached: case EntityState.Unchanged: default: break; } } //for some reason the EntityState is not modified if the it was a relationship that was altered // i.e. changing the GradeScale of a ScoreType //so loop over all the relationship changes as well foreach (IAuditInfo iai in this.GetRelationships() .Select(f => f.Item1) //select the first item of the tuple .Where(f => f is IAuditInfo) //we only care about the relationship changes where ) //the item implements our AuditInfo interface. { //added items will already be handled by the ObjectStateEntry loop above //so all these are modified iai.LastUpdatedBy = userID; iai.LastUpdatedOn = DateTime.Now; } }

public static class DbContextExtensions { /// <summary> /// Get all the relationship changes for this context. /// </summary> /// <param name="context"></param> /// <returns></returns> public static IEnumerable<Tuple<object, object>> GetRelationships(this DbContext context) { return GetAddedRelationships(context) .Union(GetDeletedRelationships(context)); } /// <summary> /// Get all the added relationships for this context. /// </summary> /// <param name="context"></param> /// <returns></returns> public static IEnumerable<Tuple<object, object>> GetAddedRelationships(this DbContext context) { return GetRelationships(context, EntityState.Added, (e, i) => e.CurrentValues[i]); } /// <summary> /// Get all the deleted relationships for this context. /// </summary> /// <param name="context"></param> /// <returns></returns> public static IEnumerable<Tuple<object, object>> GetDeletedRelationships(this DbContext context) { return GetRelationships(context, EntityState.Deleted, (e, i) => e.OriginalValues[i]); } /// <summary> /// Get all relationships of the given state. /// </summary> /// <param name="context"></param> /// <param name="relationshipState">The EntityState of the relationship.</param> /// <param name="getValue">A function that returns the values of a relationship.</param> /// <returns></returns> private static IEnumerable<Tuple<object, object>> GetRelationships( this DbContext context, EntityState relationshipState, Func<ObjectStateEntry, int, object> getValue) { context.ChangeTracker.DetectChanges(); var objectContext = ((IObjectContextAdapter)context).ObjectContext; return objectContext.ObjectStateManager .GetObjectStateEntries(relationshipState) .Where(e => e.IsRelationship) .Select( e => Tuple.Create( objectContext.GetObjectByKey((EntityKey)getValue(e, 0)), objectContext.GetObjectByKey((EntityKey)getValue(e, 1)))); } public static void Delete<T>(this EntityCollection<T> collection, T entityToDelete) where T : EntityObject, IEntityWithRelationships { RelationshipManager relationshipManager = entityToDelete.RelationshipManager; IRelatedEnd relatedEnd = relationshipManager.GetAllRelatedEnds().FirstOrDefault(); if (relatedEnd == null) { throw new Exception("No relationships found for the entity to delete. Entity must have at least one relationship."); } var query = relatedEnd.CreateSourceQuery() as ObjectQuery; if (query == null) { throw new Exception("The entity to delete is detached. Entity must be attached to an ObjectContext."); } query.Context.DeleteObject(entityToDelete); collection.Remove(entityToDelete); } }

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

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

Introduction

Using the Code

License

Comments and Discussions