[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

Tweet