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<User> 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:

[code:c#]

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();
}

[/code]

This is an imported post. It was imported from my old blog using an automated tool and may contain formatting errors and/or broken images.

Leave a Comment

avatar

5 responses

  1. Avatar for wiryadi adidharma
    wiryadi adidharma October 16th, 2007

    Thank you for your post.

    I was creating a test fixture. Using ADO.Net datareader to verify the NHibernate mapping behaviour.
    Using this method, I can now use the same transaction for ADO.NET and NHibernate.

    At the test setup I wrap the NHibernate and ADO.Net with the same transaction.
    The ADO.Net can now read uncommitted NHibernate action.
    At the test teardown I can now just rollback the transaction. No need to clean up the database,
    Sweet...

  2. Avatar for trendl
    trendl October 31st, 2007

    While this approach works, you still have to clear the session to get rid of the persistent objects stored in the NHibernate cache. Or am I wrong? I'm just beginning with NHibernate, so please correct me if necessary.

  3. Avatar for maartenba
    maartenba October 31st, 2007

    trendl, no 100% sure of that. In my case, I created a HttpModule in ASP.NET which creates and disposes the Session properly which mens no worry about manually clearing... Will have to go trough the NHibernate code though to chekc whether you have to clear the session yes or no in this particular case.

  4. Avatar for trendl
    trendl October 31st, 2007

    I'm writing a desktop application with embedded SqLite database and in order to avoid problems with dependent collections of persistent objects being called outside a session lifespan, I'm using a single static session throughout my application. When I delete an object using ADO.Net and try to create a new persistent object of the same type, I get an exception that an object with the same id 1 already exists. If I clear the session manually, it works fine.

  5. Avatar for maartenba
    maartenba October 31st, 2007

    Well, I suggest you clear your session then after a ADO.NET command. Winforms is a little different because your session can exist as long as your application runs.