NHibernate and Oracle Database via dotConnect

NHibernate is an ORM solution moved from Java to Microsoft .NET. It allows mapping application objects to relational database. NHibernate automatically generates SQL code for loading and saving objects. One of the approaches of NHibernate usage is entity mapping XML definition.

In this article we will talk about interaction of NHibernate with Oracle 11g. To establish connection to this database we will use the Devart dotConnect for Oracle provider built on ADO.NET technology to develop Oracle-based database applications.

Create a table named Category in your database before creating an NHibernate application. Use the following script to do this:

CREATE TABLE CATEGORY (
  ID NUMBER,
  NAME VARCHAR2(20),
  CONSTRAINT PK_CATEGORIES PRIMARY KEY (ID)
)

Create a new ASP.NET Web Application and call it “QuickStart”. Perform the following steps to use NHibernate with it:

1. Add the NHibernate.dll reference to your application.
2. Don’t forget to add the Devart.Data.Oracle assembly to your project.
3. To include NHibernate to your application you have to configure it. You can do this with the help of the web configuration file. To do this, perform the following steps:
   1. Add the hibernate configuration section definition to configSections in your web configuration file:

      <configSections>
             <section name="hibernate-configuration"
                      type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
      </configSections>

      Please note that it should be a single section, don’t define it inside the SectionGroup block.

   2. Add the hibernate-configuration section implementation to your web configuration file. Insert this section after the previous section definition and before other configuration sections.

      <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
      <session-factory>
         <property name="dialect">
            NHibernate.Dialect.Oracle10gDialect
         </property>
         <property name="connection.provider">
            NHibernate.Connection.DriverConnectionProvider</property>
         <property name="connection.driver_class">
            NHibernate.Driver.DevartOracleDriver, QuickStart
         </property>
         <property name="connection.connection_string">
           server=db;user id=scott;password=tiger;SID=ORCL1110;
           port=1522;Direct=True;
         </property>
         <property name="hbm2ddl.keywords">
           none</property>
         <mapping assembly="QuickStart"/>
      </session-factory>
      </hibernate-configuration>

      This section was described in the previous step. Here you can define data provider, connection string etc. NHibernate uses these settings to connect to the database.

      We are using the Direct mode to connect to the database, but you can use the OCI mode, too. For this, just change connectiong string.

4. Add the following class to your project:

   using System.Data;
         using NHibernate.AdoNet;
         using NHibernate.SqlTypes;
         using Devart.Data.Oracle;
   
         namespace NHibernate.Driver
         {
         public class DevartOracleDriver: ReflectionBasedDriver, IEmbeddedBatcherFactoryProvider
         {
         private static readonly SqlType GuidSqlType = new SqlType(DbType.Binary, 16);
   
         public DevartOracleDriver()
   			: base("Devart.Data.Oracle",
   			"Devart.Data.Oracle.NHibernate.NHibernateOracleConnection",
   			"Devart.Data.Oracle.NHibernate.NHibernateOracleCommand")
         {
         }
   
         public override bool UseNamedPrefixInSql
         {
   			get { return true; }
         }
   
         public override bool UseNamedPrefixInParameter
         {
   			get { return false; }
         }
   
         public override string NamedPrefix
         {
   			get { return ":"; }
         }
   
         protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
         {
   			if (sqlType.DbType == DbType.Guid)
   			{
         base.InitializeParameter(dbParam, name, GuidSqlType);
         OracleParameter oraParam = (OracleParameter)dbParam;
         oraParam.OracleDbType = OracleDbType.Raw;
   			}
   			else
   			{
         base.InitializeParameter(dbParam, name, sqlType);
   			}
         }
   
         #region IEmbeddedBatcherFactoryProvider Members
   
         System.Type IEmbeddedBatcherFactoryProvider.BatcherFactoryClass
         {
   			get { return typeof (OracleDataClientBatchingBatcherFactory); }
         }
   
         #endregion
         }
       }

   We have created the class mentioned above to implement interaction with Oracle 11g server. This class is a descendant of the ReflectionBaseDriver base class. We’ve defined an assembly name in the class constructor and overridden some methods. This approach is the simplest and the most useful for provider implementation.

5. Add the qualifyAssembly node to the assemblyBinding section, which is within the runtime section. It describes the Devart assembly.

     <runtime>
       <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         <qualifyAssembly partialName="Devart.Data.Oracle" fullName="Devart.Data.Oracle,Version=7.5.179.0, Culture=neutral, PublicKeyToken=09af7300eec23701"/>
       </assemblyBinding>
     </runtime>

   Go to GAC (%Windir%assembly) and find the Devart.Data.Oracle assembly. Right click on it and select Properties. This is how you can receive PublicKeyToken. Substitute values of Version and PublicKeyToken with the values from your GAC.

6. Create an hbm file which describes mapping rules for your application. To do this right-click your project in Solution Explorer and choose Add New Item. Add a new xml file and name it “Cat.hbm.xml”.For example, we have a Category table in our database. It consists of two columns – ID and Name. You can see an example of a mappling file below

   <?xml version="1.0" encoding="utf-8" ?>
   <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
       namespace="QuickStart" assembly="QuickStart">
     <class name="Category" table="Category">
       <id name="ID">
         <column name="ID" sql-type="int" not-null="true"/>
       </id>
       <property name="Name">
         <column name="Name" length="16" not-null="true" />
       </property>
     </class>
   </hibernate-mapping>

   Mark this file as Embedded Resource in its Build Action property.

7. Create a Category class corresponding to the Category table in the database. It is shown below:

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Web;
   
   namespace QuickStart
   {
       public class Category
       {
           private int id;
           private string name;
   
           public virtual int ID
           {
               get { return id; }
               set { id = value; }
           }
   
           public virtual string Name
           {
               get { return name; }
               set { name = value; }
           }
   
           public Category()
           {
           }
   
           public Category(int catid, string catname)
           {
               id = catid;
               name = catname;
           }
       }
   }

Now you can use NHibernate to work with your datasource. Create an NHibernateUtils class with the GetCurrentSession method in your application. It is used to create a session. The code of this method is shown below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NHibernate;
using NHibernate.Cfg;

public class NHibernateUtils
{
      private const string CurrentSessionKey = "nhibernate.current_session";
      private static readonly ISessionFactory sessionFactory
      = new Configuration().Configure().BuildSessionFactory();

      public static ISession GetCurrentSession()
      {
          HttpContext context = HttpContext.Current;
          ISession currentSession = context.Items[CurrentSessionKey] as ISession;

          if (currentSession == null)
          {
              currentSession = sessionFactory.OpenSession();
              context.Items[CurrentSessionKey] = currentSession;
          }

          return currentSession;
      }
}

Use the following code to add records to the Category table:

//Create a session object
ISession session = NHibernateUtils.GetCurrentSession();

//Create transaction object
using (ITransaction tx = session.BeginTransaction())
{
   //Create first record
   Category catMag = new Category();
   catMag.Name = "Magazines";
   catMag.ID = 10;

   session.Save(catMag);

   //Create second record
   Category catBooks = new Category();
   catBooks.Name = "Books";
   catBooks.ID = 20;

   session.Save(catBooks);

   //Commit transaction
   tx.Commit();
}

The Category table contains two records – “Books” and “Magazines”. Use the following code to extract data from this table:

     IQuery query = session.CreateQuery("from Category i");
     IList results = query.List();

You can use the recordset from the Categories table now. It is presented as a List container. You can use it with grids or create your own data structure.

To update a record use the following statement:

      ISession session = NHibernateUtils.GetCurrentSession();

      using (ITransaction tx = session.BeginTransaction())
      {
           IQuery query = session.CreateQuery("from Category i where i.Name = 'Books'");
           IList res = query.List();

           Category category= (Category) res[0];

           category.Name = "Newspapers";

           session.Save(category);

           tx.Commit();
       }

We have changed some fields of the Category class instance and called the Save method of the session after that.

To delete a record you can use the Delete method of the session instance.

      ISession session = NHibernateUtils.GetCurrentSession();

      using (ITransaction tx = session.BeginTransaction())
      {
          IQuery query = session.CreateQuery("from Category i where i.Name = 'Newspapers'");
          IList res = query.List();

          Category category= (Category) res[0];

          session.Delete(category);

          tx.Commit();
      }

NHibernate can be set up to work with any database via Devart data providers.