I'm using a stored procedure to handle search on my site, it includes full text searching, relevance and paging. I also wanted it to return the total number of results that would have been returned, had paging not being there. So I've now got my SP returning 2 select statements, the search and just SELECT @totalResults.
Is there any way I can get NHibernate to handle this? I'm currently accessing the ISession's connection, creating a command and executing the SP myself, and mapping the results. This isn't ideal, so I'm hoping I can get NH to handle this for me.
Or if anyone has any other better ways of creating complicated searches etc with NH, I'd really like to hear it.
No, NHibernate only uses the first result set returned by the stored procedure and ignores any others.
You will need to use an alternative method, like ADO.NET.
Or, you can incur processing overhead by having two stored procedures. One for each result set. Gross.