NHibernate named query and multiple result sets

Question

We have a stored procedure that returns several tables. When calling it using NHibernate, we use the bean transformer but only get the first table transformed and all other results are ignored.

I know that NH is able to process several queries in one db trip using futures but we only have one query and it produces a result that is similar to what we would get with futures, but getting this from a stored procedure.

I believe this scenario is quite common but could not find any clues. Is it possible to use NH to retrieve such results?

Answer 1

Yes,you can use MultiQuery "Hack" like this:

The procudure:

CREATE PROCEDURE [dbo].[proc_Name]
AS BEGIN
    SELECT * FROM Question
    SELECT * FROM Question
END

The NHibernate Query Code:

public void ProcdureMultiTableQuery()
{
    var session = Session;
    var procSQLQuery = session.CreateSQLQuery("exec [proc_Name] ?,?");// prcodure returns two table
    procSQLQuery.SetParameter(0, userId);
    procSQLQuery.SetParameter(1, page);
    procSQLQuery.AddEntity(typeof(Question));

    var multiResults = session.CreateMultiQuery()
        .Add(procSQLQuery)
        // More table your procedure returns,more empty SQL query you should add
        .Add(session.CreateSQLQuery(" ").AddEntity(typeof(Question))) // the second table returns Question Model
        .List();
    if (multiResults == null || multiResults.Count == 0)
    {
        return;
    }
    if (multiResults.Count != 2)
    {
        return;
    }
    var questions1 = ConvertObjectsToArray<Question>((System.Collections.IList)multiResults[0]);
    var questions2 = ConvertObjectsToArray<Question>((System.Collections.IList)multiResults[1]);
}

static T[] ConvertObjectsToArray<T>(System.Collections.IList objects)
{
    if (objects == null || objects.Count == 0)
    {
        return null;
    }
    var array = new T[objects.Count];
    for (int i = 0; i < array.Length; i++)
    {
        array[i] = (T)objects[i];
    }
    return array;
}