Simple Auditing with LINQ to SQL - Date, Time and User Stamps

My previous post was on automatically generating basic auditing fields on SQL Server tables via SQL scripts. Today, I examine the other side of the coin - using the LINQ data context to stamp records with audit fields such as "Modified","ModifiedBy", "Created" and "CreatedBy". These stamps are similar to the functionality provided by the windows file system.

There are many ways you can populate application audit tables or audit fields. These include:

1. With Triggers (but this relies on the SQL authenication mechanism for accurate user data);
2. With stored procedures (this also relies on the accuracy of SQL authentication information or you need to append the user name to the parameters of the procedure.)
3. On the Application Side - Manually coding all service/database calls to append this information.
4. Use a SQL Server log auditing tool like LogExplorer that tracks the SQL Server transaction log.
5. Leveraging your data access layer's update point to append this information before the update takes place.

Below you can find my code which takes the last approach - and uses reflection to stamp records with when a record was created/updated and who did the insert/update. This is a simplified alternative to an audit solution (such as http://blog.matthidinger.com/2008/05/09/LINQToSQLAuditTrail.aspx) which has a full audit table and where requirements are just to display who last modified or created a particular record in your application:

        /// <summary>
        /// Basic Audit User and Date Stamp Functionality
        /// </summary>
        /// <param name="failureMode"></param>
        public override void SubmitChanges(ConflictMode failureMode)
        {

            //Updates
            for (int changeCounter = 0; changeCounter < this.GetChangeSet().Updates.Count; changeCounter++)
            {
                object modifiedEntity = this.GetChangeSet().Updates[changeCounter];
                SetAuditStamp(this, modifiedEntity, ChangeType.Update);
            }

            //Inserts
            for (int changeCounter = 0; changeCounter < this.GetChangeSet().Inserts.Count; changeCounter++)
            {
                object modifiedEntity = this.GetChangeSet().Inserts[changeCounter];
                SetAuditStamp(this, modifiedEntity, ChangeType.Insert);
            }

            base.SubmitChanges(failureMode);
        }

        /// <summary>
        /// For Inserts or Updates - set the user and date stamps
        /// </summary>
        /// <param name="context"></param>
        /// <param name="modifiedEntity"></param>
        private void SetAuditStamp(DataContext context, object modifiedEntity, ChangeType changeType)
        {
            string userName = System.Threading.Thread.CurrentPrincipal.Identity.Name;
            const string Created = "Created", CreatedBy = "CreatedBy", 
                         Modified = "Modified", ModifiedBy = "ModifiedBy";

            if (changeType == ChangeType.Insert)
            {
                SetAuditValue(modifiedEntity, Created, System.DateTime.Now);
                SetAuditValue(modifiedEntity, CreatedBy, userName);
            }
            else if (changeType == ChangeType.Update) 
            {
                SetAuditValue(modifiedEntity, Modified, System.DateTime.Now);
                SetAuditValue(modifiedEntity, ModifiedBy, userName);
            }
        }

        /// <summary>
        /// The type of modifications
        /// </summary>
        private enum ChangeType
        {
            Update,
            Insert
        }

        /// <summary>
        /// Set target value if it exists on the object
        /// </summary>
        /// <param name="modifiedEntity"></param>
        /// <param name="fieldName"></param>
        /// <param name="propertyValue"></param>
        private void SetAuditValue(object modifiedEntity, string fieldName, object propertyValue)
        {
            if (modifiedEntity.GetType().GetProperty(fieldName) != null) //Set current user and time stamp
                {
                    modifiedEntity.GetType().GetProperty(fieldName).SetValue(modifiedEntity, propertyValue, null); 
                }
        }