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.
Thats slick Davy. What if your proc returns a graph, could you map on to it?
Lets say we want to shape 3 entities out of this proc… Product, Category & Inventory..
Product.Id
Product.Name
Product.Category.ID
Product.Category.Name
Product.Inventory.OnHand
Product.Inventory.InStock
i believe it’s possible yes… but only if the proc returns the full graph
i’m gonna try it out and i’ll let you know 🙂
it should definitely be possible with the return-join attribute, but i either hit a bug in nh2.0 or i’m doing something stupid somewhere
gonna look into it
That’s cool. An alternative suggestion might be to create a db view that contains the result of a stored procedure, then map the view like a regular entity being sure to declare the properties as read-only in the mapping file. I’ve seen this implemented when using Oracle for aggregate reporting because stored proc support wasn’t there for Oracle. An example would be, get top 100 selling products in previous 24 hours. A view might not be applicable if you need to execute a procedure with params, and it makes several assumptions about the underlying db, but then so does exec dbo.SomeProcedure. For returning compositions of read-only instances, projections, aliases and AliasToBean may be of interest.