Tag Archives: nhibernate

NHibernate Named SQL Queries with Parameters

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

NHibernate and Auto Properties

I’ve been working through the NHibernate with ASP.NET ProblemDesignSolution (Wrox Blox), with some small changes. I’m writing my sample in C# using the .Net framework 3.5. I prefer to use auto-properties.

It’s common that fields have private setters and only nhibernate can map using the backing field (set via reflection).

public String City { get; private set; }

The problem is telling NHibernate how to find the backing field. When you have an explicit field, you end up with something like:

<property access=”field.camelcase-underscore” name=”City” />

But with no backing field, that is a problem. I started looking around on the net and found the following:

StackOverflow discussion without much good info
and a blog post with an interesting response from Ayende Rahien.

Ayende is saying to not worry about the issue, NHibernate will still be able to set via reflection. That’s fine, but it feels a little “magical”, especially to a new developer coming along.

Why not set protected? It’s not unreasonable to expect a maintaining developer to understand that NHibernate sub-classes your class.

public String City { get; protected set; }

Now the mapping stays simple too:

<property name=”City” />

And there is no need to expect a maintaining developer to know that reflection magic is setting the property.