I had to create a stored procedure to be called from NHibernate. You could use Session.Connection to execute with ADO.Net, but I like the idea of staying in NHibernate for consistency. Anyway, I found a lot of documentation on how to call one, but not with a parameter, so I thought I’d document that here. Note that I recommend NHibernate in Action for understanding all the various query methods.
This will be a simple and contrived example, that in no way justifies not just using NH linq, Criteria, or HQL to query. But let’s say you have a book table, and you want to query by author and for some reason you need to do this in a stored proc, because there is some aspect of the code or optimization that you only can do in the db.
Create your stored proc:
Use [MyDatabase]
Go
if OBJECT_ID('[dbo].sp_BooksByAuthor') is not null
begin
drop proc [dbo].sp_BooksByAuthor
end
go
create proc [dbo].sp_BooksByAuthor
@author_id bigint
as
begin
Set NOCOUNT on
Select b.*
From Books b
Where AuthorId = @author_id
end
Go
*Note the alias for books is optional, but if you do it, you need to specify it in the query mapping (see below).
Then map the query. Somewhere in one of your mapping files, but outside of a class put:
<sql-query name="MyBookByAuthorQuery">
<return class="Book" alias="b" />
exec sp_BooksByAuthor :AuthorId
</sql-query>Finally, your NHibernate query would look like as follows:
public IList<Book> GetBooksByAuthor(Author author)
{
var session = SessionFactory.GetCurrentSession();
var qry = session.GetNamedQuery("MyBookByAuthorQuery");
qry.SetParameter("AuthorId", author.Id);
return qry.List<Book>();
}Your data access may vary a lot from a simple method like that, but you get the idea.
One thing worth noting, if you get an error about clazz_, it’s related to polymorphism. Go read this post for how to write your sql to account for it: http://www.methodicmadness.com/2009/01/nhibernate-what-is-heck-clazz.html