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

ASP.NET MVC - Report Generation from .ASPX Files

You can easily use the ASP.NET MVC view engines to create static HTML files or custom HTML reports from, for example, .ASPX and .ASCX files - no need to use some other template system!

Here's a simple example:

    public string Generate(ViewDataDictionary viewData, string templateFile, string masterFile,
            ControllerContext controllerContext)
    {
        var writer = new StringWriter();
        var tempData = new TempDataDictionary();

        var viewResult = ViewEngines.Engines.FindView(controllerContext, templateFile, masterFile);

        if ((viewResult == null) || (viewResult.View == null))
        {
            Log.WriteLine("Generate: ERROR - View not found, template file: '{0}', master file: '{1}",
                templateFile, masterFile);
            return "View not found";
        }

        var viewContext = new ViewContext(controllerContext, viewResult.View, viewData, tempData, writer);
        viewResult.View.Render(viewContext, writer);

        return writer.ToString();
    }

The viewData argument is used to pass in your model, e.g.

    var model = new MyReportViewModel(xxx, yyy);
    var viewData = new ViewDataDictionary(model);

I usually keep the .ASPX and .ASCX fies in a separate subtree of the Views folder.

ASP.NET MVC - Admin Helpers

A small helper to add or remove a role from an ASP.NET user.

using System.Web.Security;

namespace My.Helpers
{
    public class AdminHelpers
    {
        public static bool SetRole(string userName, string roleName, bool newState)
        {
            bool currState = Roles.IsUserInRole(userName, roleName);

            if (newState != currState)
            {
                if (newState)
                {
                    // Add the 'roleName' role to this user.
                    Roles.AddUserToRole(userName, roleName);
                }
                else
                {
                    // Remove the 'roleName' role from this user.
                    Roles.RemoveUserFromRole(userName, roleName);
                }
            }

            return true;
        }
    }
}

ASP.NET MVC Mini-Profiler - Linq to SQL

This is a useful free profiler for ASP.NET MVC from the people at StackOverflow.com.

Some advice re. usage is in my previous post.

This post shows one way to use it in ASP.NET MVC with Linq to SQL.

In A DbRepository

I'm assuming a repository-based approach similar to NerdDinner.

I usually let my repository classes share a 'DataContext' - each controller typically has a main repository which creates the data context as usual, but other repositories needed in the controller actions are created using the second constructor below, and therefore use the first repository's data context.

    public class MyDbRepository : IMyRepository
    {
        public MyDataContext DataContext { get; protected set; }

        // Constructors.

        public MyDbRepository()
        {
            MiniProfiler profiler = MiniProfiler.Current;

            // Grab the SQL connection string (or could get it from web.config, for example).
            DataContext = new MyDataContext();
            string connString = DataContext.Connection.ConnectionString;

            // Setup a profiled connection.
            var conn = new SqlConnection(connString);
            var profiledConn = MvcMiniProfiler.Data.ProfiledDbConnection.Get(conn, profiler);

            // Create another data context using the profiled connection.
            DataContext = new MyDataContext(profiledConn);
        }

        public MyDbRepository(IMyRepository repos)
        {
            // Repositories can share a data context object.
            DataContext = repos.DataContext;
        }

        // Public methods.

        // +++

    }

ASP.NET MVC Mini-Profiler - First Impressions

This is a useful free profiler for ASP.NET MVC from the people at StackOverflow.com (a wonderful source of answers, and built using ASP.NET MVC).

The easiest way to install it in VS2010 is using NuGet

These snippets show one way to use it.

Site.Master (.ASPX view engine)

<script src="<%: Url.Content("https://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.min.js") %>"  type="text/javascript"></script>
<%= MvcMiniProfiler.MiniProfiler.RenderIncludes() %> 

Global.asax.cs

A simple approach is to start and stop the profiler for each separate web request, for example:

using MvcMiniProfiler; 

    public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_BeginRequest()
        {
            if (Request.IsLocal)
                MiniProfiler.Start();
        }
        
        protected void Application_EndRequest()
        {
            if (Request.IsLocal)
                MiniProfiler.Stop();
        }
    }

In A Controller, for example

Create a separate MiniProfiler 'step' for any long processing steps that you want to profile:

var profiler = MiniProfiler.Current;
 
    // No need to check 'profiler' for null here, it's handled in profiler.Step()

    using (profiler.Step("Search"))
    {
        // Time-consuming stuff...
        DoLongSearch(xxx);
    }

Steps can be nested, and the profile output will show the step structure:

    using (profiler.Step("SearchAndUpdate"))
    {
        using (profiler.Step("Search"))
        {
            // Time-consuming stuff...
            DoSearch(xxx);
        }

        using (profiler.Step("Update"))
        {
            // Time-consuming stuff...
            DoUpdate(xxx);
        }
    }

Viewing the Output

When you navigate to a URL that causes the profiler to be called, the resulting view will contain a small MiniProfiler gadget at the top left. Click on it to get the timing detail.

Linq to SQL

You can use the MiniProfiler to profile database calls, including Linq to SQL - see this post. The profile information includes the elapsed time of each query and also warns of 'duplicate' SQL queries, both of which can help in optimizing application design.