NHibernate with Oracle: odp.net

Introduction

On this tutorial, I will explain in a step by step way (with screen shots), how to connect and create simple ASP.NET application with NHibernate and Oracle using ODP.NET connector.

Software and version used

I used Oracle database server version 11 R2 with ODP.NET version 11.2.0.1.2.
l used the oe (Order Entry) sample schema that oracle provides as part of the installation. Make sure you have this schema installed.
Visual Studio 2010 (Code in c#) with NHibernate version 2.1.2.

Step by step instruction

Step 1 - Creating Web Site solution

There are many articles on the differences between web site solution and web application solution. My example here is based on web site solution in c# stored on file system:

You need to create new empty web site solution (c#) and call it MyFirstNhbOraSite:

Step 2 - Configuring web.config

The solution you just created has only one file, which is web.config, double click on it to open the file. The first thing to do is to declare on hibernate configuration section, so we need to add the following lines right after the configuration opening tag:

The next thing is add the hibernate-configuration section, which the core configuration part for NHibernate:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="connection.provider">
        NHibernate.Connection.DriverConnectionProvider
      </property>
      <property name="dialect">
        NHibernate.Dialect.Oracle10gDialect
      </property>
      <property name="connection.driver_class">
        NHibernate.Driver.OracleDataClientDriver
      </property>
      <property name="connection.connection_string">
        User Id=oe;
        Password=oe;
        Data Source=YOUR_TNS;
        Pooling=true;
        Enlist=false;
        Statement Cache Size=50;
        Min Pool Size=10;
        Incr Pool Size=5;
        Decr Pool Size=2;
      </property>
      <property name="show_sql">
        true
      </property>
      <property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
      <mapping assembly="NhbHelper"/>
    </session-factory>
</hibernate-configuration>

I am not going into details for each line, but I will briefly go over important notes.
When we use dialect: NHibernate.Dialect.Oracle10gDialect without setting the connection.driver_class, NHibernate will use Microsoft provider for Oracle. If we want to use ODP.NET we must set the connection.driver_class to NHibernate.Driver.OracleDataClientDriver in order to set NHibernate to work with ODP.NET.
On the connection string you need to set your TNS name instead of YOUR_TNS. Also make sure that the user and password for the OE schema are correct.
The property of proxyfactory.factory_class is now (version 2.1.2) mandatory, and must be specified in the config file. This is property is reference for bytecode provider.
The last property is mapping assembly. Because the web site has dynamic assembly name we must store the NHibernate mapping and classes in different class library project. This project will have NhbHelper assembly name, but you can use any name you like.

Step 3 - Creating the NhbHelper class library

Since we created a web site solution we can not use it to store all classes and mapping files of NHiberante, because we need to have steady assembly name, and web sites does not have steady assembly name.
So we right click on the solution and choose Add->New Project:

We will pick Visual C# from the left tree and on the right we will choose Class Library and in the Name text box we will write NhbHelper:

Step 4 - Adding refrences in the NhbHelper project

Now we need to add reference to the NHibernate.dll and to the ByteCode.LinFu.Since we also use HttpContext object, we also need to add reference to the System.Web namespace. Last reference we need to add is to the Oracle data provider (Oracle.DataAccess.dll) in order to use ODP.Net.
We do it on the new NhbHelper project we just created. So we right click on the References folder and we choose Add Reference:

We will pick the Browse tab and browse the NHibernate folder and choose the NHibernate.dll:

We have to repeat this again, but this time we need to add reference to NHibernate.ByteCode.LinFu.dll:

Now we need to add reference to the System.Web namespace from the .NET tab:

Last reference we need to add is to the oracle ODP Oracle.DataAccess.dll file. The file is located in the Oracle home folder at ORACLE_HOME\odp.net\bin\4

Step 5 - Creating the NHibernateHelper class

On this step we will create generic class for handling ISession and ISenssionFactory interfaces. I will not going to dive on how it works.
Also, this class is simply copy & paste from the official reference of NHibernate that can be found in http://nhforge.org/doc/nh/en/index.html#quickstart-playingwithcats
So first we rename the Class1.cs to NHibernateHelper.cs (Press Yes when asked to rename references in the code on the project):

Now, as I already mentioned, the class implementation is identical implementation to the NHiberante reference documentation:

using System;
using System.Web;
using NHibernate;
using NHibernate.Cfg;

namespace NhbHelper
{
    public sealed class NHibernateHelper
    {
        private const string CurrentSessionKey = "nhibernate.current_session";
        private static readonly ISessionFactory sessionFactory;

        static NHibernateHelper()
        {
            var configuration = new Configuration();
            configuration.AddAssembly("NhbHelper");
            sessionFactory = configuration.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;
        }

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

            if (currentSession == null)
            {
                // No current session
                return;
            }

            currentSession.Close();
            context.Items.Remove(CurrentSessionKey);
        }

        public static void CloseSessionFactory()
        {
            if (sessionFactory != null)
            {
                sessionFactory.Close();
            }
        }
    }
}

Step 6 - Creating Customer.hbm.xml mapping file

There are 2 ways to map database objects to class objects. One: Using hbm.xml files, second: using the NHibernate.Mapping.Attributes namespace.
For simplicity I will use the hbm.xml mapping file. It will map to some fields in the Customers table.
So right click on NhbHelper project and choose Add->New Item...:

We will choose XML file, and on name textbox, we will write the name Customer.hbm.xml

Before writing the content of the file, we must change Build Action from Content to Embedded Resource:

Now, here is what should be inside the file, which basicly mapping to class that we did not created yet.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="NhbHelper" assembly="NhbHelper">

<class name="Customer" table="CUSTOMERS">

    <id name="Customer_Id" column="CUSTOMER_ID" type="t" >
      <generator class="native"></generator>
    </id>

    <property name="Cust_First_Name" column="CUST_FIRST_NAME" type="String" length="20" ></property>
    <property name="Cust_Last_Name" column="CUST_LAST_NAME" type="String" length="20" ></property>
    <property name="Credit_Limit" column="CREDIT_LIMIT"></property>
    <property name="Cust_Email" column="CUST_EMAIL"></property>
    <property name="Date_of_Birth" column="DATE_OF_BIRTH"></property>
</class>

</hibernate-mapping>

Step 7 - Creating the Customer class

The last step in the NhbHelper project is to create the actual Customer class.
So right click on the NhbHelper project and choose Add->Class...

We will call the class (file) name: customer.cs.
Here is the content of the customer.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace NhbHelper
{
    public class Customer
    {
        private int CUSTOMER_ID;
        private string CUST_FIRST_NAME;
        private string CUST_LAST_NAME;
        private double CREDIT_LIMIT;
        private string CUST_EMAIL;
        private DateTime DATE_OF_BIRTH;

        public virtual int Customer_Id
        {
            get { return CUSTOMER_ID; }
            set { CUSTOMER_ID = value; }
        }

        public virtual string Cust_First_Name
        {
            get { return CUST_FIRST_NAME; }
            set { CUST_FIRST_NAME = value; }
        }

        public virtual string Cust_Last_Name
        {
            get { return CUST_LAST_NAME; }
            set { CUST_LAST_NAME = value; }
        }

        public virtual double Credit_Limit
        {
            get { return CREDIT_LIMIT; }
            set { CREDIT_LIMIT = value; }
        }

        public virtual string Cust_Email
        {
            get { return CUST_EMAIL; }
            set { CUST_EMAIL = value; }
        }

        public virtual DateTime Date_of_Birth
        {
            get { return DATE_OF_BIRTH; }
            set { DATE_OF_BIRTH = value; }
        }
    }
}

Step 8 - Adding references in the MyFirstNhbOraSite website
We have finished with the NhbHelper project, and we can get back to the MyFirstNhbOraSite web site. First we need to add reference to the NhbHelper project, so right click on the MyFirstNhbOraSite web site and choose Add reference:

Choose the Porects tab and choose the NhbHelper project name:

We also need to add reference to the NHibernate.dll, so right click on the MyFirstNhbOraSite web site and choose Add reference. Choose the browse tab, and browse to the folder where NHibernate.dll exist:

Next reference is to the supplementlry refrence of NHiberante library which is the NHibernate.ByteCode.LinFu.dll:

The last reference we need to add is to the oracle ODP Oracle.DataAccess.dll file. However for some reason it is not enough to add reference on the MyFirstNhbOraSite web site in visual studio:

You must also copy the dll to the bin folder of the web site.
So you need to copy (default locations) the x:\oracle\client\product\11.2.0\client_N\odp.net\bin\4\Oracle.DataAccess.dll (Replace X with your disk drive and N with the client number to X:\Documents and Settings\Name\My Documents\Visual Studio 2010\WebSites\MyFirstNhbOraSite\Bin folder (Replace X with your disk drive N with oracle client number and Name with your logged in user name).

Step 9 - Creating the DisplayCustomer.aspx page

Now we would like to connect to our NHibernate project and display all our customers.
So we will create a simple ASPX web page with datagrid that will present all the customers.
So right click on the MyFirstNhbOraSite web site and choose Add New Item...

Choose Web Form and call it DisplayCustomer.aspx:

We just need to add a gridview control to the form and call it gvCustomers.

So the HTML code of the page will be:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DisplayCustomer.aspx.cs" Inherits="DisplayCustomer" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <asp:GridView ID="gvCustomers" runat="server">
        </asp:GridView>

    </div>
    </form>
</body>
</html>

Also we will assign the page as our start page. So right click on the DisplayCustomer.aspx and choose Set As Start Page:

Now we move to the code, where the actual binding is happening. The code file (DisplayCustomer.aspx.cs) should look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NhbHelper;
using NHibernate;

public partial class DisplayCustomer : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        NhbHelper.NHibernateHelper nbh;
        IList<Customer> myList;

        nbh = new NhbHelper.NHibernateHelper();
        ISession session = NHibernateHelper.GetCurrentSession();

        ITransaction tx = session.BeginTransaction();
        IQuery query = session.CreateQuery("select c from Customer as c");
        myList = query.List<Customer>();
        gvCustomers.DataSource = myList;
        gvCustomers.DataBind();
    }
}

Step 10 - Run and see the results

The small application is now finished and you can execute the application and (hopefully) see the results:

That's it, your comments, suggestions are more then welcome.

NHibernate with Oracle

Wednesday, September 22, 2010

Conneting NHibernate to oracle using ODP.NET