One of the better capabilities of NHibernate that I have recently found was the ability to group together queries on the client and execute them using only a trip to the database. What I have recently found, is that, as of writing this article and using NHibernate version 3.0 Beta 1, only SQL Server and MySQL support this. I was a little shocked after writing my program using MultiQueries (HQL) pointing to a SQL Server 2008 database instance and pointing it to an Oracle 10g database using ODP.Net. Suddenly, I received an error stating that ‘The driver NHibernate.Driver.OracleDataClientDriver does not support multiple queries.’ Boom…I was dead in the water. Searching the internet I found very little this. However I did learn that there is a method derived from the NHibernate.DriverBase.cs class called SupportsMultipleQueries() and returns a bool. If this method is not in the NHibernate.Driver.*.cs and returns true, then the NHibernate database driver will not support MultiQuery or MultipleCriteria. It would have been possible to modify NHibernate myself, this is the beauty of opensource after all. However, I didn’t want my own version of NHibernate. Nor did I want to spend so many hours developing and testing NHibernate.
What I found is that Futures work, as far as I can tell, the same as MultiQuery and MultiCriteria. The beauty of Futures, I found, is that if the database SupportsMultipleQueries is true, then the queries will be combined together and executed, using only a single trip to the server. However if multiple queries is not supported, then queries will be executed using a server access per query. There is an automatic failover using Futures.
The positive of this is that I can still remain database independent. The last thing I wanted to do was put code in my program saying, if Oracle do this, if SQL Server do this, if DB2 do this, etc…which defeats the purpose of NHibernate, in my opinion.
So now instead of writing this for MultiCriteria:
[sourcecode language="csharp" padlinenumbers="true"]
IMultiCriteria mCriteria = session.CreateMultiCriteria()
.Add("criteria", criteria)
.Add("count", rowCount);
[/sourcecode]
or this for MultiQuery:
[sourcecode language="csharp"]
var mQuery = session.CreateMultiQuery()
.Add("results", results)
.Add("count", rowCount);
[/sourcecode]
I use this:
[sourcecode language="csharp"] results = HQLResults.Future-object-() totalCount = (int)rowCount.Value; [/sourcecode]
I have found that this works well. So until the support for MultiQuery and MultiCriteria is implemented into an NHibernate version, my solution for the fact that NHibernate MultiQuery and MultiCritiera are not supported in Oracle nor DB2 is to use Futures.