R – Is it worth use ORM with Stored Procedures

.net-3.5ormstored-procedures

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?

Best Answer

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.

Related Solutions

The “N+1 selects problem” in ORM (Object-Relational Mapping)

Let's say you have a collection of Car objects (database rows), and each Car has a collection of Wheel objects (also rows). In other words, Car → Wheel is a 1-to-many relationship.

Now, let's say you need to iterate through all the cars, and for each one, print out a list of the wheels. The naive O/R implementation would do the following:

SELECT * FROM Cars;

And then for each Car:

SELECT * FROM Wheel WHERE CarId = ?

In other words, you have one select for the Cars, and then N additional selects, where N is the total number of cars.

Alternatively, one could get all wheels and perform the lookups in memory:

SELECT * FROM Wheel

This reduces the number of round-trips to the database from N+1 to 2. Most ORM tools give you several ways to prevent N+1 selects.

Reference: Java Persistence with Hibernate, chapter 13.

Php – Good PHP ORM Library

Look into Doctrine.

Doctrine 1.2 implements Active Record. Doctrine 2+ is a DataMapper ORM.

Also, check out Xyster. It's based on the Data Mapper pattern.

Also, take a look at DataMapper vs. Active Record.

Best Answer

Related Solutions

The “N+1 selects problem” in ORM (Object-Relational Mapping)

Php – Good PHP ORM Library

Related Topic