A Programmer with Microsoft tools

Usually the data of the tables should be tracking for who modified it.

Think about inserting/updating/deleting an employee :   you must know who did those actions and when. So you create another table, identically as structure, and you add another 3 fields , such as [ModifiedDate](when), [ModifiedBy](who), [ModifiedType] (what : insert, update, delete).

There are several methods to do it :

1. from database :
   • you can use triggers and insert data into new table
   • in Sql 2008 , you can use  Change Data Capture ( see also http://cdchelper.codeplex.com/ for a GUI )
2. from programming code  – every time you modify an object, you remember to modify the history object with appropiate data.

The drawback with the database approach is that you can not retrieve who done the modifications ( usually the applications connect under a single account and have a roles table)

The drawback with the programming approach is that the programmer must REMEMBER doing so…If he does not(and does not wrote tests for history), you are stuck…

In the following I propose an automatically history – that maps convention over configuration in my template, but it is easy for you to modify.

The solution works with Entity Framework 4.0 and, for more easily spearation of concerns , with POCO generators.

Let’s say you have the following tables :

As you see we have a Employee and a employee_history, an Department and Department_history

The conventions are:

the history table name = “object” table name  +  “_history” suffix

the history table fields = “object” table name  fields +[ModifiedDate], [ModifiedBy], [ModifiedType]

(if you change those conventions , please change the modelhistory.tt file)

If you want to see in action , please  download code history and do the following
1. create database tests
2. run history.sql
3. run project
4. if necessay, re-create the model1.edmx with the same name and replace the console application app.config with the new connection string

After works, please add any fields to department table  and to department_history table(same field names/type) .  Re-compile the application and modify the new field in department. You will see the modifications in the department_history table.

Ok,now how we do the magic :

We create two new tt file that points to the model.edmx .

The first one ModelHistory.tt , takes care of  creating the constructor for history entities by taking a parameter from the original entity :

public Department_History(Department original):this()
{
this.IDDepartment=original.IDDepartment;
this.Name=original.Name;
}

How it do this magic ? Simple : the ModelHistory.tt recognize the model and history in the name of tables:

</pre>
string inputFile = @"Model1.edmx";
string History = "_History";
<pre>

then it generate code for constructor :

	#>
		public <#=code.Escape(entity)#>():base()
		{
		}
		public <#=code.Escape(entity)#>(<#=NameEntityOriginal #> original):this()
		{
		<#
	foreach (EdmProperty edmProperty in entityOriginal.Properties.Where(p => p.TypeUsage.EdmType is PrimitiveType && p.DeclaringType == entityOriginal))
	{
		#>
				this.<#= code.Escape(edmProperty.Name) #>=original.<#= code.Escape(edmProperty.Name) #>;
		<#

	}
	#>
		}
	<#
</pre>

Ok, and then how to create the history entity ? I wish that the POCO template has had an event “Database saving” – but the only thing I can have is SaveChanges from the ObjectContext – so I create a new ObjectContext , derived from the default one that comes with the project, and creates a new history object :

public override int SaveChanges(SaveOptions options)
{
this.DetectChanges();
DateTime dtModified=DateTime.Now;
string UserModified=clsUser.UserName;
foreach (ObjectStateEntry ose in this.ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified))
{

//could do this way too
//if (ose.Entity != null && ose.Entity.GetType() == typeof(...))
//{
//}
if (ose.Entity != null)
{
string NameType=ose.EntitySet.ElementType.Name;

switch(NameType)
{

case "Department":
var itemDepartment_History = new Department_History(ose.Entity as Department);
//if compile error here, that means you keep tracking
//of which modified with another properties
//please modify the tt accordingly
itemDepartment_History.ModifiedType= ose.State.ToString();
itemDepartment_History.ModifiedDate= dtModified;
itemDepartment_History.ModifiedBy= UserModified;
base.Department_History.AddObject(itemDepartment_History);
break;

case "Employee":
var itemEmployee_History = new Employee_History(ose.Entity as Employee);
//if compile error here, that means you keep tracking
//of which modified with another properties
//please modify the tt accordingly
itemEmployee_History.ModifiedType= ose.State.ToString();
itemEmployee_History.ModifiedDate= dtModified;
itemEmployee_History.ModifiedBy= UserModified;
base.Employee_History.AddObject(itemEmployee_History);
break;

}
}
}

return base.SaveChanges(options);
}

Now all is ready and I made a console application for testing manually (ok, should make a NUnit / MSTest / xUnit )

 using (var ctx = new testsEntitiesHistory())
            {
                var dep = new Department();
                dep.Name = "IT";
                ctx.Departments.AddObject(dep);
                ctx.SaveChanges();
                id = dep.IDDepartment;
            }
            using (var ctx = new testsEntitiesHistory())
            {
                var dep = ctx.Departments.Where(depart => depart.IDDepartment == id).FirstOrDefault();
                dep.Name = "Information tehnology";
                ctx.SaveChanges();
                //
            }
            using (var ctx = new testsEntitiesHistory())
            {
                var dep = ctx.Departments.Where(depart => depart.IDDepartment == id).FirstOrDefault();
                ctx.Departments.DeleteObject(dep);
                ctx.SaveChanges();

            }
            using (var ctx = new testsEntitiesHistory())
            {
                foreach (var dephist in ctx.Department_History)
                {
                    Console.WriteLine("Found {0} with state {1}", dephist.Name,dephist.ModifiedType);
                }
            }

And the output is :

Now you can add more tables to the edmx or change the fields – all is done automatically when compiling

If you want to see in action , please download code history

Update : for another way to do it( generating trigger and tables ) please see : http://msprogrammer.serviciipeweb.ro/2010/09/27/generating-history-trigger-with-ef-edmx-and-tt-files/