Connecting NHibernate to Oracle Database

Overview

In my previous blog - NHibernate Setup, I shared my experience on setting up NHibernate with ASP.NET and SQL Server 2005 Database. Now I would like to talk about my experience on connecting NHibernate to Oracle Database.

Reference

1. As long as ODP.NET is under the hood - NHibernate
2. NHibernate & Spring.NET with Oracle
3. NHibernate and Oracle Part 1 - using an external configuration file

About the Oracle Client Driver

There are two types of commonly used Oracle Client Driver: Microsoft's .NET driver & Oracle's own .NET driver. Using Microsoft's .NET driver in NHibernate is very very simple, but according to Reference 2 talking about NHibernate documentation, this driver does not handle long character strings correctly.
In the following blog, I will show the configuration for BOTH Drivers.

Steps - Microsoft's .NET driver

1. Install "Oracle Client", you can download it at "http://www.oracle.com/technology/software/index.html" but registration required. I use Client 10g.
2. Replace the original "hibernate-configuration" section in "web.config" with the following XML:
   

   <session-factory>

         <property name="dialect">NHibernate.Dialect.Oracle9Dialect</property>

         <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>

         <property name="hibernate.connection.driver_class">NHibernate.Driver.OracleClientDriver</property>

         <property name="connection.connection_string">Data source=DATASOURCE;User Id=ID;Password=PASS;</property>

       </session-factory>

3. That's it!!! One thing to note is that in the connection string "Data source" is defined in "Net Manager" of Oracle Client. You can also find that in "$ORACLE_HOME\NETWORK\ADMIN\tnsnames.ora". $ORACLE_HOME is the installation folder of your Oracle Client, mine is "C:\oracle\product\10.2.0\client_1".

Steps - Oracle's .NET driver

1. Install "Oracle Client", you can download it at "http://www.oracle.com/technology/software/index.html" but registration required. I use Client 10g.
2. Set Environment Variable "ORACLE_HOME" to installation folder of your Oracle Client, mine is "C:\oracle\product\10.2.0\client_1".
3. Make sure that "Oracle.DataAccess" is available in your Global Assembly Cache (GAC). You can view your GAC at "Administrative Tools" => ".NET Framework 2.0"
4. In "web.config", add reference to "Oracle.DataAccess" by adding "qualifyAssembly" section in "assemblyBinding" section:
   

   <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

        <qualifyAssembly partialName="Oracle.DataAccess" fullName="Oracle.DataAccess, Version=10.2.0.100, Culture=neutral, PublicKeyToken=89b483f429c47342" />

       </assemblyBinding>

5. Replace the original "hibernate-configuration" section in "web.config" with the following XML:
   

   <session-factory>

         <property name="dialect">NHibernate.Dialect.Oracle9Dialect</property>

         <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>

         <property name="hibernate.connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>

         <property name="connection.connection_string">Data source=DATASOURCE;User Id=ID;Password=PASS;</property>

       </session-factory>

   
   Note that the "driver_class" is "OracleDataClientDriver" instead of "OracleClientDriver".