Should you use NHibernate with Stored Procedure?
time to read 5 min | 871 words

In my previous post about NHibernate and Stored Procedures, I showed how it can be done, and I closed of with this:

The disadvantages - You take away from NHibernate the ability to comprehend the structure of the tables, this mean that it can't do joins, eager fetching, etc. This is extremely important capability that just vanished. Likewise for queries, NHibernate's abilities to query your objects is being severly limited using this method of operation.

Galen commented:

It sounds like the following two architectural decisions are mutually exclusive:
1. Require stored procedures for all data access
2. Use NHibernate

Is there an effective way to do both?

We have a particularly large project (3 year, multi-million) on which we know we need code generation.  Our choices are down to
a) NHibernate/MyGeneration or
b) Roll our own code generation using CodeSmith templates. 

We want to use NHibernate, but our data architects are unhappy about losing the "require stored procedures" battle.

I won't get into the issues I have with "SP for everything", I already expressed them (at some length), elsewhere.  NHibernate is a great tool for abstracting the database, and calling Stored Procedures is a good step forward, mainly because some issues are best solved with them.

The main problem with stored procedures is that they rob NHibernate one of its most important advantages, its flexibility. If you want to do an arbitrary join between three entities and sort by a forth, you can do it very easily. This opens up a lot of options that you just can't have with SP. Using SP, if the SP doesn't allow it, you just can't do it. And any time you have a new need you have to either create a new SP or add parameters, and I am not sure which is worse.

Here is what I would recommend, assuming that the "Give Me Stored Procedures Or Give Away The Database" mindset is prevasive. Do all selects from views (preferably thin views), and all CUD via stored procedures. I had quite a success with using views for NHibernate, and I don't really care how NHibernate is writing stuff to the database. That is going to always be fairly simple insert/update/delete statement, and as long as the SP keeps the usual semantics, everything is going to be fine.

In most cases, this makes the DBAs happy, since then they get to deny everyone direct access to the databases, and they control both the views and the SP. However, this also means that NHibernate is mostly free to join between the views, so it doesn't lose any of its power.

Take into account that writing the SP and wiring NHibernate to use them is still not simple, but you get all the power of NHibernate and the DBAs are happy because they get to keep all of the control in their hand and can optimize the database to their heart's content.

Now, if you can't get your data architects to accept that, it is a problem.

Would I use NHibernate is a situation where SP are really the only way to use the database? Yes. But you should read this statement with the qualifier that says "I really like NHibernate and I know what I'm doing there." There is a lot of value that NHibernate brings to the table quite outside from the flexiblity of working with the data model (just the Unit Of Work is a huge issue for me, and I could gush on and on and on about it... hm, actually, I do ). But, I don't have any experiance in working with NHibernate in a SP-only manner. (And if I have any say in the matter, I won't :-) ). It might be better to try asking the Java guys that has done it before.

I would highly recommend against rolling your own, for reasons already mentioned.

Tweet Share Share 0 comments
Tags: