Tuesday, April 28, 2009
LINQ TO SQL AUDIT TABLE
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.
Subscribe to Posts [Atom]