Sql query from NHibernate criteria

Let me first state the requirement clearly. I need to get the Sql query from a NHibernate criteria, which will be fired when the criteria is executed. However, I need to get the sql query without executing the criteria. This was required not for logging the query or debugging any issues.


The specific requirement in my case was to create the criteria based on all the business rules, get the sql query from it (without actually executing the criteria), and use the query as an ADO command and avoid NHibernate, for performance reasons. This does seem a little weird to me though, I must admit. 


The advantage of this approach is that, if achieved, it would help avoid the need for writing a sql parser of our own based on the business rules. The criteria would help us in that regard. By executing the query as an ADO command instead of an NHibernate criteria, will result in performance gains in certain cases. My requirement was to use it while performing data export from tables.


I started of with a simple criteria, and using the criteria walker approach was able to get the sql query from the criteria without actually executing it. My joy was shortlived though, as when I moved towards more complex criteria, with multiple conditions, it started to fail. It was still spitting out the sql query, but without the various parameters, which were substituted by a '?' sign. Another problem  was SetMaxResults(N) was not working. I discarded this approach and started looking at other possible solutions for the same. 


The second approach I though of was to use NHibernate Interceptors. The idea was to get the sql statement from the OnPrepareStatement(SqlString sql) method, and then not go ahead with the transaction. This approach also faced the same problem as the sql statement has the parameters missing. Again.


I also flirted with the idea of executing the criteria against a fake IDbConnection for a while. However, somehow the whole idea seemed a little weird, and I did not go ahead with this approach.


The entire episode was a futile attempt at getting the sql from the criteria. The problem appeared to be the sql parameters, which do not appear in the query. The cause for this is that the parameters are added all over the place. So, for a very simple criteria, whose query will not be parametrized, these approaches might work. However, for the more generic cases, these approaches did not work!

Transaction not connected, or was disconnected error

I recently came across this rather bizarre error. All I needed to do for my work was to import some files into my system. The import process used bulk insert along with some other processing, all inside a transaction.

When the bulk insert is successful, it works great. However, when the bulk insert fails, it results in "Transaction is not connected, or was disconnected" error, when the transaction is rolled back.

The stacktrace for the error is:-

 at NHibernate.Transaction.AdoTransaction.CheckNotZombied() at NHibernate.Transaction.AdoTransaction.Rollback() 

This in itself was not too helpful. However, when I had a look at the NHibernate source code, I had a better idea of what was going on beneath the hood. The definition for the CheckNotZombied() method reveals the clue.

private void CheckNotZombied()
{
if (trans != null && trans.Connection == null)
{
throw new TransactionException("Transaction not connected,                                                or was disconnected");
}
}


Proceeding further, the CheckNotZombied() method is actually called from a couple of places, Committ() and Rollback().
The code for either of these two looks like this :-
                                
                                //do stuff
                                CheckNotDisposed();
CheckBegun();
CheckNotZombied();
                                //do stuff


So, it looked like when the bulk insert query fails, it closes the transaction's connection, which resulted not only this error, but also the start of my very own blog).
A little googling revelaed that bulk insert is actually not transactional, and that sql server by itself will roll back the batches,as mentioned here. 
The other important point to notice is that when an exception occurs in the bulk insert part, sql server closes the connection. Interestingly, if you decide to catch the exception, and for some reason decide to go ahead with the next bulk insert, sql server will use implicit transactions for that.