Using NHibernate- ADO Connection

So I had a problem where I need to use the sqlBulk to insert multiple records from a data table to database. Now we are using NHibernate but sometime we don’t always use it or not truly use the NHibernate entities instead sometime we use Sqls directly either in new ado connection or using the NHibernate session.

Problem: So we had a module which was using a mixture of both some read data was done using the NHibernate session and some bulk inserts 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 that the if another module is calling this module using its own session (say NHibernate) the ado wont respect that and it will open a new connection. If the caller module is not using NHibernate than it should inject the connection down the line.

Solution:

I am not saying that picked up the best solution but I decided to use NHibernate session and sqlBulk using connection form NHibernate connection. So, all the things went smooth 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 your self.

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