LINQ To SQL – Audit changes

I was working on a project where I needed to insert an audit record when changes to the database were made.  My first thought was to use triggers, but unfortunately, the deployment model of this application made that option impossible.  So then I turned to the data access layer of the application.  Sure, an “easy” option would’ve been to go through all the calls to the database and add a call to the “audit” function, but that seemed error-prone and difficult to maintain.  Then i turned to the Data Context, which is handled by the DBML class in Linq to SQL.  The DBML inherits from System.Data.Linq.DataContext.  I created a new base class called “AuditableContext” which inherits from DataConext and used it as an interception point for my calls to the Database.  It was easy to switch the DBML to inherit from my new AuditableContext class…just open up the DBML in design mode and go to its properties, then set the Base Class property.

Having done that, now I had a nice interception point for all my calls.  So now I created an Override method for SubmitChanges(), which gets called on any Insert, Update, Delete operation using L2S (note, that this is not called for stored procedures).  In SubmitChanges() I can interrogate the changeset and pull out the fields that changed, the before and after values, and of course the table name.  I also added an “AuditEnabled” property that allows me to turn the behavior on or off (it’s off by default) at the time that i instantiate the context.  Here is the code that gets the changes out…it’s VB.NET, so don’t be afraid 🙂   I simplified the code a bit for the demo, so be sure to check for nulls and apply good exception handling…this shows the logic to capture updates, but if you need inserts and deletes as well, you can follow the same pattern. 

 Public Overrides Sub SubmitChanges(ByVal failureMode As System.Data.Linq.ConflictMode)
        If AuditEnabled Then
            Dim _Updates As IEnumerable = GetChangeSet().Updates
            If Not _Updates Is Nothing Then
                For Each _Entity As Object In _Updates
                    Dim _EntityType As Type = _Entity.GetType()
                    Dim _Table As ITable = GetTable(_EntityType)
                    Dim _ModifiedList As ModifiedMemberInfo() = _Table.GetModifiedMembers(_Entity)

                    For Each _ModifiedData As ModifiedMemberInfo In _ModifiedList
                        Dim _OriginalValue As String = _ModifiedData.OriginalValue.ToString
                        Dim _CurrentValue As String = _ModifiedData.CurrentValue.ToString

                        If m_OriginalValue <> m_CurrentValue Then
                            LogAudit(_EntityType.Name, _ModifiedData.Member.Name, _OriginalValue, _CurrentValue)
                        End If
                    Next
                Next
            End If
        End If

        MyBase.SubmitChanges(failureMode)
    End Sub