ASP.NET MVC - Audit Database Changes

You may wish to create an audit trail of database changes, i.e. creates, updates or deletes of records. This might be for internal corporate reasons (the 'blame game'), or for external regulatory reasons, e.g. FDA Part 11 compliance.

Using SQL Server or similar, you can setup a combination of triggers and stored procedures to automatically audit each change, but the simple approach presented here is to maintain the audit trail in application server code using a modified DbRepository pattern (as per NerdDinner). Once setup as described below, no other code changes or database changes are required (other than provision of appropriate views / reports etc. of the audit data).

Create an Audits Table

For example, using SQL Server:

Column Name	Data Type	Allow Nulls	Comments
AuditID	int	no	Primary key. Auto-incrementing.
AuditNewState	nvarchar(MAX)	no
AuditOperation	nvarchar(10)	no
AuditPrimaryKey	int	no	This is a foreign key for one of your business tables.
AuditTable	nvarchar(30)	no	The name of the business table for this item.
AuditTime	datetime	no	The time of the change.
AuditUser	int	no	The user instigating this change.

Intercept Each Database Save

If you are using the DbRepository pattern described for NerdDinner, you can simply create a base Repository class for your app, and add a Save method to the base class:

    public virtual void Save(int user)
    {
        try
        {
            if (user == 0)
            {
                // Don't audit these changes.
                DataContext.SubmitChanges();
            }
            else
            {
                // Audit these changes.
                var deletes = DataContext.GetChangeSet().Deletes;
                var inserts = DataContext.GetChangeSet().Inserts;
                var updates = DataContext.GetChangeSet().Updates;

                DataContext.SubmitChanges();

                if (deletes.Count() + inserts.Count() + updates.Count() > 0)
                    AuditChanges(user, deletes, inserts, updates);
            }
        }
        catch (Exception e)
        {
            throw e;
        }
    }

The Save method is passed some User ID which is saved with the audit item. Passing a user ID of zero will suppress the auditing, for example if you don't want to audit when importing another database.

The AuditChanges method can be something like:

        protected virtual bool AuditChanges(int user, IList<object> deletes, IList<object> inserts, IList<object> updates)
        {
            // Audit multiple change.
            var auditRepos = new AuditDbRepository(this);

            // Audit the deletes.
            foreach (var item in deletes)
                AuditChange(item, user, "delete", auditRepos);

            // Audit the inserts.
            foreach (var item in inserts)
                AuditChange(item, user, "insert", auditRepos);

            // Audit the updates.
            foreach (var item in updates)
                AuditChange(item, user, "update", auditRepos);

            auditRepos.Save(user);

            // OK.
            return true;
        }

and the AuditChange method could be:

        protected virtual bool AuditChange(object item, int user, string operation, IAuditRepository auditRepos)
        {
            // Audit a single change.
            string typeName = item.GetType().Name.ToUpper();

            string newState = "";
            int primaryKey = 0;
            string table = "";
            DateTime time = DateTime.Now;

            switch (typeName)
            {
                // Add all business classes to be audited.
                case "CUSTOMERS":
                    table = "Customers";
                    primaryKey = ((Customer)item).CustomerID;
                    newState = ((Customer)item).AuditState();
                    break;

                case "PRODUCTS":
                    table = "Products";
                    primaryKey = ((Product)item).ProductID;
                    newState = ((Product)item).AuditState();
                    break;
            }

            if (primaryKey == 0)
            {
                // Ignore this change.
                return true;
            }
            
            return AuditHelpers.AddItem(time, table, primaryKey, user, operation, newState, auditRepos);
        }

Add an Audit Item

Each audited database change causes one new item to be added to the Audits table. A new Audit object is created using the helper method below and added to the Audits repository.

namespace AuditHelpers
{
    public static class AuditHelpers
    {
        public static bool AddItem(DateTime time, string table, int primaryKey, int user, string operation,
            string newState, IAuditRepository repository)
        {
            var audit = new Audit();

            audit.AuditNewState = newState;
            audit.AuditOperation = operation;
            audit.AuditPrimaryKey = primaryKey;
            audit.AuditTable = table;
            audit.AuditTime = time;
            audit.AuditUser = user;

            repository.Add(audit);

            // OK.
            return true;
        }
    }
}

Serialize a Record

Each business class to be audited should have an AuditState extension method, probably implemented in a helper class, e.g.

        public static string AuditState(this Customer cust)
        {
            // Serialize the current field values of 'cust' for storage in an audit item.
            string result = "Name: " + cust.CustomerName + "\r\n";

            if (!String.IsNullOrWhiteSpace(cust.CustomerAddress))
                result += "Address: " + cust.CustomerAddress + "\r\n";

            result += "Tel: " + cust.CustomerTel + "\r\n";
            result += "Fax: " + cust.CustomerFax + "\r\n";

            return result;
        }