Click here to Skip to main content
Click here to Skip to main content

Automatic Event Logging in LINQ

, 30 Nov 2009
Rate this:
Please Sign up or sign in to vote.
An event logging class, and an auto-logging LINQ DataContext descendant built around it.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

David H. Smith
Software Developer (Senior)
United States United States
David Smith is a one-man development team at a major medical support company in the Pacific Northwest. He has been coding since age 11, when he picked up one of his dad's books on C++ during during an especially boring summer break.

Comments and Discussions

 
GeneralIt works not for me, i need more code Pinmemberrspaz1616-Sep-10 1:45 
GeneralRe: It works not for me, i need more code Pinmemberpedrualmeida5-Mar-14 5:16 
GeneralDoes the CloneObjectWithIL do a deep clone PinmvpSacha Barber3-Dec-09 0:05 
GeneralRe: Does the CloneObjectWithIL do a deep clone PinmemberDavid H. Smith3-Dec-09 12:07 
Nope, it's just a fast way of creating a shallow clone, but in this situation a deep clone would be both unecessary and a fairly big performance hit.
GeneralI believe the getdate code might have come from me. PinmvpPete O'Hanlon2-Dec-09 2:16 
GeneralRe: I believe the getdate code might have come from me. PinmemberDavid H. Smith2-Dec-09 7:02 
GeneralRe: I believe the getdate code might have come from me. PinmvpPete O'Hanlon3-Dec-09 0:06 
Generalcan't edit PinmemberDavid H. Smith1-Dec-09 8:06 
GeneralRe: can't edit Pinmemberaspdotnetdev29-Jan-10 3:07 
QuestionThis is faster? PinmemberMike Lang1-Dec-09 6:42 
AnswerRe: This is faster? PinmemberDavid H. Smith1-Dec-09 7:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 30 Nov 2009
Article Copyright 2009 by David H. Smith
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid