Stored Procedure in NHibernate Part 3 - Populate Two Related Entities Objects

Now we will see how to use stored procedure in NHibernate to return two entities at the same time. We will continue our work from the previous post (second post of this series). You can also use the works from the first post of this series, however you need to ignore the Component part from the examples below. I choose to continue from the latter post to show that in NHibernate, we can use stored procedures to populate many different types' objects at one time.

First we modify our existing stored procedure to return data to populate objects of the new entity as well:

ALTER 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.*
   , R.*
   , A.*
   FROM Users U
   INNER JOIN Roles R ON U.RoleId=R.RoleId
   LEFT JOIN Attributes A ON A.UserId=U.UserId
   -- other joins and conditions
END

Then we create the new entity class:

public class UserAttribute
{
    public int AttributeId { get; set; }
    public User User { get; set; }  // link to the main entity
    public string DisplayName { get; set; }
    public string Code { get; set; }           
}

And its .hbm.xml mapping file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping assembly="MyAssemblyName" namespace="MyNamespace" xmlns="urn:nhibernate-mapping-MyNHibernateVersionNumber">
  <class name="UserAttribute" lazy="false">
    <id name="AttributeId">
      <generator class="identity" />
    </id>
    <many-to-one name="User" column="UserId" not-null="true"/>  <!-- specify relationship to the main entity -->
    <property name="DisplayName" />
    <property name="Code" />
  </class>
</hibernate-mapping>

We also link the new entity from our main entity:

public class User
{
    public long UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime? DOB { get; set; }
    public UserRole Role { get; set; }
    public IList<UserAttribute> Attributes { get; set; } // link to the new entity
}

Also modify the main entity's mapping file to specify the relationship with the new entity. In this case we use bag collection mapping:

<?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" />
    <component name="Role" class="UserRole">
      <property name="RoleId">
        <column name="RoleId" />
      </property>
      <property name="Name" />
      <property name="Description" />
      <property name="Active" />
    </component>
    <bag name="Attributes">   <!-- specify relationship to the new entity -->
      <key column="UserId" />
      <one-to-many class="UserAttribute" />
    </bag>
  </class>
</hibernate-mapping>

Finally we modify the named query's mapping; add an alias attribute to the existing <return> element then add a new <return-join> element with its alias and property attributes in order to populate the new objects:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping assembly="MyAssemblyName" namespace="MyNamespace" xmlns="urn:nhibernate-mapping-MyNHibernateVersionNumber">
  <sql-query name="GetRelatedUsers"> 
    <return alias="U" class="User"></return>  <!-- alias is from the one used in the select query -->
    <return-join alias="A" property="U.Attributes"></return-join>  <!-- alias is from the one used in the select query -->
    exec spGetRelatedUsers :UserName, :NumberOfRecords  
  </sql-query>
</hibernate-mapping>

The codes to call the named query remain the same.

Stored Procedure in NHibernate Part 2 - Using Component Mapping

This time we will learn how to use stored procedure in NHibernate with Component mapping to extend an entity to contain another type in one of its properties. A Component is an object that is persisted as a value type. Usually it is used to represent objects that are part of an entity.

We will continue the work that we have done on the previous post. We will modify our main entity to have its related one-to-one type as a property. Then we will modify our stored procedure to populate these two objects together.

Firstly, we prepare a new class for the related one-to-one type that will be linked from our main entity:

public class UserRole
{
    public long RoleId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public bool Active { get; set; }
}

Then we modify our main entity (i.e. User class) to contain the new type as a property:

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

We also need to change the stored procedure to return the new type as well in addition of the main type:

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.*
   , R.*
   FROM Users U
   INNER JOIN Roles R ON U.RoleId=R.RoleId
   -- other joins and conditions
END

Finally we change the main entity mapping file to include the new type by using Component:

<?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" />
    <component name="Role" class="UserRole">
      <property name="RoleId">
        <column name="RoleId" />
      </property>
      <property name="Name" />
      <property name="Description" />
      <property name="Active" />
    </component>
  </class>
</hibernate-mapping>

On the next post we will see how to use stored procedure to populate two entities' objects (an entity with its related one-to-many entity).

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>();