Stored Procedure in NHibernate Part 1 - Map to Simple Class

On this post, we will see a simple example of how to use stored procedure in NHibernate. In the coming posts we will learn how to use stored procedure with Component mapping to populate an entity with its related type and then how to populate two related entities' objects.

First, prepare the mapping file for the stored procedure. Save this as a .hbm.xml file.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping assembly="MyAssemblyName" namespace="MyNamespace" xmlns="urn:nhibernate-mapping-MyNHibernateVersionNumber">
  <sql-query name="GetRelatedUsers">  <!-- the name of named query that will be called by the codes later -->
    <return class="User"></return>  <!-- the type that will be mapped onto -->
    exec spGetRelatedUsers :UserName, :NumberOfRecords  <!-- stored proc name and its parameters -->
  </sql-query>
</hibernate-mapping>

On line 4 of the named query's mapping file above, we set the return type as a class (i.e. User). We can use <return-property> elements to specify the mappings for the class' properties, however I prefer to put the mappings inside the class' own mapping file to support future extension and accommodate more complex properties as we will see later in the coming posts. So we create a mapping file for the class:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping assembly="MyAssemblyName" namespace="MyNamespace" xmlns="urn:nhibernate-mapping-2.2">
  <class name="User" lazy="false">
    <id name="UserId">
      <generator class="identity" />
    </id>
    <property name="FirstName" />
    <property name="LastName" />
    <property name="DOB" column="DateOfBirth" />
  </class>
</hibernate-mapping>

Then here is the class:

public class User
{
    public long UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime? DOB { get; set; }
}

The stored procedure:

CREATE PROCEDURE [sec].[spGetRelatedUsers]
 @UserName nvarchar(256),
 @NumberOfRecords int
AS
BEGIN
   -- some processing before the final select
   -- this can include complex processing using temporary table(s), CTE(s), etc...

   SELECT DISTINCT TOP (@NumberOfRecords) U.*
   FROM Users U
   -- other joins and conditions
END

Lastly we call the named query from codes:

IQuery query = Session.GetNamedQuery("GetRelatedUsers");

//add the parameter(s)
query.SetString("UserName", approverUserName);
query.SetInt32("NumberOfRecords", numberOfRecords);

return query.List<User>();