0

I'm interested in using an object relational mapper for an existing system which is a ASP.NET client, ASP.NET Web Services middle layer, and with an Oracle back-end. All database access is done using stored procedures and no SQL is allowed in the web services. I've been investigating NHibernate, Telerik's OpenAccess ORM, and the Entity Framework. I titled this "segregated" because the database is pretty tightly controlled by the DBA's. They also control the database design and reworking the database for adequate normalization (for the object model) is pretty much out of the question. Also, allowing the tool to create any of the SQL is also out the question.

My question is: Given these constraints, which of these tools would allow the best integration for this sort of environment?

StoneJedi
  • 575
  • 1
  • 8
  • 19

3 Answers3

3

None at all.

You're not going to be using 99% of the functionality of an ORM by having everythign done in Stored Procedures.

Probably better to use a Micro ORM like ServiceStack.OrmLite, or Massive, etc...

But looking at any full fledge ORM like NH, LightSpeed, EF, is complete over-kill and will just create more complexity for 0 gain.

Phill
  • 18,398
  • 7
  • 62
  • 102
  • Thanks Phil. I'll need to look into those. I was hoping to use EF just to keep us on the same stack (plus we are moving to SQL Server relatively soon). – StoneJedi Mar 26 '12 at 06:49
3

Implementing your data access with stored procedures entirely does not mean that you won't gain any value by using an ORM. It just means that you will probably not use some of its benefits.

As for the ORMs you have evaluated, you have probably noticed already that:

  • All of them support Database First approach, where you can just create your model after the database is already defined, so you won't have to interfere with the DBAs work in any way but to ask for credentials
  • Entity Framework and OpenAccess provide visual representation of your model out of the box, while NHibernate doesn't
  • OpenAccess and NHibernate support Oracle, while with Entity Framework using Oracle is not so straight forward
  • The stored procedures support in Entity Framework and OpenAccess is much more sophisticated than in NHibernate. In OpenAccess you can even map a stored procedure to more than one results set.

I hope that helps.

Ivailo
  • 31
  • 2
1

Let me get this right. Your constraints are:

  • You have to use an ORM
  • You cannot modify the database in any way.
  • You can only use stored procedures.

I think I agree with @Phill. A full blown ORM is overkill when you cannot use it's functionality.

BTW, I once worked on a system like this where the DBA's ruled the roost and mandated only procedures to access the data. Nightmare.

drekka
  • 20,957
  • 14
  • 79
  • 135
  • Yeah, it's very restrictive and I've done some testing with generic SQL statements created by ORM's and they aren't any slower than the sprocs I am given. Plus, they're embedding business logic in the sprocs. I want the logic to exist in our middle tier and only there so we aren't duplicating anything and so that we don't have to look in two places for logic errors when testing. Oh well, I'll have to work with what I got! Great post here on the issue: http://stackoverflow.com/questions/1191244/convincing-a-die-hard-dba-to-use-an-orm-for-the-majority-of-crud-vs-stored-proce – StoneJedi Mar 26 '12 at 06:45
  • Also drekka, 1) I dont need to use an ORM. 2) I can't modify the database 3) yes, written by DBA. Lots of security restrictions on this application. – StoneJedi Mar 26 '12 at 06:52