Using nhibernate and oracle using DbProviderFactories

Question

In our system we are using nhibernate and Oracle. We have always a lot of deployment problems because the relationship between Oracle.DataAccess.dll and the Oracle client that is installed in our client's server is a mess.

Sometimes we can change the installed oracle client, sometimes not. Sometimes we need the 32bit version of the dll, sometimes the 64bit one. I wish I could change oracle for a friendlier database, but that's not an option for now.

One way to get rid of all this mess is to use DbProviderFactories, so I could have no reference for the Oracle.DataAccess.dll in my project and use whichever version is installed on the server.

How can I configure nhibernate to use a DbProviderFactory?

My configuration:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="dialect">NHibernate.Dialect.Oracle9iDialect</property>
      <property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
      <property name="connection.connection_string">Data Source=XE; User Id=*****; Password=******;</property>
      <property name="current_session_context_class">NHibernate.Context.ManagedWebSessionContext, NHibernate</property>
      <property name="sql_exception_converter">Way2Pim.Data.NHibernate.SqlExceptions.SqlExceptionConverter, Way2Pim.Data</property>
      <property name="show_sql">true</property>
      <property name="hbm2ddl.keywords">none</property>
      <mapping assembly="MyAssembly" />
    </session-factory>
  </hibernate-configuration>

Answer 1

NHibernate already uses the dbproviderfactory if loading per reflection fails. see NHibernate.Driver.ReflectionBasedDriver

this feature is available since

Revision: bb904343e18b693f6d1e13b357c305fdd46ff5ee
Author: Fabio Maulo
Date: 14.08.2010 19:37:12
Message:
Refactoring of ReflectionBasedDriver in order to use DbProviderFactories

Answer 2

Got it!

Nhibernate has a bug concerning Oracle and DBProviderFactories. If you look at OracleDataClientDriver class, it derives from ReflectionBasedDriver.

In it's constructor, ReflectionBasedDriver loads the Oracle.DataAccess provider from DbProviderFactory correctly. After that, the OracleDataClientDriver constructor tries to get the OracleCommand type from the loaded assemblies, which of course will result in null (the Oracle.DataAccess is not in memory yet).

A quick fix is to create your own Driver copying everything from OracleDataClientDriver, but replacing all lines of its constructor with these:

System.Type oracleCommandType = base.CreateCommand().GetType();
oracleCommandBindByName = oracleCommandType.GetProperty("BindByName");

System.Type parameterType = oracleCommandType.Assembly.GetType("Oracle.DataAccess.Client.OracleParameter");
oracleDbType = parameterType.GetProperty("OracleDbType");

System.Type oracleDbTypeEnum = oracleCommandType.Assembly.GetType("Oracle.DataAccess.Client.OracleDbType");
oracleDbTypeRefCursor = System.Enum.Parse(oracleDbTypeEnum, "RefCursor");

And that's it, no Oracle.DataAccess.dll in your project anymore :)