Audit history of an object

Feb 12, 2010 at 4:16 AM

I have to log the changes of Order and OrderDetail  in the database.

Currently I just load two copies of Order with its OrderDetails.  I changed some values in one copy and submit  my changes.

Before the changes are submitted, I iterate through the changed copy and compare it to the unchanged copy, write the audit history to the database.

Is there a better way to do this? My method looks awkward...

Feb 12, 2010 at 11:02 AM

Ross --

Here is how I do something like that.

I have an interface that all my L2SEB objects implement which requires each object to have a audit method, something like "AuditNow()".

At the end of the OnValidate for create/update/delete extensibility method, I call AuditNow() wherein I serialize the entity as XML and save it to an audit table.

That way, I have a snapshot of the objects state through its lifecycle.

It is a bit fuzzy because exceptions could occur between the end of OnValidate and the time the entity actually gets to the database-- but, since I have error handling, I do not care about that.

This works, it is easy, and I can review the changes to the object simply.

HTH.

Thank you.

-- Mark Kamoski

Feb 12, 2010 at 2:23 PM

Mark,

Can you show some code snippet for your implementation?

How can I access the original value of the object after it is modified?

Thank you.

Ross

Feb 12, 2010 at 2:52 PM

Ross --

Sure thing.

One caveat is that my implementation is GEFN, not perfect.

The logging library that I use is 3rd party and VERY good; but, it makes my audit implementation a verbose-- but it is "almost" automatic in the end and it works great.

If one is using the Kellerman logger, then this design is good as-is-- if not, then the IDEA behind this design is good but the implementation is not ideal.

 

This is the interface, which guarantees that every entity will implement audit methods...

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Test.Framework.Interfaces.BusinessLayer.BusinessEntities
{
	/// <summary>
	/// This is a shared entity interface for projects using Linq To Sql (AKA L2s).
	/// </summary>
	public interface IL2sEntity
	{
		void CreateAuditForInsert();
		void CreateAuditForUpdate();
		void CreateAuditForDelete();
	}
}

 

This is a class that implements the interface...

using System;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Test;
using Test.TestProject;
using Test.TestProject.BusinessLayer;
using Test.TestProject.BusinessLayer.BusinessEntities;
using Test.TestProject.BusinessLayer.BusinessManagers;

namespace Test.TestProject.BusinessLayer.BusinessEntities
{
	public partial class CitizenshipCode : Test.Framework.Interfaces.BusinessLayer.BusinessEntities.IL2sEntity
	{
		#region ExtensibilityMethodImplementations

		partial void OnValidate(System.Data.Linq.ChangeAction action)
		{
			if ((action == System.Data.Linq.ChangeAction.Insert) || (action == System.Data.Linq.ChangeAction.Update))
			{
				if (this.PkId == Guid.Empty)
				{
					this.PkId = Guid.NewGuid();
				}

				this.ModifiedBy = BusinessLayer.BusinessComponents.EntityHelper.GetModifiedByValue();
				this.ModifiedDateTime = DateTime.Now;
				this.DescriptionText = (this.DescriptionText + "").Trim();
				this.NameText = (this.NameText + "").Trim();

				if (this.NameText.Length <= 0)
				{
					throw new System.ApplicationException("The input for name is not valid because it has Length='" +
						this.NameText.Length + "'.");
				}
			}

			//Check for an existing name.

			CitizenshipCodeManager myManager = new CitizenshipCodeManager();

			if (myManager.IsExistingNameText(this.NameText))
			{
				if (action == System.Data.Linq.ChangeAction.Insert)
				{
					throw new System.ApplicationException("The input for name is is already being used, where action='" +
						action.ToString() + "'.");
				}
				else if (action == System.Data.Linq.ChangeAction.Update)
				{
					CitizenshipCode myCode = (CitizenshipCode)myManager.RetrieveByNameText(this.NameText);

					if (myCode.PkId != this.PkId)
					{
						throw new System.ApplicationException("The input for name is already being used, where action='" +
							action.ToString() + "'.");
					}
				}
			}

			//Audit.
			if (action == System.Data.Linq.ChangeAction.Delete)
			{
				this.CreateAuditForDelete();
			}
			else if (action == System.Data.Linq.ChangeAction.Insert)
			{
				this.CreateAuditForInsert();
			}
			else if (action == System.Data.Linq.ChangeAction.None)
			{
				//Continue.
			}
			else if (action == System.Data.Linq.ChangeAction.Update)
			{
				this.CreateAuditForUpdate();
			}
			else
			{
				throw new System.ApplicationException("The given object, action='" + action.ToString() + "', is not supported.");
			}
		}

		#endregion //ExtensibilityMethodImplementations

		#region InterfaceImplementations

		public void CreateAuditForInsert()
		{
			SystemConfigSettingManager mySystemConfigSettingManager = new SystemConfigSettingManager();
			bool isAuditLogEnabled = mySystemConfigSettingManager.GetValueAsBool(
				Test.TestProject.CommonLayer.Core.Enums.SystemConfigSettingKey.AuditLogIsEnabledFlag.ToString());

			if (isAuditLogEnabled)
			{
				KellermanSoftware.NetLoggingLibrary.Log.Info(Test.Framework.Common.Linq.LINQHelper.SerializeEntity(
					this, Test.TestProject.CommonLayer.Core.Consts.DefaultSerializeToUtf8Flag));
			}
		}

		public void CreateAuditForUpdate()
		{
			SystemConfigSettingManager mySystemConfigSettingManager = new SystemConfigSettingManager();
			bool isAuditLogEnabled = mySystemConfigSettingManager.GetValueAsBool(
				Test.TestProject.CommonLayer.Core.Enums.SystemConfigSettingKey.AuditLogIsEnabledFlag.ToString());

			if (isAuditLogEnabled)
			{
				KellermanSoftware.NetLoggingLibrary.Log.Info(Test.Framework.Common.Linq.LINQHelper.SerializeEntity(
					this, Test.TestProject.CommonLayer.Core.Consts.DefaultSerializeToUtf8Flag));
			}
		}

		public void CreateAuditForDelete()
		{
			SystemConfigSettingManager mySystemConfigSettingManager = new SystemConfigSettingManager();
			bool isAuditLogEnabled = mySystemConfigSettingManager.GetValueAsBool(
				Test.TestProject.CommonLayer.Core.Enums.SystemConfigSettingKey.AuditLogIsEnabledFlag.ToString());

			if (isAuditLogEnabled)
			{
				KellermanSoftware.NetLoggingLibrary.Log.Info(Test.Framework.Common.Linq.LINQHelper.SerializeEntity(
					this, Test.TestProject.CommonLayer.Core.Consts.DefaultSerializeToUtf8Flag));
			}
		}

		#endregion //InterfaceImplementations
	}
}

 

HTH.

Thank you.

-- Mark Kamoski

Feb 12, 2010 at 3:00 PM
rosss wrote:

Mark,

Can you show some code snippet for your implementation?

How can I access the original value of the object after it is modified?

Thank you.

Ross

Ross --

Regarding a code snippet, see above.

Regarding the orginal value, I do not know. However, since I log every change in my design, I do not care. The current value in the database is the current value. The audit table has a snapshot of the object at every stage of its lifetime. Therefore, if I want to do an audit of object_1 from date_1 to date_2, then I just select from my audit table and get all object_1 snapshots between date_1 and date_2 and I can see everything that happened to that object during that time period. That is a about as tight as auditing gets I think. It can be tricky to reconstitute composed objects; but, it can be done and, in general, I will typically need to audit changes to a root entity in my "simple database modellilng paradigm" that I use.  Also, and of course, I auto-truncate my audit table such that it has at most the N most-recent rows, to make sure it does not get too full-- thankfully, the Kellerman Logging Library http://www.kellermansoftware.com/ does the auto-truncate processing for me-- sweet.

BTW, I have an end-to-end sample application that shows how I am using L2SEB and L2S and T4 and T4ToolBox to make a nice simple and generic Repository pattern implementation, etc, so look here...

http://mkamoski1.wordpress.com/2009/12/01/t4-t4-toolbox-linq-to-sql-entity-base-code-sample/

...if you are interested.

HTH.

Thank you.

-- Mark Kamoski

 

Coordinator
Feb 12, 2010 at 8:11 PM

Hi guys, to add to your conversation...

The original value is stored when changes are made if you set the "onModifyKeepOriginalFlag" on the SetAsChangeTrackingRootMethod.

Once you have done this, you can access the original version of an entity by using the "LINQEntityOriginalValue" property (simply change the LINQ2SQLEB class to make it public, or expose it through your own property).

Also, to tell if an object differs from it's original version, use the ShallowCompare method on the LINQ2SQLEB, which will compare the columns of two entities and tell you if the entities differ - if you look at the code for this, you should be able to create another functon which is similar that returns the names of the fields that differ, and write these and there values into an audit table (or whatever).

Also!  A simple way of doing this too is just to use the "onModifyKeepOriginalFlag" and using the LINQ2SQL logging feature, which returns the statements that were used to modify the database - you could store this or extract the details from the string.

Cheers

Matt

 

Feb 20, 2010 at 3:50 AM

Matt and Mark, Thank you guys.

I took Matt's route.  Use reflections to query the original value and current value similar to ShallowCompare method.

Is there a way to get only value type properties like datetime, integer and string?  I have to skip property like LINQEntityState and LINQOrignalValue by checking the property name with name.StartWith("LINQ")

Feb 22, 2010 at 6:55 PM
rosss wrote:

...Is there a way to get only value type properties like datetime, integer and string?

You probably need to test the PropertyType, as a more generic solution, rather than relying on a hardcode prefix, etc.

Here is some code that may help.

		[Test]
		public void GetReflectionInfoTest()
		{
			this.GetReflectionInfo();
		}

		private void GetReflectionInfo()
		{
			CitizenshipCode myObject = new CitizenshipCode();

			foreach (PropertyInfo myInfo in myObject.GetType().GetProperties())
			{
				Debug.WriteLine("-----");
				Debug.WriteLine("Name='" + myInfo.Name + "'");
				Debug.WriteLine("PropertyType='" + myInfo.PropertyType.ToString() + "'");
				Debug.WriteLine("GetType='" + myInfo.GetType().ToString() + "'");
				Debug.WriteLine("MemberType='" + myInfo.MemberType.ToString() + "'");
				Debug.WriteLine("ReflectedType='" + myInfo.ReflectedType.ToString() + "'");
				Debug.WriteLine("DeclaringType='" + myInfo.DeclaringType.ToString() + "'");
			}

			//Sample output...
			//
			//-----
			//Name='PkId'
			//PropertyType='System.Guid'
			//GetType='System.Reflection.RuntimePropertyInfo'
			//MemberType='Property'
			//ReflectedType='Test.Project1.BusinessLayer.BusinessEntities.CitizenshipCode'
			//DeclaringType='Test.Project1.BusinessLayer.BusinessEntities.CitizenshipCode'
		}


HTH.
Thank you.
-- Mark Kamoski
Feb 22, 2010 at 8:00 PM

I changed to use PropertyType.

if(pInfo.PropertyType.IsValueType || pInfo.PropertyType == typeof(string))

 

Thank you.