NHibernate with nothing but stored procedures

Question

I'd like to have NHibernate call a stored procedure when ISession.Get is called to fetch an entity by its key instead of using dynamic SQL.

We have been using NHibernate and allowing it to generate our SQL for queries and inserts/updates/deletes, but now may have to deploy our application to an environment that requires us to use stored procedures for all database access. We can use sql-insert, sql-update, and sql-delete in our .hbm.xml mapping files for inserts/updates/deletes. Our hql and criteria queries will have to be replaced with stored procedure calls.

However, I have not figured out how to force NHibernate to use a custom stored procedure to fetch an entity by its key. I still want to be able to call ISession.Get, as in:

using (ISession session = MySessionFactory.OpenSession())
{
    return session.Get<Customer>(customerId);
}

and also lazy load objects, but I want NHibernate to call my "GetCustomerById" stored procedure instead of generating the dynamic SQL.

Can this be done?

Perhaps NHibernate is no longer a fit given this new environment we must support.

Answer 1

I've not tried it myself but you might want to looking into native sql and custom loader concept.

Here's a good article by Ayende Rahien: Using NHibernate With Stored Procedures

Here's another easier-to-understand version by Scott McMaster based on NH beta version: Stored Procedures in NHibernate

Answer 2

An occasional sproc one off is one thing, but if you want NHibernate to use sprocs for each operation, even if you could, why would you want to?

Don't... just... don't