Is it worth use ORM with Stored Procedures

Question

My company's polices forces to use only stored procedures (SP) to talk with DB (no dynamic execution of SELECT, VIEWS on app side available)
Business objects (BO) are fetched from DB.
There is implemented simple BO-DB mapper using SQL Reader and this seems working well.

Form DB side we have one parametrized SPs for each Business Object one SP for all CRUD operations, switched by @MODE parameter) So we don't have separate SP like SP_INSERT, SP_UPDATE, SP_SELECT etc ...

I have read recently lot about linq2SQL, nHibernate, Subsonic etc and those look very interesting.
I noticed main ORM's benefit is flexible queries execution / filtering directly from c# code - in my word I cannot execute such (only SP allowed...)

SO, I'm wondering
- if it is worth to use ORM in my environment?
- can you mention any benefits?
- if so - what ORM you can recommend

What's your view?

Answer 1

Someone asked a similar question some time ago - about using an ORM to wrap a body of legacy stored procedures. If you have this type of incumbent code base or political constraints on the architecture you might want to look at alternative data access mechanisms and see if they will work better. Fowler's Patterns of Enterprise Application Architecture does a good job of cataloguing various options for this.

The alternative is to go through the political channels and see if you can get the constraint relaxed to 'Use stored procedures in the corner cases where an ORM will not work well, otherwise use the ORM'.

Answer 2

f it is worth to use ORM in my environment?

IMHO, probably not. ORMs focus on the object model and essentially try and treat the database as a dumb store or data. You probably have business logic contained in your stored procedures, which is somewhat counter to the general principles (or at least conventions).

In the very least you'll probably have manual mapping of your objects to and from the stored procedures.

Answer 3

I wonder what was the rationale behind this "architectural" decision.

Manually dealing with low-level ADO.NET stuff (yes, IDataReader is low-level) is almost never a good idea.

NHibernate allows you to specity custom SQL for querying and modifying data, but you lose a great deal of power NHibernate offers by doing so.

Rather, consider using a simple Result-Set Mapper, such as BLToolkit, which will free you from most of grunt work while dealing with sprocs.

Answer 4

Some (maybe all, I'm not fully familiar with all of them) ORM tools provide you a class to call you stored procedure. This helps with intellisense, types, names, etc..

SubSonic has this and I find it very helpful to be able to call Project.DAL.SPs.InsertEmployee('John', 'Doe') rather than build the parameter list.

Answer 5

I'm glad I don't work where you do. I got a VERY painful education in why stored procedures should be avoided unless they're essential for performance or data integrity reasons not long ago. I took on a legacy project. Half the program source was in Java under version control. Half of it was embedded as stored procedures in the database. You pretty much had to have DBA rights to even see it, and working on it remotely was out of the question. It also got into fights with the Microsoft SQL Server driver's transaction manager.

But having said that, the ORM managers I've worked with had no issues with stored procedures, only me.

The main thing was that much of the reason for using an ORM is to make it easier to do "stored procedures" in the application on satellite servers instead of dumping all the work on the database server. And, of course, it's also helpful when all the source code is in one place and in one language.