The object-relational mapper for .NET

Populating Entities From Stored Procedures With NHibernate

Note: this was orginally posted on my own blog.

A short while ago we needed to fetch the data for some entities through a stored procedure for performance reasons. We already use NHibernate in the typical way to fetch and modify the data of this entity type, but we just wanted something so we could also use the resultset of the stored procedure to populate the entities. One of my team members spent some time figuring out how to get the data returned by the stored procedure into the entities without actually having to write the code ourselves. Turns out this was pretty easy to do. Let's go over the solution with a very simple example.

The stored procedure i'll use for the example is extremely simple, and you'd never need to use this technique for such a stupid procedure. But in the situation we faced at work, the stored procedure was obviously a lot more complicated. So the stored procedure for this example is just this:

ALTER PROCEDURE [dbo].[GetProductsByCategoryId]

    @CategoryId int





    SELECT [ProductID]










      FROM [Northwind].[dbo].[Products]

     WHERE [CategoryId] = @CategoryId


This just returns the product rows for the given CategoryId parameter. Again, you'd never do this in real life but this simple procedure is just used as an example. Now, the structure of the resultset that this procedure returns is identical to the structure that the Product entity is mapped to. This makes it really easy to get this data into the Product entities. Just add a named query to your mapping like this:

  <sql-query name="GetProductsByCategoryId">

    <return class="Product" />

    exec dbo.GetProductsByCategoryId :CategoryId


And this is all you need to do in code to get your list of entities from this stored procedure:

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

            query.SetInt32("CategoryId", 1);

            IList<Product> products = query.List<Product>();

Is that easy or what? Now, suppose that the stored procedure returns more columns than you've got mapped to the entity. You can still use this approach as well, but then you'll need to specify which return values map to which properties in the entity like this:

  <sql-query name="GetProductsByCategoryId">

    <return class="Product">

      <return-property column="ProductID" name="Id" />

      <return-property column="ProductName" name="Name" />

      <return-property column="SupplierID" name="Supplier" />

      <return-property column="CategoryID" name="Category" />

      <return-property column="QuantityPerUnit" name="QuantityPerUnit" />

      <return-property column="UnitPrice" name="UnitPrice" />

      <return-property column="UnitsInStock" name="UnitsInStock" />

      <return-property column="UnitsOnOrder" name="UnitsOnOrder" />

      <return-property column="ReorderLevel" name="ReorderLevel" />

      <return-property column="Discontinued" name="Discontinued" />


    exec dbo.GetProductsByCategoryId :CategoryId


Notice how the CategoryID and SupplierID columns are mapped to Category and Supplier properties, which in Product's mapping are mapped as Category and Supplier many-to-one types, so basically references of type Category and Supplier respectively. NHibernate basically just takes care of all of the dirty work.

Posted Sun, 23 November 2008 04:37:00 AM by DavyBrion
Filed under:

comments powered by Disqus
© NHibernate Community 2016