Enlisting an ADO.NET command in an NHibernate transaction

For everyone who has read my article on NHibernate, here’s a story for you…

When building an application, everyone comes to a point where one needs to batch-update records in a database table, based on one or more criteria. Let’s say, for example, there’s a table “User” containing an activation date. And you want to remove all users that have activated in 1999. In a regular database environment, or when using ADO coding, one would write a DbCommand “DELETE FROM User WHERE activationdate < ‘2000-01-01’”.

This can also be done using NHibernate, by fetching an IList from your database, and calling session.Delete(user); for each user in the list. Another idea is to use a HQL query: session.Delete(“from User u where u.ActivationDate < ‘2000-01-01’”); A good thing about NHibernate is that it supports caching of data, but for this batch-delete purpose, it sucks. NHibernate will, in both previous cases, fetch all affected data, map it to objects, store it in first-level cache, … Overhead galore!

Luckily, I saw a blog post on this by jlockwood. He simply tells to enlist a regular SQL statement in a NHibernate transaction, and you’re ready to go. His code isn’t provider-independent, so here’s an improved version:

ISession session = sessionFactory.GetSession();
using(ITransaction transaction = session.BeginTransaction())
{
    IDbCommand command = session.Connection.CreateCommand();
    command.Connection = session.Connection;
    transaction.Enlist(command);
    command.CommandText = "delete from User where activationdate < '2000-01-01'";
    command.ExecuteNonQuery();
    transaction.Commit();
}