NHibernate stored procedure with multiple result sets [duplicate]

Question

Is there any known solution to getting NHibernate to work with stored procedures that return multiple result sets?

I am working on a project that the lead wishes to use NHibernate and our dba's only want us to read from stored procedures many of which return multiple result sets. I looks to me like NHibernate doesn't support this use case directly, Are there any work arounds / extensions for this?

Answer 1

Yes, it is possible

Add dummy queries for getting desired result. For example if we want fifth result set from the output of a stored procedure, after the actual query add 4 more dummy query(empty query), then use AddScalar method to retrieve the data using the column name. Example given below is actually retrieving data of column "export_file_line" from 5th and 7th result set

var session = GetCurrentSession();
var query = session.CreateSQLQuery("exec SPName @param1=:param1,   @param2=:param2");
query.SetParameter("param1", value1);
query.SetParameter("param2", value2);

var multiResults = session.CreateMultiQuery()
    .Add(query)// More table your procedure returns,more empty SQL query you should add
    .Add(session.CreateSQLQuery(" "))
    .Add(session.CreateSQLQuery(" "))
    .Add(session.CreateSQLQuery(" "))
    .Add(session.CreateSQLQuery(" ")
    .AddScalar("export_file_line", NHibernateUtil.String))// the fifth result set
    .Add(session.CreateSQLQuery(" "))
    .Add(session.CreateSQLQuery(" ")
    .AddScalar("export_file_line", NHibernateUtil.String))// the seventh result set
    .Add(session.CreateSQLQuery(" "))
    .List();

if (multiResults == null || multiResults.Count <= 6 || multiResults[4] == null || multiResults[6] == null) return result;
var headerList = (System.Collections.IList)multiResults[4];
var detailsList = (System.Collections.IList)multiResults[6];

Answer 2

The short answer is that NHiberate does not support this.

See here:

nhibernate multiple recordsets from stored procedure

Entity Framework ~kinda~ supports it.

http://msdn.microsoft.com/en-US/data/jj691402

The msdn article name is "Stored Procedures with Multiple Result Sets" (in case the link dies in the future)

The "kinda" is this part of the EntityFramework portion is this -> Your column-names must match your Poco objects property-names exactly.

Below is a quote from the msdn article.

Note:EF does not take any mapping into account when it creates entities using the Translate method. It will simply match column names in the result set with property names on your classes.