Automatic Event Logging in LINQ

Introduction

Enterprise applications often have tens or even hundreds of users capable of making destructive changes to database records. For this reason, developers often need to develop a system to log database changes to ensure that data is recoverable, and that users are responsible for their actions.

In this article, you will find a logging solution for use with LINQ to SQL, and will learn how to modify a LINQ DataContext to automatically log changes.

Background

Database change logging is usually handled either by a custom system using Stored Procedures, or more often through database triggers. But both options are time sinks for developers, and triggers, while a very reliable option, can be a huge performance hit to a database. For this reason, I have developed a library to handle database change logging in the application layer. All you need to do is include the event-logging code file, and create a class that inherits from your custom LINQ class.

This article assumes that you know how to use LINQ, and that you have a LINQ to SQL class set up already.

Initial Setup

The first thing you'll want to do before using this code is to create a table in your database called TableHistory:

CREATE TABLE [dbo].[TableHistory] (
    [TableHistoryID] [int] IDENTITY NOT NULL ,
    [TableName] [varchar] (50) NOT NULL ,
    [Key1] [varchar] (50) NOT NULL ,
    [Key2] [varchar] (50) NULL ,
    [Key3] [varchar] (50) NULL ,
    [Key4] [varchar] (50) NULL ,
    [Key5] [varchar] (50) NULL ,
    [Key6] [varchar] (50)NULL ,
    [ActionType] [varchar] (50) NULL ,
    [Property] [varchar] (50) NULL ,
    [OldValue] [varchar] (8000) NULL ,
    [NewValue] [varchar] (8000) NULL ,
    [ActionUserName] [varchar] (50) NOT NULL ,
    [ActionDateTime] [datetime] NOT NULL 
)

Once you have created the table, you'll need to add it to your custom LINQ class (which I will refer to as DboDataContext), thus creating the TableHistory class. Then, you'll need to add the History.cs file to your project.

You'll also want to add the following code to your project to get the system date:

public partial class DboDataContext
{

    [Function(Name = "GetDate", IsComposable = true)]
    public DateTime GetSystemDate()
    {
        MethodInfo mi = MethodBase.GetCurrentMethod() as MethodInfo;
        return (DateTime)this.ExecuteMethodCall(this, mi, new object[] { }).ReturnValue;
    }
}

private static Dictionary<type,> _cachedIL = new Dictionary<type,>();

public static T CloneObjectWithIL<t>(T myObject)
{
    Delegate myExec = null;
    if (!_cachedIL.TryGetValue(typeof(T), out myExec))
    {
        // Create ILGenerator
        DynamicMethod dymMethod = new DynamicMethod("DoClone", 
                                      typeof(T), new Type[] { typeof(T) }, true);
        ConstructorInfo cInfo = myObject.GetType().GetConstructor(new Type[] { });

        ILGenerator generator = dymMethod.GetILGenerator();

        LocalBuilder lbf = generator.DeclareLocal(typeof(T));
        //lbf.SetLocalSymInfo("_temp");

        generator.Emit(OpCodes.Newobj, cInfo);
        generator.Emit(OpCodes.Stloc_0);
        foreach (FieldInfo field in myObject.GetType().GetFields(
           System.Reflection.BindingFlags.Instance | 
           System.Reflection.BindingFlags.Public | 
           System.Reflection.BindingFlags.NonPublic))
        {
            // Load the new object on the eval stack... (currently 1 item on eval stack)
            generator.Emit(OpCodes.Ldloc_0);
            // Load initial object (parameter)      (currently 2 items on eval stack)
            generator.Emit(OpCodes.Ldarg_0);
            // Replace value by field value         (still currently 2 items on eval stack)
            generator.Emit(OpCodes.Ldfld, field);
            // Store the value of the top on the eval stack into
            // the object underneath that value on the value stack.
            //  (0 items on eval stack)
            generator.Emit(OpCodes.Stfld, field);
        }

        // Load new constructed obj on eval stack -> 1 item on stack
        generator.Emit(OpCodes.Ldloc_0);
        // Return constructed object.   --> 0 items on stack
        generator.Emit(OpCodes.Ret);

        myExec = dymMethod.CreateDelegate(typeof(Func<t,>));
        _cachedIL.Add(typeof(T), myExec);
    }
    return ((Func<t,>)myExec)(myObject);
}

I got both of the above methods off of the net somewhere (maybe even from CodeProject), but it's been long enough that I can't recall where I got them.

Explanation of the History Class

The History class records changes by creating a TableHistory record, inserting the values for the primary key for the table being modified into the Key1, Key2, ..., Key6 columns (if you have more than 6 values that make up a primary key on any table, you'll want to modify this), setting the type of change being made in the ActionType column (INSERT, UPDATE, or DELETE), old value and new value if it happens to be an update action, and the date and Windows identity of the user who made the change.

Let's examine what happens when a call is made to the RecordLinqInsert method:

public static void RecordLinqInsert(DboDataContext dbo, IIdentity user, object obj)
{
    TableHistory hist = NewHistoryRecord(obj);

    hist.ActionType = "INSERT";
    hist.ActionUserName = user.Name;
    hist.ActionDateTime = dbo.GetSystemDate();

    dbo.TableHistories.InsertOnSubmit(hist);
}

private static TableHistory NewHistoryRecord(object obj)
{
    TableHistory hist = new TableHistory();

    Type type = obj.GetType();
    PropertyInfo[] keys;
    if (historyRecordExceptions.ContainsKey(type))
    {
        keys = historyRecordExceptions[type].ToArray();
    }
    else
    {
        keys = type.GetProperties().Where(o => AttrIsPrimaryKey(o)).ToArray();
    }

    if (keys.Length > KeyMax)
        throw new HistoryException("object has more than " + KeyMax.ToString() + " keys.");
    for (int i = 1; i <= keys.Length; i++)
    {
        typeof(TableHistory)
            .GetProperty("Key" + i.ToString())
            .SetValue(hist, keys[i - 1].GetValue(obj, null).ToString(), null);
    }
    hist.TableName = type.Name;

    return hist;
}

protected static bool AttrIsPrimaryKey(PropertyInfo pi)
{
    var attrs =
        from attr in pi.GetCustomAttributes(typeof(ColumnAttribute), true)
        where ((ColumnAttribute)attr).IsPrimaryKey
        select attr;

    if (attrs != null && attrs.Count() > 0)
        return true;
    else
        return false;
}

RecordLinqInsert takes as input a data context which it will use to write to the database, the user, and the LINQ object to be recorded (a single object, for instance, a Customer or Order object if you're using AdventureWorks). It then calls the NewHistoryRecord method, which uses LINQ to Objects in conjunction with the AttrIsPrimaryKey method to pull all the primary key properties, set the Key1-KeyN properties of the TableHistory object, and return the new TableHistory object. The code would be called in an application, like so:

DboDataContext dbo = new DboDataContext();
Customer cust = new Customer();
dbo.Customers.InsertOnSubmit(cust);
History.RecordLinqInsert(dbo, WindowsIdentity.GetCurrent(), cust);
dbo.SubmitChanges();

Updates are handled by creating a new TableHistory record for each property changed. The process for using the RecordLinqUpdate function is similar, but it requires a copy of the LINQ object as it was before the modification:

DboDataContext dbo = new DboDataContext();
Customer cust = dbo.Customers.Where(o => o.CustomerID == 1001).Single();
Customer oldCust = CloneObjectWithIL<customer>(cust);
cust.Name = "ASDF";
History.RecordLinqUpdate(dbo, WindowsIdentity.GetCurrent(), oldCust, cust);
dbo.SubmitChanges();

The DboTrackedDataContext Class

Finally, we get to the really cool part -- the auto-tracking DataContext! We derive a class from DboDataContext as shown below to track all changes made with a DboTrackedDataContext. For this, we need to add a property to use for the connection string for the history recording, and also to override the SubmitChanges method.

public class DboTrackedDataContext : DboDataContext
{
    private string historyConnectionString;
    public string HistoryConnectionString
    {
        get 
        {
            if (String.IsNullOrEmpty(historyConnectionString))
                return global::System.Configuration.ConfigurationManager.
                   ConnectionStrings["DboConnectionString"].ConnectionString;
            else
                return historyConnectionString;
        }
        set { historyConnectionString = value; }
    }

    public PIPrimaryTrackedDataContext() : base() { }
    public PIPrimaryTrackedDataContext(string connectionString) 
        : base(connectionString) { }
    public PIPrimaryTrackedDataContext(string connectionString, 
                                       MappingSource mappingSource)
        : base(connectionString, mappingSource) { }
    public PIPrimaryTrackedDataContext(IDbConnection connection)
        : base(connection) { }
    public PIPrimaryTrackedDataContext(IDbConnection connection, 
                                       MappingSource mappingSource) 
        : base(connection, mappingSource) { }

    public List<type> ExcludedTypes = new List<type>() { typeof(TableHistory) };



public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
{            
    WindowsIdentity identity = WindowsIdentity.GetCurrent();
    PIPrimaryDataContext histDbo = new PIPrimaryDataContext(HistoryConnectionString);

        ChangeSet changeSet = GetChangeSet();
        foreach (var delete in changeSet.Deletes)
        {
            History.RecordLinqDelete(histDbo, identity, delete);
            if (ExcludedTypes.Contains(delete.GetType())) 
                continue;
        }
        foreach (var update in changeSet.Updates)
        {
            if (ExcludedTypes.Contains(update.GetType()))
                continue;
            object table = GetTable(update.GetType());
            Type tableType = table.GetType();
            MethodInfo getOriginal = tableType.GetMethod("GetOriginalEntityState");
            object oldObj = getOriginal.Invoke(table, new object[] { update });
            History.RecordLinqUpdate(histDbo, identity, oldObj, update);
        }

        base.SubmitChanges(failureMode);

        foreach (var insert in changeSet.Inserts)
        {
            if (ExcludedTypes.Contains(insert.GetType()))
                continue;
            History.RecordLinqInsert(histDbo, identity, insert);
        }
        histDbo.SubmitChanges();
    }
}

As you can see, the overridden SubmitChanges method calls GetChangeSet() to search through the list of changes, and records each insert, update, and delete. It then submits all of them at the end, in order to improve logging efficiency by making only one extra database transaction.

Points of Interest

This was a fun project, and it turned out really well. In under 400 lines of code, you can have a fully functional automatic data change tracking system, and it's more efficient and easier to maintain than the traditional trigger method. In addition, you can automatically pull the Windows username of the person making the change.

History

• 1.0 - Article relased.