There are many ways you can populate application audit tables or audit fields. These include:
- With Triggers (but this relies on the SQL authenication mechanism for accurate user data);
- 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.)
- On the Application Side - Manually coding all service/database calls to append this information.
- Use a SQL Server log auditing tool like LogExplorer that tracks the SQL Server transaction log.
- 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);
}
}
