NHibernate named query and multiple result sets

Question

We have a stored procedure that returns multiple tables. When called using NHibernate, we use a bean transformer, but we only convert the first table and all other results are ignored.

I know that NH can handle multiple requests in a single db trip using futures, but we only have one request, and it gives a result similar to what we get with futures, but getting it from a stored procedure.

I believe this scenario is quite common, but did not find any clues. Can NH be used to produce such results?

Answer 1

Yes, you can use MultiQuery "Hack" as follows:

Sentence:

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

NHibernate Request 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; }