Does It Make Sense To Use NHibernate in a Sproc-Only Shop?

July 31, 2009 Russell Ball 7 comments

I just got the following email question from a friend:

If I’m stuck in one of those loony shops that mandates that all data access has to be done via stored procedures, will NHibernate buy me much? In other words, if the tool’s purpose is to eliminate me writing SQL, but I have to write SQL anyway, is there a point?

I’ve only been using NHibernate for about 6 months and am barely struggling to emerge from the newbie stage, but my gut instinct says that you would loose about 90% of the benefits if you only ever used sprocs with NHibernate.

However, if I were in my friend’s situation I would probably still use it.

Why?

The main reason isn’t because of time or productivity savings, but rather because I would bet on the long-term viability of NHibernate and ORM’s in general.

NHiberate is already pretty mainstream and becoming more popular every day. The fact that Microsoft is now fully invested in the ORM space with the Entity Framework means that it will only be a matter of time before the traditional sproc-only mentality finally disappears from even the most conservative software shops.

In the meantime, I’m guessing that I would be able to speed up that process by introducing NHibernate into my application and my workplace even it would be under less than optimal circumstances and not save me much (if any) time in the short term.

Based on my experience with human nature, I’m betting that once NHibernate is in the dev and production environments for a while, the other developers and administrators would get used to it and eventually loosen up enough to let me start mapping directly against tables. It’s a slight gamble, but one based on pretty predictable human behavioral traits.

If nothing else, it’s much more convincing to show someone a proof of concept based on an existing app in production that you only have to slightly modify rather than on a silly Northwind example.

As far as more technical reasons, I leave this up to the more advanced NHiberate using reader to comment on. My sense is that you could still cut down on some tedious mapping code (right to left assigment stuff), but using Fluent NHibernate’s Auto Mapping feature, but I haven’t tried this out yet on sprocs so that may be (at least for now) a dead end.

Are there any other compelling reasons to use NHibernate if you worked in a shop that mandated using only sprocs for data access?

Popularity: 10% [?]

Persistence NHibernate

#1 | Written by Paul Batum about 3 years ago.

The fluent nhibernate automapper is for generating nhibernate mappings, not for right-to-left assignment stuff. You might be confusing it with Jimmy Bogard’s project “Automapper” which has nothing to do with NH but is very useful for reducing right-to-left assignment code.

That said, I’m not sure if using NH with sprocs involves much right-to-left stuff. Ayende has a good post on the basics:

http://ayende.com/Blog/archive.....dures.aspx
#2 | Written by Tom Willis about 3 years ago.

I don’t know if this is still the case, but last year mapping properties to stored procedure params was a gamble due to having no control over the order of the params.

I tried my darndest anyway and I think it ended up causing more work for me, not only did I have to keep ~4 stored procedures in sync with the table(s) now there was source code as well.
#3 | Written by Russell Ball about 3 years ago.

@Tom

Thanks for the heads up about the issue related to param ordering. I’m curious about that one so I’ll play with it this weekend to see if either anything has changed or else there is a way around it (i.e. named parameters that make the order irrelevant).

I’m a little confused about your second statement. How would NHibernate cause more work with regards to keeping your sprocs in sync with your source code. Wouldn’t you also have to worry about that with regular ADO.NET code?
#4 | Written by Tom Willis about 3 years ago.

Yeah IIRC named params had no effect, but surely they’ve changed that by now.

What I meant by more work was that I had more to worry about in regards to the mapping.

Not only did I have to pay attention to the table structure/columns etc…. but the inputs/outputs of each stored procedure. Which in a sense from the mapping standpoint could be thought of as another table. So essentially a class that would be mapped to one tableish entity now has to be mapped N stored procedures table.

Ultimately what was needed was a convention for the stored procedures in terms of params and their names and what was returned. For example, you would need a sproc for fetching a single row by id at least, and there could be more select sprocs depending on your needs, each one would probably have to return a result set with the same columns/structure or you would likely drive yourself insane, and an update insert sproc that would likely contain identical params probably matching up closely to the columns of the underlying table and a delete by id sproc. Now, if you need a new property which will map to a column in the underlying table what do you have to do?

1: add the column(regardless of whether sprocs were used)
2: change each select sproc(s) to include the new column in the result
3: change param signature for insert and update
4: change mapped class(regardless of sprocs)

And as far as I can tell, the only tool you have to manage all that is your brain. I don’t trust mine to handle all that.

Without sprocs you still have to do 1 and 4, introducing sprocs into the equation to me yields little or no benefit other than keeping some DBA employed, or slowing down development to the point where it can be managed by some pointy haired boss, and distracting the developer from the problem domain.

Hope that explains things, this is strictly my point of view by the way, and runs the risk of your blog post being overrun by a religious war at this point. Apologies in advance.
#5 | Written by Russell Ball about 3 years ago.

@Tom

Thanks for the detailed clarification. I definitely see how having to create 6 mapping files because you have an update, insert, delete, and 3 versions of a select is insanely more difficult than just having to worry about 1 table mapping. That’s the beauty of using ORM’s the way they were supposed to be used (without sprocs).

However, if you didn’t use NHibernate and just used straight ADO.NET code instead, wouldn’t you also have one ADO.NET method for each NHibernate sproc mapping file with basically the same input and output information (and in a slightly more verbose format based on how many data access helper methods you have)? In your experience, if you had to compare number of lines of code between an ADO.NET version and an NHibernate with sproc version, would you actually come out with more lines of code on the NHibernate side?
#6 | Written by Tom Willis about 3 years ago.

@Russel

It’s hard to say which one would save you LOC. Going straight ADO.net has the “benefit” of being the standard though, so any code monkey can wrap their head around that or buy a book that shows how to do it.

Effort might be better spent getting away from sprocs entirely
#7 | Written by Mischa Kroon about 3 years ago.

I would probably just use a simple ORM.
Subsonic comes to mind, I just wrote a post about it’s Sproc support and how it might become better here:

http://bloggingabout.net/blogs.....tizen.aspx

#1 | Pinged by Arjan’s World » LINKBLOG for August 1, 2009 about 3 years ago.

[...] Does It Make Sense To Use NHibernate in a Sproc-Only Shop? – Russell Ball ‘ my gut instinct says that you would loose about 90% of the benefits if you only ever used sprocs with NHibernate. However (…) ‘ read on… [...]

Caffeinated Coder

Does It Make Sense To Use NHibernate in a Sproc-Only Shop?

Leave a Comment

Popular Posts

Stats

Blogroll

Featured Posts

Categories