Geeks With Blogs
Łukasz Kuryło's blog

NHibernate has many ways to create and executes queries. With one of them* - the Named Queries we can execute our manually created stored procedures. This example is dedicated to SQL Server. This is important to notice, because this code is database specified. That means, for each database the query can look diffrent. E.g. in SQL Server we call the stored procedure by using the syntax: exec <procedure>. In Oracle it is: call <procedure>, so we must remember it.


Let's assume we have a simple stored procedure in our database which takes two arguments:

create procedure FindFilesWithNameLike(@pattern nvarchar(max), @extension nvarchar(10)) as

select f.Id,f.Name,f.[Path],f.Size,f.Extension from [File] f
join Extension e on f.Extension=e.Id 
where f.Name 
like '%'+@pattern +'%'
and e.Name=@extension


To use it with NHibernate, we must create a mapping. This is done in the *.hbm.xml file (I didn't find if this can be done with Fluent NHibernate and/or mapping by code).


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <sql-query name="FindFilesWithNameLike"  callable="true" >
    <query-param name="pattern" type="System.String"/>
    <query-param name="extension" type="System.String"/>

    <return-scalar column ="Id" type ="System.Guid"/>
    <return-scalar column ="Name" type ="System.String"/>
    <return-scalar column ="Size" type ="System.Int64"/>
    <return-scalar column ="Extension" type ="System.String"/>
    <return-scalar column ="Path" type ="System.String"/>

    exec FindFilesWithNameLike @pattern=:pattern, @extension=:extension

As you can see - nothing special. We are using the query-param to define the procedure parameters and return-scalars to define, what columns (with types) are returned. At the end there is our database-specified code to execute the stored procedure with parameters.


When we have a mapping, we can now execute this query:

        public IEnumerable<File> Execute(string pattern, string extension)
            return session.GetNamedQuery("FindFilesWithNameLike")
                  .SetParameter("pattern", pattern)
                  .SetParameter("extension", extension)
                  .SetResultTransformer(new ResultToFileTransformer())

To do this, we are using the api for named queries. When we execute this query we will have a list of list of objects. Thats why I added the SetResultTransformer. When the database table and custom entity is mapped one to one, we can use the Transformers.AliasToBean<T>, otherwise we can create custom transformer. In this example it is the ResultToFileTransformer. That class must implement the IResultTransformer interface.

*probably with using the native sql support in the NHibernate this it possible either, but I didn't checked

Posted on Wednesday, August 24, 2011 12:12 AM NHibernate , SQL | Back to top

Comments on this post: Using stored procedures with NHibernate

# re: Using stored procedures with NHibernate
Requesting Gravatar...
Nice article, basic but very informative. Thanks
Left by Sarjeet on Sep 13, 2011 10:36 PM

Comments have been closed on this topic.
Copyright © Łukasz Kuryło | Powered by: