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
like '%'+@pattern +'%'
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" ?>
<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)
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