Mapping to Oracle Stored Procedure with Fluent NHibernate

Background

ORM mapping happens mostly between a database table/view and a class. But there are situations where the mapping should be between a stored procedure and a class. I encountered one of those situations when a basic database view was lacking in performance and I had to replace it with a stored procedure. This tutorial will show you how to use the Fluent NHibernate to map a class to a Oracle Stored Procedure.

Note:This tutorial only focuses on the query side. You will not be able to modify data with the following code.

The stored procedure

The easiest way to work with selective stored procedures in Oracle is to create a procedure which returns a SYS_REFCURSOR. In this tutorial we will use a simple procedure called SP_WEB_ACCOUNTS which takes a one parameter as an input and then returns the SYS_REFCURSOR (“record set”).

create or replace PROCEDURE SP_WEB_ACCOUNTS
(
    p_cursor OUT SYS_REFCURSOR,
    p_Customer Varchar2
)
IS

Begin
Open P_cursor for

  SELECT     AccountId     AS Id
    CustomerId   AS Customer,
    AccountDescription   AS AccountDESCRIPTION
  FROM Accounts
  Where AccountId=p_customer;

End SP_WEB_ACCOUNTS;

P_Customer is the input parameter for our procedure and it returns the p_cursor of type SYS_REFCURSOR.

The Fluent NHibernate mapping

At the moment the Fluent NHibernate does not support mapping into the SELECT procedures but that doesn’t mean  that we couldn’t use it to create the basic mapping between the class and the columns which the stored procedure returns.

In our case we have a simple class called Account.

    public class Account
    {
        public virtual int Id { get; set; }
        public virtual string Customer { get; set; }
        public virtual string AccountDescription { get; set; }
    }

We also have a AccountClassMap, defined like every other Fluent NHibernate mapping class:

    public class AccountMap : ClassMap<Account>
    {
        public AccountMap()
        {
            Id(x => x.Id);
            Map(x => x.Customer);
            Map(x => x.AccountDescription);
        }
    }

The mapping lacks one thing which is usually included:The name of the database table or view. We can’t use Fluent NHibernate to map the Account to our previously created stored procedure, so we have to resort to the native NHibernate way: We need a XML-file.

Note:Fluent NHibernate does support the mapping of INSERT, UPDATE AND DELETE stored procedures.

The XML-mapping

Creating the required XML-mapping is straightforward: We just add a new XML-file into the project and change its Build Action to “Embedded”.

Inside the XML-file we add a named SQL-query which works as the mapping between our stored procedure and our Account-class.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">

  <sql-query name="GetAccountsForCustomer">
    <return class="our.project.Account, our.assembly"  />
    { call SP_WEB_ACCOUNTS(:customer) }
  </sql-query>

</hibernate-mapping>

This XML-configuration tells fours things to the NHibernate:

1. Our stored procedure is known by the NHibernate with the name “GetAccountsForCustomer”.
2. Our stored procedure returns objects of type Account.
3. Our stored procedure is executed by “call SP_WEB_ACCOUNTS” –statement.
4. And that our stored procedure expects a parameter which NHibernate knows by name “customer”.

Now we have the required ClassMap (created with the help of Fluent NHibernate) and the required XML-configuration file. Then we just need combine these two in Fluent NHibernate configuration.

Configuring the Fluent NHibernate

When we previously had only the ClassMaps to use, our Fluent NHibernate configuration code looked like this:

            return Fluently.Configure(normalConfig)
                .Mappings(m =>m.FluentMappings.AddFromAssemblyOf<UserMap>())
                .BuildSessionFactory();

Making the Fluent NHibernate to take advantage of the XML-configuration file requires only a small modification:

            return Fluently.Configure(normalConfig)
                .Mappings(m =>m.FluentMappings.AddFromAssemblyOf<UserMap>())
                .Mappings(m => m.HbmMappings.AddFromAssemblyOf<UserMap>())
                .BuildSessionFactory();

This assumes that the newly added XML-files resides in the same assembly as the UserMap-class. Fluent NHibernate is smart enough to scan the assembly for the NHibernate mapping files.

Now everything is ready and we can start using our new mapping.

Querying the database with NHibernate

Using the mapping is very simple. We ask NHibernate to execute the query called GetAccountsForCustomer by calling session.GetNamedQuery-method and provide the required parameter for it:

var accounts = session.GetNamedQuery("GetAccountsForCustomer")
                .SetParameter("customer", User.Identity.Name)
                .List<Account>();

This reaches out to the database using the stored procedure and provides the list of Account-objects.

Further reading