Populating Entities From Stored Procedures With NHibernate
Posted by Davy Brion on November 23rd, 2008
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
AS
BEGIN
SET NOCOUNT ON;
SELECT [ProductID]
,[ProductName]
,[SupplierID]
,[CategoryID]
,[QuantityPerUnit]
,[UnitPrice]
,[UnitsInStock]
,[UnitsOnOrder]
,[ReorderLevel]
,[Discontinued]
FROM [Northwind].[dbo].[Products]
WHERE [CategoryId] = @CategoryId
END
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
</sql-query>
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" />
</return>
exec dbo.GetProductsByCategoryId :CategoryId
</sql-query>
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.
November 23rd, 2008 at 6:39 pm
[...] Note: this was orginally posted on my own blog. [...]
November 23rd, 2008 at 9:04 pm
Lets suppose I wanted to show some grid with Supplier name and Category name. How will the mapping look if I wanted to eagerly load all (or some) Supplier and Category fields ? Is it possible ?
November 23rd, 2008 at 9:07 pm
If the data in the grid doesn’t need to come from a stored procedure, you can just retrieve the data with a Criteria that joins on the Category and Supplier tables.
Or you could simply map an entity to a View as well, we do that quite often at work… we do set the mutable and lazy attributes of the entity to ‘false’ in that case.
If the data in the grid needs to come from a stored procedure, you’re probably better off mapping a specific entity to the result of the stored procedure
November 24th, 2008 at 11:15 am
[...] Populating Entities From Stored Procedures With NHibernate – Davy Brion looks at using Stored Procedures with NHibernate to populate your entities, and then follows upwith a look at filling the associated entities from the same data set returned from the SP. [...]
November 24th, 2008 at 2:09 pm
[...] Populating Entities From Stored Procedures with NHibernate and Populating Entities with Associations from Stored Procedures with NHibernate (Davy Brion) [...]
November 24th, 2008 at 2:09 pm
[...] Populating Entities From Stored Procedures with NHibernate and Populating Entities with Associations from Stored Procedures with NHibernate (Davy Brion) [...]
November 24th, 2008 at 5:57 pm
@Robert:
For the grid results that you are wanting to display, you might consider using a technique similar to the one James Avery describes here:
November 24th, 2008 at 5:59 pm
@Robert – sorry for my incomplete comment, the link didn’t post right:
http://infozerk.com/averyblog/refactoring-using-object-constructors-in-hql-with-nhibernate/
December 4th, 2008 at 11:56 pm
Hi, very nice your article. Perhaps you can help with a problem i’m having with stored procedures (sp) and nhibernate. I want to map from the returned values of a sp a part to a domain object (already mapped through hbm.xml) and another part to a flag variable. Which is the best way of doing this? without casting objects? As a solution i was trying adding a property value in the doamin object named “status” and trying to map that property in the <return-property… of sql-query that constaints the sp.
December 5th, 2008 at 7:39 am
@Martin
i don’t know if that’s possible…