Using NHibernate transaction in SqlBulkCopy

ado.net, c#, nhibernate, sql-server, transactions

Solution

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`).

Problem

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

Original source