NHibernate Native SQL and DialectScope

Question

Hi,

I have two native SQL queries. One for Oracle and one for MSSql. I want to run them trough NHibernate. How do I find out what Db-engine I'm on? I know I can add dialect-scope in mapping files for <database-object />. Is there something similar for <sql-query />? Or should I put an if-loop in my code and try to figure out the dialect?

Thanks.

Answer 1

You can cast your NHibernate.ISessionFactory to a NHibernate.Impl.SessionFactoryImpl and query its Dialect property, which is an object of a class derived from NHibernate.Dialect.Dialect (such as MsSql2000Dialect or Oracle8iDialect).

Subsequent versions of a database (2000, 2005, 2008) are implemented by classes derived from the previous version (use the Object Browser to analyse NHibernate.dll)

Answer 2

If the SQL is the same for both queries, you can use the ADO.NET interfaces from an ISession. For example:

IDbConnection cxn = session.Connection;
using (IDbCommand cmd = cxn.CreateCommand)
{
    cmd.CommandText = "select * from schema.table";
    IDataReader rdr = cmd.ExecuteReader;
}

You can add parameters using cmd.CreateParameter.

Answer 3

This was never going to work: I need to parse the result differently according to the version of the database. An if-else on the dialect is the way to go here.

One other option would have been a Stored Procedure. Then I could use database-object and create one for each database. But I don't like stored procedures :)