Use nHibernate to Call Stored Procedure to Return Dataset Without Mapping Entity

NHibernate 1.2 introduces support for queries via stored procedures and functions. The stored procedure/function must return a resultset to be able to work with NHibernate. An example of such a stored function in MS SQL Server 2000 and higher is as follows:

    CREATE PROCEDURE selectAllEmployments AS
        SELECT EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE,
        REGIONCODE, EMPID, VALUE, CURRENCY
        FROM EMPLOYMENT

    <sql-query name="selectAllEmployments_SP">
        <return alias="emp" class="Employment">
            <return-property name="employee" column="EMPLOYEE"/>
            <return-property name="employer" column="EMPLOYER"/>
            <return-property name="startDate" column="STARTDATE"/>
            <return-property name="endDate" column="ENDDATE"/>
            <return-property name="regionCode" column="REGIONCODE"/>
            <return-property name="id" column="EID"/>
            <return-property name="salary">
                <return-column name="VALUE"/>
                <return-column name="CURRENCY"/>
            </return-property>
        </return>
        exec selectAllEmployments
    </sql-query>

Notice that stored procedures currently only return scalars and entities.

Here is the problem: what if you don't have a table like "Employment" to return, and you do want to return a dataset that doesn't map to any table?

I played with nHibernate a litte bit and find a way to circumvent this limitation. What you need to do is create an empty table with columns maps to the dataset you want to return. Create its hbm mapping and C# domain model class too. You don't have insert any data into this table. Then nHibernate will be able to call the stored procedure and return the dataset you want. The downside of this approach is you don't want to create and maintain a lot of empty tables to just make nHibernate happy.

Use nHibernate to Call Stored Procedure to Return Dataset Without Mapping Entity

See also