NHibernate multi query / futures with Oracle

.net, fluent-nhibernate, nhibernate, oracle, orm

Solution

I would like to share the way how I made NHibernate Future queries work with Oracle. You can just add the below two classes EnhancedOracleDataClientDriver and EnhancedOracleResultSetsCommand to your project and configure NHibernate to use the EnhancedOracleDataClientDriver class as the database driver. I would appreciate feedback whether this approach works for other people. Here is the source code of the mentioned classes.

EnhancedOracleDataClientDriver.cs

using NHibernate.Engine;

namespace NHibernate.Driver
{
    public class EnhancedOracleDataClientDriver : OracleDataClientDriver
    {
        public override bool SupportsMultipleQueries
        {
            get
            {
                return true;
            }
        }

        public override IResultSetsCommand GetResultSetsCommand(ISessionImplementor session)
        {
            return new EnhancedOracleResultSetsCommand(session);
        }
    }
}

EnhancedOracleResultSetsCommand.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using NHibernate.Engine;
using NHibernate.SqlCommand;
using NHibernate.SqlTypes;
using NHibernate.Util;

namespace NHibernate.Driver
{
    public class EnhancedOracleResultSetsCommand : BasicResultSetsCommand
    {
        private const string driverAssemblyName = "Oracle.DataAccess";

        private SqlString sqlString = new SqlString();
        private int cursorCount = 0;
        private readonly PropertyInfo oracleDbType;
        private readonly object oracleDbTypeRefCursor;

        public EnhancedOracleResultSetsCommand(ISessionImplementor session)
            : base(session)
        {
            System.Type parameterType = ReflectHelper.TypeFromAssembly("Oracle.DataAccess.Client.OracleParameter", driverAssemblyName, false);
            oracleDbType = parameterType.GetProperty("OracleDbType");

            System.Type oracleDbTypeEnum = ReflectHelper.TypeFromAssembly("Oracle.DataAccess.Client.OracleDbType", driverAssemblyName, false);
            oracleDbTypeRefCursor = System.Enum.Parse(oracleDbTypeEnum, "RefCursor");
        }

        public override void Append(ISqlCommand command)
        {
            Commands.Add(command);
            sqlString = sqlString.Append("\nOPEN :cursor")
                .Append(Convert.ToString(cursorCount++))
                .Append("\nFOR\n")
                .Append(command.Query).Append("\n;\n");
        }

        public override SqlString Sql
        {
            get { return sqlString; }
        }

        public override IDataReader GetReader(int? commandTimeout)
        {
            var batcher = Session.Batcher;
            SqlType[] sqlTypes = Commands.SelectMany(c => c.ParameterTypes).ToArray();
            ForEachSqlCommand((sqlLoaderCommand, offset) => sqlLoaderCommand.ResetParametersIndexesForTheCommand(offset));

            sqlString = sqlString.Insert(0, "\nBEGIN\n").Append("\nEND;\n");

            var command = batcher.PrepareQueryCommand(CommandType.Text, sqlString, sqlTypes);
            if (commandTimeout.HasValue) {
                command.CommandTimeout = commandTimeout.Value;
            }

            BindParameters(command);

            for (int cursorIndex = 0; cursorIndex < cursorCount; cursorIndex++) {
                IDbDataParameter outCursor = command.CreateParameter();
                oracleDbType.SetValue(outCursor, oracleDbTypeRefCursor, null);
                outCursor.ParameterName = ":cursor" + Convert.ToString(cursorIndex);
                outCursor.Direction = ParameterDirection.Output;
                command.Parameters.Add(outCursor);
            }

            return new BatcherDataReaderWrapper(batcher, command);
        }
    }
}

Problem

Original source

Related problems

• NHibernate: Custom Dialect - how to specify the class in a separate assembly