I recently began the undertaking of converting the linq 2 sql data access layer of an application of mine to nhibernate (prior to linq 2 sql it was hand crafted sql through ado.net). I’m using fluentnhibernate to define my mappings. Part of the conversion is converting the auditing.
My existing auditing table looks looks like the following:
CREATE TABLE [dbo].[Audit](
[AuditId] [int] IDENTITY(1,1) NOT NULL,
[AuditDate] [datetime] NOT NULL,
[User] [nvarchar](30) NOT NULL,
[TableName] [nvarchar](60) NOT NULL,
[FieldName] [nvarchar](60) NOT NULL,
[KeyId] [int] NOT NULL,
[OldValue] [nvarchar](80) NULL,
[NewValue] [nvarchar](80) NULL
)
plus the normal indexes and constraints. This code is in production so I don’t want to change the schema. The basics of it is I log every field change, logging the date and time the field changed and the old and new values.
To achieve this in linq 2 sql I was subscribing to the SubmitChanges event on the DataContext and I registered each linq 2 sql entity of interest to auditing.
public override void SubmitChanges(ConflictMode failureMode)
{
try
{
this.Audit<Contract>(c => c.ContractId);
this.Audit<Account>(a => a.AccountId);
this.Audit<Company>(c => c.CompanyId);
this.Audit<Contact>(c => c.ContactId);
this.Audit<Network>(n => n.NetworkId);
this.Audit<NetworkTariff>(nt => nt.NetworkTariffId);
this.Audit<Plan>(p => p.PlanId);
this.Audit<Tariff>(t => t.TariffId);
this.Audit<Agent>(a => a.AgentId);
base.SubmitChanges(failureMode);
}
catch (Exception ex)
{
LoggingUtility.LogException(ex);
throw;
}
}
I also had my own AuditLogger class, where the Audit<TEntity>() method did the heavy lifting, finding the properties that were updated, getting their old and new values and persisting to the audit table (you can still see some of the old ado.net code in there!)
public static class AuditLoggingUtility
{
static readonly SqlConnection cn = new SqlConnection(ConfigItems.ConnectionString);
static readonly SqlCommand cmd;
static AuditLoggingUtility()
{
cmd = cn.CreateCommand();
cmd.CommandText = "INSERT INTO Audit (AuditDate, [User], TableName, FieldName, KeyId, OldValue, NewValue) VALUES (@AuditDate, @User, @TableName, @FieldName, @KeyId, @OldValue, @NewValue)";
cmd.Parameters.Add(new SqlParameter("@AuditDate", SqlDbType.DateTime));
cmd.Parameters.Add(new SqlParameter("@User", SqlDbType.NVarChar, 30));
cmd.Parameters.Add(new SqlParameter("@TableName", SqlDbType.NVarChar, 60));
cmd.Parameters.Add(new SqlParameter("@FieldName", SqlDbType.NVarChar, 60));
cmd.Parameters.Add(new SqlParameter("@KeyId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@OldValue", SqlDbType.NVarChar, 80));
cmd.Parameters.Add(new SqlParameter("@NewValue", SqlDbType.NVarChar, 80));
cmd.CommandType = CommandType.Text;
}
public static bool WriteAuditLog(string user, string tableName, string fieldName, int keyId, string oldValue, string newValue)
{
cmd.Parameters["@AuditDate"].Value = DateTime.Now;
cmd.Parameters["@User"].Value = user;
cmd.Parameters["@TableName"].Value = tableName;
cmd.Parameters["@FieldName"].Value = fieldName;
cmd.Parameters["@KeyId"].Value = keyId;
cmd.Parameters["@OldValue"].Value = oldValue;
cmd.Parameters["@NewValue"].Value = newValue;
var rows = 0;
try
{
cn.Open();
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.Message);
}
finally
{
cn.Close();
}
return rows == 1;
}
}
public static class AuditExtensions
{
public static bool HasAttribute(this Type t, Type attrType)
{
return t.GetCustomAttributes(attrType, true) != null;
}
public static bool HasAttribute(this PropertyInfo pi, Type attrType)
{
return pi.GetCustomAttributes(attrType, false) != null;
}
private static string GetPropertyValue(PropertyInfo pi, object input)
{
var tmp = pi.GetValue(input, null);
return (tmp == null) ? string.Empty : tmp.ToString();
}
private static string GetPropertyValue(object input)
{
return (input == null) ? "<null>" : input.ToString();
}
public static void Audit<TEntity>(this DataContext dc, Func<TEntity, int> tableKeySelector) where TEntity : class
{
var table = dc.GetTable<TEntity>();
string tableName = dc.Mapping.GetTable(typeof(TEntity)).TableName;
// Remove the 'dbo.' prefix
if (tableName.IndexOf("dbo.") == 0)
{
tableName = tableName.Substring(4);
}
// Audit the items that have been modified.
var updates = dc.GetChangeSet().Updates.OfType<TEntity>();
foreach (var item in updates)
{
var key = tableKeySelector.Invoke(item);
var mmi = table.GetModifiedMembers(item);
foreach (var mi in mmi)
{
var user = System.Threading.Thread.CurrentPrincipal.Identity.Name;
AuditLoggingUtility.WriteAuditLog(user, tableName, mi.Member.Name, key, GetPropertyValue(mi.OriginalValue), GetPropertyValue(mi.CurrentValue));
}
}
}
}
I figured Nhibernate should have an equivalent, and after a little reading, it turns out I was right. Nhibernate 2.0 has events, and the IPreUpdateEventListener is the interface of interest. Nhforge explains audit logging where the auditing is happening in the same table. Not quite what I want. Check out the helpful article on that site or the background information on this event and how it works.
My class is based on what I read in that article:
public class AuditEventListener : IPreUpdateEventListener
{
public bool OnPreUpdate(PreUpdateEvent @event)
{
var entity = @event.Entity as EntityBase;
if (entity == null) return false;
for (var index = 0; index < @event.State.Length; index++) {
if (@event.State[index] != @event.OldState[index]) {
var audit = new Audit(Thread.CurrentPrincipal.Identity.Name, entity.GetEntityName(),
@event.Persister.PropertyNames[index], entity.Id,
@event.OldState[index].ToString(), @event.State[index].ToString());
@event.Session.Save(audit);
}
}
return false;
}
}
What I am doing is iterating through the State and OldState properties and writting to the Audit table where they’re different. It’s nice how the two different ORM’s are similar. Though there’s not a lot that should be different.
The other main step is letting the Nhibernate configuration know about the event. For that we need to call SetListener on the Configuration object. Note I am using FluentNhibernate and my configuration below is for my unit test assembly which uses SQLite
private static ISessionFactory CreateSessionFactory()
{
return Fluently.Configure()
.Database(SQLiteConfiguration.Standard.InMemory().ShowSql())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<CompanyMap>())
.ExposeConfiguration(cfg => { cfg.SetListener(ListenerType.PreUpdate, new AuditEventListener()); _configuration = cfg; })
.BuildSessionFactory();
}
There is a difference between the two strategies though. Where Linq 2 Sql logs changes to the database tables, Nhibernate logs changes to the entities. Take the follow entity as example
public class Company : EntityBase
{
public Company()
{
Address = Address.Empty;
PostalAddress = Address.Empty;
}
public virtual string CompanyName { get; set; }
public virtual string ABN { get; set; }
public virtual string ACN { get; set; }
public virtual Address Address { get; set; }
public virtual Address PostalAddress { get; set; }
public virtual MasterAccount CreateAccount()
{
return new MasterAccount(this);
}
public override System.Collections.Generic.IEnumerable GetRuleViolations()
{
if (string.IsNullOrEmpty(CompanyName))
yield return new RuleViolation("Company Name required", "CompanyName");
// TODO check for valid ABN and ACN.
yield break;
}
}
The linq 2 sql method will save the individual fields, whereas the Nhibernate method will save the entire component value. Basically I think the nhibernate method needs more work to handle many to many relationships and components. etc.
