Friday, 1 July 2011

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;
        }