[Update]
You cand find the code to do the same with entity framework here
[/Update]
Recently I had to work on a project using Linq to sql and I was suprised to see that Linq to SQL has no built-in auditing ability.
There is no support for logical deletion neither.
With the SubSonic’s ActiveReccord, all these features are built in (see the documentation page).
So, I decided to add this ability for the project, because I doesn’t want to change all the existing request to add these informations.
In my BLL layer, I have a manager object for my DAL Linq objects, so by Example if I have an “User” Object in my DAL layer, I have an “UserManager” object in my BLL layer.
All my managers objects inherit a “ManagerBase” Object, which contains only a “DataContext” Property, which can be used in the child classes.
So my first approach was to add a “SubmitChanges” method in that class and call it from my child classes instead of directly call the one from the DataContext.
In my SubmitChanges method I add the needed logic before calling the one from the DataContext.
But I realized that when there are more than one developper working on an application, it can be difficult to ensure that everybody use the same technique. A person may not use the base class, another may forget to use the base class method, or other things..
Then I investigated the DataContext object with Reflector, and I noticed that the SubmitChanges(void) method directly call the SubmitChanges(ConflictMode) one
public void SubmitChanges()
{
this.CheckDispose();
this.SubmitChanges(ConflictMode.FailOnFirstConflict);
}
And if I take a look at the generated DataContext, I see that the DataContext is a partial class
public partial class MyContext: System.Data.Linq.DataContext
{
// ...
}
So, I decided to add a new file in my DAL layer and extend the DataContext
partial class MyContext
{
// ...
}
Then override the SubmitChanges(ConflictMode) DataContext method
public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
{
SetSystemFields(); // notice this method call !
base.SubmitChanges(failureMode);
}
In the SetSystemFields method, I retrieve the pending changes
private void SetSystemFields()
{
ChangeSet changeset = this.GetChangeSet();
// ...
}
Then I retrieve the user identity in order to be able to set the CreatedBy and ModifiedBy properties of my data objects
System.Security.Principal.WindowsIdentity identity = System.Security.Principal.WindowsIdentity.GetCurrent();
string userName = "Unknow";
if (identity != null)
{
userName = identity.Name;
}
Then I loop over the objects that have to be inserted
int maxInserts = changeset.Inserts.Count;
for (int i = 0; i < maxInserts; i++)
{
// ...
}
and I use reflection to get the audit properties and set their values
object entity = changeset.Inserts[i];
Type objectType = entity.GetType();
PropertyInfo[] properties = objectType.GetProperties();
foreach (PropertyInfo property in properties)
{
switch (property.Name.ToUpper())
{
case SystemFields.CreatedOn:
case SystemFields.ModifiedOn:
{
property.SetValue(entity, DateTime.Now, null);
} break;
case SystemFields.CreatedBy:
case SystemFields.ModifiedBy:
{
property.SetValue(entity, userName, null);
} break;
case SystemFields.IsDeleted:
{
property.SetValue(entity, false, null);
} break;
default:
break;
}
}
Remark : “SystemFields” is a struct I defined with the audit column names but you may as well make it come from a config file.
private struct SystemFields
{
public const string CreatedOn = "CREATEDON";
public const string ModifiedOn = "MODIFIEDON";
public const string CreatedBy = "CREATEDBY";
public const string ModifiedBy = "MODIFIEDBY";
public const string IsDeleted = "ISDELETED";
}
I do the same treatment for the update and deletes (if I want to enable them).
Now everytime that the DataContext’s SubmitChanges method is called, the audit fields are automaticaly set!
Here is the full partial class for the DataContext
using System;
using System.Data.Linq;
using System.Reflection;
namespace MyNamespace.DAL
{
partial class MyContext
{
/// <summary>
/// system fields for automatic audit and logical delete
/// </summary>
private struct SystemFields
{
public const string CreatedOn = "CREATEDON";
public const string ModifiedOn = "MODIFIEDON";
public const string CreatedBy = "CREATEDBY";
public const string ModifiedBy = "MODIFIEDBY";
public const string IsDeleted = "ISDELETED";
}
public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
{
SetSystemFields();
base.SubmitChanges(failureMode);
}
private void SetSystemFields()
{
ChangeSet changeset = this.GetChangeSet();
// retrieve the user name
System.Security.Principal.WindowsIdentity identity = System.Security.Principal.WindowsIdentity.GetCurrent();
string userName = "Unknow";
if (identity != null)
{
userName = identity.Name;
}
// insert
int maxInserts = changeset.Inserts.Count;
for (int i = 0; i < maxInserts; i++)
{
object entity = changeset.Inserts[i];
Type objectType = entity.GetType();
PropertyInfo[] properties = objectType.GetProperties();
foreach (PropertyInfo property in properties)
{
switch (property.Name.ToUpper())
{
case SystemFields.CreatedOn:
case SystemFields.ModifiedOn:
{
property.SetValue(entity, DateTime.Now, null);
} break;
case SystemFields.CreatedBy:
case SystemFields.ModifiedBy:
{
property.SetValue(entity, userName, null);
} break;
case SystemFields.IsDeleted:
{
property.SetValue(entity, false, null);
} break;
default:
break;
}
}
}
// update
int maxUpdates = changeset.Updates.Count;
for (int i = 0; i < maxUpdates; i++)
{
object entity = changeset.Updates[i];
Type objectType = entity.GetType();
PropertyInfo[] properties = objectType.GetProperties();
foreach (PropertyInfo property in properties)
{
switch (property.Name.ToUpper())
{
case SystemFields.ModifiedOn:
{
property.SetValue(entity, DateTime.Now, null);
} break;
case SystemFields.ModifiedBy:
{
property.SetValue(entity, userName, null);
} break;
default:
break;
}
}
}
// deletes (logical deletes)
int maxDeletes = changeset.Deletes.Count;
for (int i = 0; i < maxDeletes; i++)
{
object entity = changeset.Deletes[i];
Type objectType = entity.GetType();
PropertyInfo[] properties = objectType.GetProperties();
foreach (PropertyInfo property in properties)
{
switch (property.Name.ToUpper())
{
case SystemFields.ModifiedOn:
{
property.SetValue(entity, DateTime.Now, null);
} break;
case SystemFields.ModifiedBy:
{
property.SetValue(entity, userName, null);
} break;
case SystemFields.IsDeleted:
{
property.SetValue(entity, true, null);
} break;
default:
break;
}
}
}
}
}
}
