views:

97

answers:

1

I understand how NHibernate separates the persistent db structure from the logic and how the mapping works, but how do you guys handle data intensive actions, complex select scenarios, data maintenance, etc? We use stored procedures for things like these and we actually have quite a few of them.

Now what is the recommended practice? Is separate set of stored procedures for each db back-end the way to go?

+1  A: 

If you are using stored procedures, then yes, you'll have to port them to each backend. You may be able to get away with defining paramaterized sql statements in the NHibernate config, but it would probably still be difficult to come up with statements that are database agnostic.

The easiest way is to let NHibernate generate your SQL, and just change the database dialect in the NHibernate config depending on which DB you are using. But based off of your situation, I think the answer is that you will have to port your procedures.

To put it another way - using stored procedures takes away NHibernate's ability to be used with a different database without having to do some work.

Daniel Auger
Thank you. I am not really going to be porting our current application to NHibernate, it just seems to me each db application ends up with some stored procedures that are simply the best solution speed-wise, which kind of suggests that multi-database is not an easy/cheap "feature", even though the middle layer (NH) helps a lot. Would you agree?
Martin Haluza
If you only have a handful of stored procedures and still use NHibernate for most sql operations, then I think it's still easier to move to another db than using stored procedures for everything. That being said, NHibernate does a lot more than generate sql. It tracks changes, manages object cache, object identity map, unit of work etc... Those features are just as useful as the SQL generation IMHO because they are very difficult/tedious to do by hand. NHibernate still has real value even in a 100% stored procedure environment. But yes, the more procs you have, the less easy it is to move DBs.
Daniel Auger