Technical Notes by Rogelio Prestol: Linq2SQL Audit Trail Log

SQL SCRIPT for Table:

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE [dbo].[t_audit_log](
[f_id] [bigint] IDENTITY(1,1) NOT NULL,
[f_user_id] [int] NOT NULL,
[f_row_id] [varchar](20) NOT NULL,
[f_action] [varchar](1) NOT NULL,
[f_table_name] [varchar](50) NOT NULL,
[f_column_name] [varchar](50) NOT NULL,
[f_data_type] [varchar](50) NOT NULL,
[f_old_value] [text] NULL,
[f_new_value] [text] NULL,
[f_created_date] [datetime] NOT NULL DEFAULT(getdate()),
CONSTRAINT [PK_t_audit_log] PRIMARY KEY CLUSTERED ([f_id] ASC))

VB.NET:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97

Imports Microsoft.VisualBasic
Imports System.Data.Linq.Mapping

Public Class TrackedDataContext
Inherits dbDataContext

Private _items As New Dictionary(Of Object, List(Of t_audit_log))

Public Sub TrackChanges(item As Object)
If item Is Nothing Then Return
If _items.Keys.Contains(item) Then Throw New Exception("This item is already being tracked for changes.")
_items.Add(item, New List(Of t_audit_log))
End Sub

Public Overrides Sub SubmitChanges(failureMode As Data.Linq.ConflictMode)
For Each o In Me.GetChangeSet.Updates
LogChanges(o, "U")
Next

For Each o In Me.GetChangeSet.Deletes
LogChanges(o, "D")
Next

For Each o In Me.GetChangeSet.Inserts
LogChanges(o, "A")
Next

MyBase.SubmitChanges(failureMode)

For Each o In _items
For Each i In o.Value

If i.f_action = "A" AndAlso i.f_column_name = "f_id" Then
i.f_row_id = o.Key.GetType().GetProperty("f_id").GetValue(o.Key).ToString()
i.f_old_value = i.f_row_id
i.f_new_value = i.f_row_id
End If

If Me.t_audit_logs.GetOriginalEntityState(i) Is Nothing Then
Me.t_audit_logs.InsertOnSubmit(i)
End If

Next
Next

MyBase.SubmitChanges(failureMode)
End Sub

Private Sub LogChanges(m As Object, action As String)
If _items.Keys.Contains(m) = False Then Return

Dim a = m.GetType().GetProperties()

Dim id = m.GetType().GetProperty("f_id").GetValue(m)

For Each p In a
Dim k = Me.GetTable(m.GetType).GetModifiedMembers(m).Where(Function(x) x.Member.Name = p.Name).FirstOrDefault()
If k.Member Is Nothing AndAlso action = "U" Then Continue For

Dim nv = p.GetValue(m)
Dim ov = k.OriginalValue

If k.Member Is Nothing Then
ov = nv
End If

If p.CanWrite = False Then
ov = Nothing : nv = Nothing
End If

Dim t = p.PropertyType
Dim TableName = CType(m.GetType().GetCustomAttributes(True)(0), TableAttribute).Name.Replace("dbo.", "")
Dim ColumnName = CType(p.GetCustomAttributes(True)(0), ColumnAttribute).Storage.TrimStart("_")

If t.IsGenericType AndAlso t.GenericTypeArguments.Length > 0 Then
t = t.GenericTypeArguments(0)
End If

If t.IsPrimitive OrElse t = GetType(String) OrElse t = GetType(DateTime) Then
If nv <> ov OrElse "DA".Contains(action) Then
Dim l As New t_audit_log
l.f_action = action
l.f_data_type = t.Name
l.f_table_name = TableName
l.f_column_name = ColumnName
l.f_new_value = nv
l.f_old_value = ov
l.f_row_id = id
l.f_user_id = 1

_items(m).Add(l)
End If
End If
Next
End Sub

End Class

C#:

using System;
using System.Collections.Generic;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Web;

public class TrackedDataContext : dbDataContext
{
private Dictionary<object, List<t_audit_log>> _items = new Dictionary<object,List<t_audit_log>>();

public void TrackChanges(object item)
{
if (item == null) { return; }
if (_items.Keys.Contains(item)) { throw new Exception("This item is already being tracked for changes."); }
_items.Add(item, new List<t_audit_log>());
}

public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
{
foreach (var o in this.GetChangeSet().Updates)
{
LogChanges(o, "U");
}

foreach (var o in this.GetChangeSet().Deletes)
{
LogChanges(o, "D");
}

foreach (var o in this.GetChangeSet().Inserts)
{
LogChanges(o, "A");
}

base.SubmitChanges(failureMode);

foreach (var o in _items)
{
foreach (var i in o.Value)
{
if (i.f_action == "A" && i.f_column_name == "f_id")
{
i.f_row_id = o.Key.GetType().GetProperty("f_id").GetValue(o.Key).ToString();
i.f_old_value = i.f_row_id;
i.f_new_value = i.f_row_id;
}

if (this.t_audit_logs.GetOriginalEntityState(i) == null)
{
this.t_audit_logs.InsertOnSubmit(i);
}
}
}

base.SubmitChanges(failureMode);
}

private void LogChanges(object m, string action)
{
if (_items.Keys.Contains(m) == false) { return; }

var a = m.GetType().GetProperties();

var id = m.GetType().GetProperty("f_id").GetValue(m);

foreach (var p in a)
{
var k = this.GetTable(m.GetType()).GetModifiedMembers(m).Where(x => x.Member.Name == p.Name).FirstOrDefault();
if (k.Member == null && action == "U") { continue; }

var nv = p.GetValue(m);
var ov = k.OriginalValue;

if (k.Member == null)
{
ov = nv;
}

if (p.CanWrite == false)
{
ov = null; nv = null;
}

var t = p.PropertyType;
var TableName = (m.GetType().GetCustomAttributes(true)[0] as TableAttribute).Name.Replace("dbo.", "");
var ColumnName = (p.GetCustomAttributes(true)[0] as ColumnAttribute).Storage.TrimStart('_');

if (t.IsGenericType && t.GenericTypeArguments.Length > 0)
{
t = t.GenericTypeArguments[0];
}

if (t.IsPrimitive || t == typeof(string) || t == typeof(DateTime))
{
if (nv != ov || "DA".Contains(action))
{
var l = new t_audit_log();
l.f_action = action;
l.f_data_type = t.Name;
l.f_table_name = TableName;
l.f_column_name = ColumnName;
l.f_new_value = nv == null ? null : nv.ToString();
l.f_old_value = ov == null ? null : ov.ToString();
l.f_row_id = id.ToString();
l.f_user_id = Users.Current.ID;

_items[m].Add(l);
}
}
}
}
}

Technical Notes by Rogelio Prestol

Thursday, May 22, 2014

Linq2SQL Audit Trail Log

No comments:

Post a Comment