Is it possible to call a stored procedure using NHibernate which returns a custom object instead of domain object?

Question

I have several stored procedures that don't return the domain objects (i.e; objects which have corresponding sql table mapping in hbm files); but return the custom objects instead.

I want to call these stored procedures using NHibernate and fill my custom objects with the output automatically, instead of filling them mannually as I would do if I use a SqlDataReader.

An example shall be highly appreciated.

BTW: I use nhibernate 3.2 new feature mapping by code.

Answer 1

Maybe you can try the following (taken from https://stackoverflow.com/a/10513319/1236044 )

It uses CreateSQLQuery. You may try replacing the select... with Exec MyStoredProc

The key point is having your select or stored procedure return columns with the same name as the properties of the DTO you are trying to populate.

public class YourDto
{
    public int YourDtoId { get; set; }
    public string YourDtoTitle { get; set; }
}

then

var result = yourNhSession
    .CreateSQLQuery("select yourColumn1 as YourDtoId, yourColumn2 as YourDtoTitle from YOUR_TABLE")
    .SetResultTransformer(Transformers.AliasToBean<YourDto>())
    .List<YourDto>();