A practical approach for intra-table auditing.

Introduction

What the hell is intra-table auditing? Well, intra-table auditing is a term I invented some minutes ago.

It’s a minimalistic audit solution where some audit evidence is stored inside the record that is audited. As a result, each table that should be audited, needs to have a set of mandatory audit fields. (DateCreated, DateModified, CreatedBy and ModifiedBy). You probably noticed that,for obvious reasons, there is no DateDeleted field.

So, don’t confuse this approach with “full auditing”, where a separate audit table is used to store, in addition to the above 4 audit fields, also the full set of fields that are changed (full auditing allows also to audit deleted records). You can find a full audit solution here: http://blog.pragmaswitch.com/?p=291.

The solution I present here is nothing more than some slight improvement on a technique initially developed by Kostas Christodoulou: http://kostaschristodoulou.blogspot.be/2011/11/in-msdn-forums-i-came-across-post_20.html .

What can you expect?

This is the auditing schema we want to apply:

  public interface IAuditable
    {
        DateTime? DateCreated { get; set; }
        string CreatedBy { get; set; }
        DateTime? DateModified { get; set; }
        string ModifiedBy { get; set; }
    }

We want that applying the above audting schema is as simple as possible. So, the only effort we want to do when setting up a new table in our LightSwitch project, is to tell the LightSwitch engine that your new Type should implement the above interface. Make sure to add the above interface in the Common project of your LightSwitch solution.

Let’s say we introduced a new Customer type in our internal DataSource (via the designer). If we want now that the customer table participates in our intra-table auditing, we need to provide following code:

 public partial class Customer: IAuditable
    {
    }

Note that the usage of the the IAuditable interface serves two different purposes:

First of all, when an entity type implements the IAuditable interface, it will assure the the above mentioned audit fields are effectively part of the entity definition. The nice thing that this is checked during compile time !

Secondly, implementing the IAuditable interface is a “signal” to the server side auditing engine for including the entity type which implements the IAuditable interface, in the intra-table auditing system. In order words, entity definitions that don’t implement IAuditable are not audited.

For serving this last requirement, an alternative approach could have been used, namely decorating the entity type with an home-made attribute. You can find more info on attributes here: http://msdn.microsoft.com/en-us/library/z0w1kczw.

Although attribute decoration is a powerful technique, for our purpose here, it can’t compete with the IAuditable approach because without interface we would miss the compile time checking on the audit fields ! Nonetheless, note that for the “signaling” fuction, attribute decoration is much more powerfull than implementing an interface, because it can work much more “fine-grained” because an attribute can have parameters.

Server side processing

As mentioned above, once the server side code is hooked up, there is no additional work (apart from implementing IAuditable)  to be done when you extend your LightSwitch solution  with an additional entity type. That’s because we call the audit “engine in the SaveChanges_Executing method:

public partial class ApplicationDataService

    {
        partial void SaveChanges_Executing()
        {
            IntraTableAuditing.ApplyIntraTableAuditing(this.DataWorkspace.ApplicationData, this.Application.User);
        }
    }

Make sure to include the following class in the Server project of your LightSwitch solution:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.LightSwitch;
using System.Data.SqlTypes;
using Microsoft.LightSwitch.Security;

namespace LightSwitchApplication
{
    public static class IntraTableAuditing
    {
        public static void ApplyIntraTableAuditing(IDataService dataService, IUser user)
        {
            EntityChangeSet changes = dataService.Details.GetChanges();

            foreach (var modifiedEntity in changes.ModifiedEntities)
            {
                if (typeof(IAuditable).IsAssignableFrom(modifiedEntity.GetType()))
                {
                    modifiedEntity.Details.Properties["DateModified"].Value = CalculateTimeStamp();
                    modifiedEntity.Details.Properties["ModifiedBy"].Value = user.Name;
                }
            }

            foreach (var insertedEntity in changes.AddedEntities)
            {
                if (typeof(IAuditable).IsAssignableFrom(insertedEntity.GetType()))
                {
                    insertedEntity.Details.Properties["DateCreated"].Value = insertedEntity.Details.Properties["DateModified"].Value = CalculateTimeStamp();
                    insertedEntity.Details.Properties["CreatedBy"].Value = insertedEntity.Details.Properties["ModifiedBy"].Value = user.Name;
                }
            }
        }
        private static DateTime CalculateTimeStamp()
        {
            //We construct a DateTime without milliseconds for avoiding a concurrency exception
            //and return a sqlDateTime !
            return new SqlDateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second).Value;
        }
    }
}

The audit engine will make sure also that DateTime.Now values will not cause concurrency exceptions, which have been reported many times on the LightSwitch forum. Apparently, a DateTime.Now when the finest resolution is applied can give trouble, that’s why I cut the milliseconds . Problem solved, thanks to Yann Duran :)

The good thing here is that the auditing is done explicitely on the server side, and that ‘s the place where it belongs.