Getting Ado Connection From Nhibernate

I had a problem where I needed to use the sqlBulk to insert multiple records from a data table to database. Now we are using NHibernate but sometimes don't use it or the NHibernate entities. Instead, sometime we use Sql directly, either in a new ado connection or by using the NHibernate session.

Problem
We had a module which was using a mixture of some read data which was done using the NHibernate session, and some bulk inserts which were done in a new ado connection. Obviously that was a bug, so I thought of fixing it to use NHibernate throughout.

Options

Use the ado connection do everything in one transaction and commit.

Use NHibernate session and do everything in that, so that if this module is called from another place it could use the same session.

Analysis

Now as you can see from option 2, you can easily spot if another module is calling this module using its own session (say NHibernate), and the ado won't respect that and it will open a new connection. If the caller module is not using NHibernate then it should inject the connection down the line.

Solution

I am not saying that I picked the best solution but I decided to use NHibernate session and sqlBulk using connections from NHibernate connections. So, all the things went smoothly until I hit two walls

1. A piece of code using an ado connection and a transaction
2. Sql bulk copy

So for the first one I need a connection from NHibernate Session and a transaction. To get ado connection from NHibernate session this helped me

var reliableConnection = (ReliableSqlDbConnection)yourUnitOfWorkOject.Session().Connection;
var adoConnFromSession = reliableConnection.ReliableConnection.Current;

And it’s perfectly ok if you don’t want any transient error handling but if you do want that then this won’t give you any as by using the connection this way you sacrificed the transient handling. But I fixed that using this code

_retryPolicy.ExecuteAction(() => {
    tryPersist(youObject);
});

Off course you have to define SqlAzureTransientErrorDetectionStrategyWithTimeout yourself.

The other sub problem was to get a transaction. Now you can’t have a beginTransaction on the connection, as you can’t create parallel transaction using the NHibernate connection. So what you have to do is enlist your command in ITransaction from the session so this should work like this

using(var cmd = new SqlCommand("select * from table", adoConnFromSession, null)) {
    yourCurrentUnitOfWork.Session().Transaction.Enlist(command);
    using(var reader = command.ExecuteReader()) {
        Other code
    }
}

I used the same strategy in the bulk which was like this before my fix

using(var cmd = new SqlCommand()) {
    using(var trans = adoCon.BeginTransastion()) {
        using(var bulk = new SqlBulkCopy(adoCon, SqlBulkCopyOptions.Default, trans)) {
            bulk.DestinationTableName = "tableName";
            bulk.WriteToServer(dt);
        }
        trans.Commit();
    }
}
}

And after my fix it looked like this

using(var cmd = new SqlCommand()) {
    youtUnitOfWork.Session().Transaction.Enlist(cmd);
    using(var bulk = new SqlBulkCopy(adoConnFromSession, SqlBulkCopyOptions.Default, cmd.Transaction)) {
        bulk.DestinationTableName = "tableName";
        bulk.WriteToServer(dt);
    }
}