[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; } } } } } }
Incoming search terms:
- linq audit inserts