Using NHibernate transaction in SqlBulkCopy

Question

I'm storing some data using NHibernate, and I need to insert huge amount of data as a part of this action - i.e. in the same transaction. Code looks like this:

using (ISession session = NHibernateHelper.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
    session.SaveOrUpdate(something);
    // ...


    SqlBulkCopy bulkCopy = new SqlBulkCopy(
    (SqlConnection)session.Connection,
    SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers,
    ???transaction???
    );
    //...

    transaction.Commit();
}

I know that I could use TransactionScope or do it otherwise. But I insist on this pattern. Let's pretend that for the sake of independent DB access (if I extract and inject arbitrary bulk insert operation). Is there a way how to get SqlTransaction instance out of NHibernate.ITransaction?

Thanks

Answer 1

Unsurprisingly, Ayende tackled this one as well, but it's pretty grody.

The gist of it is that you know you can enlist normal ADO.NET IDbCommand instances in the NHibernate transaction, like so:

var cmd = new SqlCommand ();
if (session.Transaction != null && session.Transaction.IsActive)
    session.Transaction.Enlist (cmd);

But SqlBulkCopy isn't an IDbCommand, and that particular constructor requires a SqlTransaction (so you've already skipped the boat on provider-independence anyways). So cheat -- your example might look something like this:

using (var session = NHibernateHelper.OpenSession ())
using (var transaction = session.BeginTransaction ()) {
    using (var cmd = new SqlCommand ()) {
        transaction.Enlist (cmd);

        var bulk = new SqlBulkCopy ((SqlConnection)session.Connection,
                                    SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers,
                                    (SqlTransaction)cmd.Transaction);
    }
    // ...
    transaction.Commit ();
}

You'll undoubtedly want some error-checking, safe casts, etc. in there. I'm not aware of a more modern/less scary way to do this, unfortunately (even to get an IDbTransaction from an ITransaction).