NHibernate sql-query with stored procedures

I’ve spent a lot of time trying to get Nhibernate to work calling my legacy stored procedures, and have been frustrated by the lack of documentation for any of the Nhibernate edge cases.  In my particular instance, I followed what documentation I could, by kept ending up with a “Named query not known error”.  I finally fixed it by looking at the Nhibernate source and back tracking from there. I figured that I’d document what my problems were in case it helps anyone else out there.

My setup…

• Nhibernate 2.1 GA
• FluentNhibernate 1.0 RC
• SharpArchitecture 1.0
• My.Data.Shared.dll (Entities, including My.Data.Entities.User.cs.  These are POCO objects)
• My.Data.Nhibernate dll (Repositories and references to NHibernate)
  

I obviously can’t show my production code, so I will use a bit of a contrived example. (Also, forgive the example naming conventions.  I tried to use a different name for each piece to make it clear what I was referring to.
My stored procedure is defined as…

ALTER PROCEDURE [dbo].[usp_custom_GetUsers_By_City_Age]
  @cityname VARCHAR(255), 
  @ageinyears int
AS

SELECT
  user_id,
  city_name,
  age_in_years
FROM
  tblUsers
WHERE
  city_name = @cityname
  AND age_in_years = @ageinyears

My Entity is defined as…

namespace My.Data.Entities
{
  public class User
  {
    public virtual int Id { get; set; }
    public virtual string City { get; set; }
    public virtual int Age { get; set; }
  }
}

Since I am using FluentNhibernate, I did not have any .hbm files in my solution to begin with.  In the project that was referencing Nhibernate (My.Data.Nhibernate.dll), I added a Queries.hbm.xml file to the root of the project.  Note: This must end in “.hbm.xml”. In the properties window, I made sure to set the “Build Action” to “Embedded Resource” and “Copy To Output Directory” to “Do Not Copy”.  Note : I originally tried setting this to “Conent” and “Copy Always”.  I could not get this to work successfully. 







Then, where I configured my Nhibernate session, I had to add a line to load all of the hbm files from the assembly.

var config = NHibernateSession.Init(sessionStorage,
new string[] { mappingPath },
new NHPersistance().Generate(),
configPath);

config.AddAssembly("My.Data.NHibernate");

The configuration of the hbm.xml turns out to be the most important part.  Here is mine for this sample…

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="My.Data.Nhibernate"
	                 namespace="My.Data.Entities">

  <sql-query name="GetUsersByCityAndAge" callable="true">
    <query-param name="City" type="string" />
    <query-param name="Age" type="int" />

    <return class="User">
      <return-property column="user_id" name="Id" />
      <return-property column="city_name" name="City" />
      <return-property column="age_in_years" name="Age" />
    </return>

    exec usp_custom_GetUsers_By_City_Age
    @cityname = :City,
    @ageinyears = :Age
  </sql-query>
</hibernate-mapping>

There is a lot going on in here.  The thing that turned out to be the biggest issue causing my “Named Query Not Known” problem was that I was missing the “assembly” attribute and the “namespace” attribute on the “<hibernate-mapping>” node. 

• The assembly attribute should match the assembly which contains the embedded resource of your hbm.xml file.



• The namespace attribute should match the namespace of your returned entity.



• The “<sql-query name=” attribute should be a unique name for your query.  This does not need to match anything else.  You will use this name when calling the stored procedure from code



• Add a “<query param” for each parameter to the database.  The name of the parameter can be anything.  You’ll use the name in the exec below



• The “<return class=” attribute should be the name of your entity



• The “<return-property column=” attribute should match the column that is returned from the stored procedure call



• The “<return-property name=” attribute should match the name of the property in the Entity



• When executing the stored procedure, you will call “exec STORED_PROCEDURE_NAME_IN_DATABASE” and pass the parameters.





• Multiple parameters are separated by columns



• The parameter name should match the name defined in the stored procedure (i.e. @cityname).



• The parameter value is the name you defined in the “<query-param name=” attribute

Lastly, we need to call the stored procedure from code. 
My repository is defined as…

namespace My.Data
{
  using System.Collections.Generic;
  using NHibernate;
  using SharpArch.Data.NHibernate;

  public partial class UserRepository : NHibernateRepository<My.Data.Entities.User>, IUserRepository
  {
    public IList<My.Data.Entities.User> GetUsers(string p_city, int p_age)
    {
      IQuery namedQuery = Session.GetNamedQuery("GetUsersByCityAndAge")
        .SetParameter<string>("City", p_city)
        .SetParameter<int>("Age", p_age)
        ;

      IList<My.Data.Entities.User> users = namedQuery.List<My.Data.Entities.User>();

      return users;
    }
  }
}