NHibernate: Is it possible to use stored proc when updating/deleting/inserting a class mapped on view?

Question

Here is the preamble:

• I have a SQL View and mapped NHibernate C# class

• I only allowed to modify SQL View data through some stored procedures (Insert/Update/Delete)

How to denote such logic in mapping file? Is it possible with only specific mapping or i need some supplementary code?

Answer 1

You could invoke your SQL's stored procedures using NHibernate's Named Queries:

< ?xml version="1.0" encoding="utf-8"?>
<hibernate -mapping xmlns="urn:nhibernate-mapping-2.2">
    <sql -query name="FindPlayerByLastDrugTest">
        <query -param name="LastDrugTestDate" />

            <return class="Player">
                <return -property column="PlayerID" name="PlayerID" />
                <return -property column="PlayerName" name="Name" />
                <return -property column="TeamID" name="Team" />
                <return -property column="LastDrugTestID" name="LastDrugTestResult" />
            </return>

        exec spSelectPlayersByLastDrugTestDate @LastDrugTest=:LastDrugTestDate
    </sql>
</hibernate>

/*** Back on C# ***/
IQuery query = session.GetNamedQuery("FindPlayerByLastDrugTest")
                      .SetDateTime(new DateTime(2003, 1, 1));

// Return a collection
IList list = query.List();

// Return a single entity
var result = query.UniqueResult();

More information here.

Answer 2

This is described in 15.3. Custom SQL for create, update and delete

Example:

<class name="Person" table="PersonView">
    <id name="id">
        <generator class="guid"/>
    </id>
    <property name="name" not-null="true"/>
    <sql-insert>exec createPerson ?, ?</sql-insert>
    <sql-delete>exec deletePerson ?</sql-delete>
    <sql-update>exec updatePerson ?, ?</sql-update>
</class>