Friday 29 June 2012

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!

No comments:

Post a Comment