Event Logging in LINQ C# .NET
Posted
on Microsoft .NET Support Team
See other posts from Microsoft .NET Support Team
Published on Tue, 01 Dec 2009 09:52:00 +0000
Indexed on
2010/03/16
15:31 UTC
Read the original article
Hit count: 533
The first thing you'll want to do before using this code is to create a table in your database called TableHistory:
CREATE TABLE [dbo].[TableHistory] (
[TableHistoryID] [int] IDENTITY NOT NULL ,
[TableName] [varchar] (50) NOT NULL ,
[Key1] [varchar] (50) NOT NULL ,
[Key2] [varchar] (50) NULL ,
[Key3] [varchar] (50) NULL ,
[Key4] [varchar] (50) NULL ,
[Key5] [varchar] (50) NULL ,
[Key6] [varchar] (50)NULL ,
[ActionType] [varchar] (50) NULL ,
[Property] [varchar] (50) NULL ,
[OldValue] [varchar] (8000) NULL ,
[NewValue] [varchar] (8000) NULL ,
[ActionUserName] [varchar] (50) NOT NULL ,
[ActionDateTime] [datetime] NOT NULL
)
Once you have created the table, you'll need to add it to your custom LINQ class (which I will refer to as DboDataContext
), thus creating the TableHistory
class. Then, you'll need to add the History.cs file to your project.
You'll also want to add the following code to your project to get the system date:
public partial class DboDataContext
{
[Function(Name = "GetDate", IsComposable = true)]
public DateTime GetSystemDate()
{
MethodInfo mi = MethodBase.GetCurrentMethod() as MethodInfo;
return (DateTime)this.ExecuteMethodCall(this, mi, new object[] { }).ReturnValue;
}
}
private static Dictionary<type,> _cachedIL = new Dictionary<type,>();
public static T CloneObjectWithIL<t>(T myObject)
{
Delegate myExec = null;
if (!_cachedIL.TryGetValue(typeof(T), out myExec))
{
// Create ILGenerator
DynamicMethod dymMethod = new DynamicMethod("DoClone",
typeof(T), new Type[] { typeof(T) }, true);
ConstructorInfo cInfo = myObject.GetType().GetConstructor(new Type[] { });
ILGenerator generator = dymMethod.GetILGenerator();
LocalBuilder lbf = generator.DeclareLocal(typeof(T));
//lbf.SetLocalSymInfo("_temp");
generator.Emit(OpCodes.Newobj, cInfo);
generator.Emit(OpCodes.Stloc_0);
foreach (FieldInfo field in myObject.GetType().GetFields(
System.Reflection.BindingFlags.Instance |
System.Reflection.BindingFlags.Public |
System.Reflection.BindingFlags.NonPublic))
{
// Load the new object on the eval stack... (currently 1 item on eval stack)
generator.Emit(OpCodes.Ldloc_0);
// Load initial object (parameter) (currently 2 items on eval stack)
generator.Emit(OpCodes.Ldarg_0);
// Replace value by field value (still currently 2 items on eval stack)
generator.Emit(OpCodes.Ldfld, field);
// Store the value of the top on the eval stack into
// the object underneath that value on the value stack.
// (0 items on eval stack)
generator.Emit(OpCodes.Stfld, field);
}
// Load new constructed obj on eval stack -> 1 item on stack
generator.Emit(OpCodes.Ldloc_0);
// Return constructed object. --> 0 items on stack
generator.Emit(OpCodes.Ret);
myExec = dymMethod.CreateDelegate(typeof(Func<t,>));
_cachedIL.Add(typeof(T), myExec);
}
return ((Func<t,>)myExec)(myObject);
}
I got both of the above methods off of the net somewhere (maybe even from CodeProject), but it's been long enough that I can't recall where I got them.
Explanation of the History Class
The History
class records changes by creating a TableHistory
record, inserting the values for the primary key for the table being modified into the Key1, Key2, ..., Key6 columns (if you have more than 6 values that make up a primary key on any table, you'll want to modify this), setting the type of change being made in the ActionType
column (INSERT
, UPDATE
, or DELETE
), old value and new value if it happens to be an update action, and the date and Windows identity of the user who made the change.
Let's examine what happens when a call is made to the RecordLinqInsert
method:
public static void RecordLinqInsert(DboDataContext dbo, IIdentity user, object obj)
{
TableHistory hist = NewHistoryRecord(obj);
hist.ActionType = "INSERT";
hist.ActionUserName = user.Name;
hist.ActionDateTime = dbo.GetSystemDate();
dbo.TableHistories.InsertOnSubmit(hist);
}
private static TableHistory NewHistoryRecord(object obj)
{
TableHistory hist = new TableHistory();
Type type = obj.GetType();
PropertyInfo[] keys;
if (historyRecordExceptions.ContainsKey(type))
{
keys = historyRecordExceptions[type].ToArray();
}
else
{
keys = type.GetProperties().Where(o => AttrIsPrimaryKey(o)).ToArray();
}
if (keys.Length > KeyMax)
throw new HistoryException("object has more than " + KeyMax.ToString() + " keys.");
for (int i = 1; i <= keys.Length; i++)
{
typeof(TableHistory)
.GetProperty("Key" + i.ToString())
.SetValue(hist, keys[i - 1].GetValue(obj, null).ToString(), null);
}
hist.TableName = type.Name;
return hist;
}
protected static bool AttrIsPrimaryKey(PropertyInfo pi)
{
var attrs =
from attr in pi.GetCustomAttributes(typeof(ColumnAttribute), true)
where ((ColumnAttribute)attr).IsPrimaryKey
select attr;
if (attrs != null && attrs.Count() > 0)
return true;
else
return false;
}
RecordLinqInsert
takes as input a data context which it will use to write to the database, the user, and the LINQ object to be recorded (a single object, for instance, a Customer or Order object if you're using AdventureWorks). It then calls the NewHistoryRecord
method, which uses LINQ to Objects in conjunction with the AttrIsPrimaryKey
method to pull all the primary key properties, set the Key1-KeyN properties of the TableHistory
object, and return the new TableHistory
object. The code would be called in an application, like so: Continue © Microsoft .NET Support Team or respective owner