Smit Shah's Blog

We have been using LINQ for over 18 months now.

The functionality and development speed is great.

However, we found it really hard and time consuming to log changes.

I have looked at several online tutorials, most of them want you to use their class libraries. Others make it way too complex for simple tracking.



Below is the goal and changes done to the system.

1) Create a SQL table to track changes.

This table will grow as the logs increase so I recommend backing up the logs as time progresses.

CREATE TABLE [dbo].[AuditLog](

    [UserId] [uniqueidentifier] NULL,

    [TableName] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [FieldName] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [FieldValue] [varchar](7500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [AuditDateTime] [datetime] NULL

) ON [PRIMARY]

GO


CREATE CLUSTERED INDEX [IX_AuditLog_ForTable] ON [dbo].[AuditLog]

(

    [TableName] ASC

) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_AuditLog_ForUser] ON [dbo].[AuditLog]

(

    [UserId] ASC,

    [TableName] ASC

) ON [PRIMARY]

GO




2) Add the table to the DBML file.

This way you can use the existing datacontext and simply tag on the connection to save your data.





3) Overload the SubmitChanges function.

Unfortunately you can not override the function, so the only option is to create your own definition.

We did not pass any parameters during the save function, if you do, the definition might be different for you.




    Public Overloads Sub SubmitChanges(ByVal AuditChanges As Boolean, ByVal UserId As String)

        If AuditChanges Then
            Dim cs As System.Data.Linq.ChangeSet = Me.GetChangeSet
            For Each ChangeInsert In cs.Inserts
                Dim type As Type = ChangeInsert.GetType()
                If type.Name.ToUpper = "AuditLog".ToUpper Then
                    'Dont Audit the Audit Table
                    Exit Sub
                End If
                Dim properties() As System.Reflection.PropertyInfo = type.GetProperties()
                For Each p As System.Reflection.PropertyInfo In properties
                    Dim NewAuditLog As New DealerInventory.AuditLog
                    NewAuditLog.AuditId = Guid.NewGuid
                    NewAuditLog.UserId = New Guid(UserId)
                    NewAuditLog.FieldName = p.Name
                    NewAuditLog.TableName = type.Name
                    NewAuditLog.FieldValue = Left(DebugTextWriter.GetString(p.GetValue(ChangeInsert, Nothing)), 7500)
                    NewAuditLog.AuditDateTime = Now

                    Me.AuditLogs.InsertOnSubmit(NewAuditLog)
                Next
            Next
            For Each ChangeUpdates In cs.Updates
                Dim type As Type = ChangeUpdates.GetType()
                If type.Name.ToUpper = "AuditLog".ToUpper Then
                    'Dont Audit the Audit Table
                    Exit Sub
                End If
                Dim properties() As System.Reflection.PropertyInfo = type.GetProperties()
                For Each p As System.Reflection.PropertyInfo In properties
                    Dim NewAuditLog As New DealerInventory.AuditLog
                    NewAuditLog.AuditId = Guid.NewGuid
                    NewAuditLog.UserId = New Guid(UserId)
                    NewAuditLog.FieldName = p.Name
                    NewAuditLog.TableName = type.Name
                    NewAuditLog.FieldValue = Left(DebugTextWriter.GetString(p.GetValue(ChangeUpdates, Nothing)), 7500)
                    NewAuditLog.AuditDateTime = Now

                    Me.AuditLogs.InsertOnSubmit(NewAuditLog)
                Next
            Next
            For Each ChangeDeletes In cs.Deletes
                Dim type As Type = ChangeDeletes.GetType()
                If type.Name.ToUpper = "AuditLog".ToUpper Then
                    'Dont Audit the Audit Table
                    Exit Sub
                End If
                Dim properties() As System.Reflection.PropertyInfo = type.GetProperties()
                For Each p As System.Reflection.PropertyInfo In properties
                    Dim NewAuditLog As New DealerInventory.AuditLog
                    NewAuditLog.AuditId = Guid.NewGuid
                    NewAuditLog.UserId = New Guid(UserId)
                    NewAuditLog.FieldName = p.Name
                    NewAuditLog.TableName = type.Name
                    NewAuditLog.FieldValue = Left(DebugTextWriter.GetString(p.GetValue(ChangeDeletes, Nothing)), 7500)
                    NewAuditLog.AuditDateTime = Now

                    Me.AuditLogs.InsertOnSubmit(NewAuditLog)
                Next
            Next
        End If

        MyBase.SubmitChanges()
    End Sub




The code above is a quick and easy way to add auditing to your LINQ to SQL library.



If you have any questions or suggestions please feel free to email me at ullfindsmit@gmail.com



As alwayz

Enzzoy



Smit.