Monday 19 May 2008

Simple Auditing with LINQ to SQL - Date, Time and User Stamps

My previous post was on automatically generating basic auditing fields on SQL Server tables via SQL scripts. Today, I examine the other side of the coin - using the LINQ data context to stamp records with audit fields such as "Modified","ModifiedBy", "Created" and "CreatedBy". These stamps are similar to the functionality provided by the windows file system.

There are many ways you can populate application audit tables or audit fields. These include:
  1. With Triggers (but this relies on the SQL authenication mechanism for accurate user data);
  2. With stored procedures (this also relies on the accuracy of SQL authentication information or you need to append the user name to the parameters of the procedure.)
  3. On the Application Side - Manually coding all service/database calls to append this information.
  4. Use a SQL Server log auditing tool like LogExplorer that tracks the SQL Server transaction log.
  5. Leveraging your data access layer's update point to append this information before the update takes place.

Below you can find my code which takes the last approach - and uses reflection to stamp records with when a record was created/updated and who did the insert/update. This is a simplified alternative to an audit solution (such as http://blog.matthidinger.com/2008/05/09/LINQToSQLAuditTrail.aspx) which has a full audit table and where requirements are just to display who last modified or created a particular record in your application:




/// <summary>
/// Basic Audit User and Date Stamp Functionality
/// </summary>
/// <param name="failureMode"></param>
public override void SubmitChanges(ConflictMode failureMode)
{

//Updates
for (int changeCounter = 0; changeCounter < this.GetChangeSet().Updates.Count; changeCounter++)
{
object modifiedEntity = this.GetChangeSet().Updates[changeCounter];
SetAuditStamp(this, modifiedEntity, ChangeType.Update);
}

//Inserts
for (int changeCounter = 0; changeCounter < this.GetChangeSet().Inserts.Count; changeCounter++)
{
object modifiedEntity = this.GetChangeSet().Inserts[changeCounter];
SetAuditStamp(this, modifiedEntity, ChangeType.Insert);
}

base.SubmitChanges(failureMode);
}

/// <summary>
/// For Inserts or Updates - set the user and date stamps
/// </summary>
/// <param name="context"></param>
/// <param name="modifiedEntity"></param>
private void SetAuditStamp(DataContext context, object modifiedEntity, ChangeType changeType)
{
string userName = System.Threading.Thread.CurrentPrincipal.Identity.Name;
const string Created = "Created", CreatedBy = "CreatedBy",
Modified = "Modified", ModifiedBy = "ModifiedBy";

if (changeType == ChangeType.Insert)
{
SetAuditValue(modifiedEntity, Created, System.DateTime.Now);
SetAuditValue(modifiedEntity, CreatedBy, userName);
}
else if (changeType == ChangeType.Update)
{
SetAuditValue(modifiedEntity, Modified, System.DateTime.Now);
SetAuditValue(modifiedEntity, ModifiedBy, userName);
}
}

/// <summary>
/// The type of modifications
/// </summary>
private enum ChangeType
{
Update,
Insert
}

/// <summary>
/// Set target value if it exists on the object
/// </summary>
/// <param name="modifiedEntity"></param>
/// <param name="fieldName"></param>
/// <param name="propertyValue"></param>
private void SetAuditValue(object modifiedEntity, string fieldName, object propertyValue)
{
if (modifiedEntity.GetType().GetProperty(fieldName) != null) //Set current user and time stamp
{
modifiedEntity.GetType().GetProperty(fieldName).SetValue(modifiedEntity, propertyValue, null);
}
}

5 comments:

Unknown said...

Wow, really nice, let's see if it works....
Amazing.
Unfortunately for this project i need VB (its disgusting syntax imho), but the code is:
Public Overrides Sub SubmitChanges(ByVal failureMode As System.Data.Linq.ConflictMode)

'inserts
For Each modifiedEntry As Object In Me.GetChangeSet().Inserts
SetAuditStamp(Me, modifiedEntry, ChangeType.Insert)
Next

'updates
For Each modifiedEntry As Object In Me.GetChangeSet().Updates
SetAuditStamp(Me, modifiedEntry, ChangeType.Update)
Next

MyBase.SubmitChanges(failureMode)
End Sub

Private Enum ChangeType
Update
Insert
End Enum

Private Sub SetAuditStamp(ByVal context As DataContext, ByVal modifiedEntry As Object, ByVal changeType As ChangeType)
Dim user As sys_User = App.User
Const Created As String = "Felvitel_Datum", Created_By As String = "Felvivo", Modified As String = "Modositas_Datum", Modified_By As String = "Modosito"

If changeType = DBDataContext.ChangeType.Insert Then
SetAuditValue(modifiedEntry, Created, System.DateTime.Now)
SetAuditValue(modifiedEntry, Created_By, user.User_ID)
End If

If changeType = DBDataContext.ChangeType.Update Then
SetAuditValue(modifiedEntry, Modified, System.DateTime.Now)
SetAuditValue(modifiedEntry, Modified_By, user.User_ID)
End If



End Sub

Private Sub SetAuditValue(ByVal modifiedEntity As Object, ByVal fieldName As String, ByVal propertyValue As Object)
If Not modifiedEntity.GetType().GetProperty(fieldName) Is Nothing Then
modifiedEntity.GetType().GetProperty(fieldName).SetValue(modifiedEntity, propertyValue, Nothing)
End If
End Sub

Brent said...

Your reflection could be simplified (and sped up considerably) if you created an interface such as this one:
interface IHasRowLevelAuditing
{
DataTime Created{get;set;}
String CreatedBy{get;set;}
DataTime Modified{get;set;}
String ModifiedBy{get;set;}
}

Then, add the interface to all your entity classes like this...

partial class Employee
: IHasRowLevelAuditing
{}

And then, in your submit changes code, instead of using the for statements, you use, you could try something like this...

//Updates
foreach (IHasRowLevelAuditing modifiedEntity in this.GetChangeSet().Updates.OfType<IHasRowLevelAuditing >())
{
modifiedEntity.Modified = DateTime.Now;
modifiedEntity.ModifiedBy = username;
}

and off you go...

Thanks for a good post. I like the idea of linking in at the point where you did in order to manage this sort of stuff in a uniform way across all tables.

Unknown said...

Using a marker interface is also the approach that I generally use to implement this. It is a very simple solution.

Generic auditing in Linq to Sql

Peter said...

I noticed I've written the same just the other day, yet completely different! It probably is a lot slower too :)

private bool updateAuditInformation(IEntity entity, string creationUserEmail, string modificationUserEmail)
{
var table = Context.GetTable();
var mapping = Context.Mapping.GetTable(typeof(T));

// Update SubEntitySets from LinqToSQL entity
foreach (PropertyInfo propertyInfo in entity.GetType().GetProperties())
{
if (propertyInfo.PropertyType.IsGenericType &&
typeof(EntitySet<>).IsAssignableFrom(propertyInfo.PropertyType.GetGenericTypeDefinition()))
{
IEnumerable enumerable = propertyInfo.GetValue(entity, null) as IEnumerable;
IEnumerator enumerator = enumerable.GetEnumerator();

while (enumerator.MoveNext())
{
updateAuditInformation(enumerator.Current as IEntity, creationUserEmail, modificationUserEmail);
}
}
}

// Update current Entity Audit information if present
if (!string.IsNullOrWhiteSpace(creationUserEmail))
{
var creationDateField = mapping.RowType.DataMembers.FirstOrDefault(d => d.Name == "CreationDate");
var creationUserIdField = mapping.RowType.DataMembers.FirstOrDefault(d => d.Name == "CreationUser");
if (creationDateField != null)
entity.GetType().GetProperties().Where(p => p.CanRead && p.Name == creationDateField.Name).SingleOrDefault().SetValue(entity, DateTime.UtcNow, null);
if (creationUserIdField != null)
entity.GetType().GetProperties().Where(p => p.CanRead && p.Name == creationUserIdField.Name).SingleOrDefault().SetValue(entity, creationUserEmail, null);
}
if (!string.IsNullOrWhiteSpace(modificationUserEmail))
{
var modificationDateField = mapping.RowType.DataMembers.FirstOrDefault(d => d.Name == "ModificationDate");
var modificationUserIdField = mapping.RowType.DataMembers.FirstOrDefault(d => d.Name == "ModificationUser");
if (modificationDateField != null)
entity.GetType().GetProperties().Where(p => p.CanRead && p.Name == modificationDateField.Name).SingleOrDefault().SetValue(entity, DateTime.UtcNow, null);
if (modificationUserIdField != null)
entity.GetType().GetProperties().Where(p => p.CanRead && p.Name == modificationUserIdField.Name).SingleOrDefault().SetValue(entity, modificationUserEmail, null);
}
return true;
}

stealthbits said...

Hi Dude,

In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution. Thanks!

SMP For SQL