Alex Rivera | Logout

ORM vs Handcoded Data Access Layer

Asked 2009-02-19T02:52:51.440
21

I'm a bit scared to ask this question as it may start a religous war so I want to be really clear on what I'm looking for. I'm looking for a reason(s) why you would or have jumped one way or the other and also for items to add to my lists. I'm looking for the big ticket, big bang items. Also, items specific to a product, maybe, if they are really relevant. At this point I'm trying to evaluate ORM vs Manual not product A vs product B.

ORM Advantages

 - Quick to code and low maintenance (in some/most scenarios) 
 - Additional features for "free" (no developer effort)

Hand Coded Advantages

 - More Efficient (at runtime, maybe not at dev time?)
 - Less layers of complexity
 - Most ORMS seem to struggle with being retricted to sprocs only

In the interests of full disclosure, I really don't like the idea of "something" executing code against my database that I can't directly modify, if I see fit but I can see the potentially massive development time advatages of an ORM.

Its probably also worth noting I'm in a .Net world

[edit] (the question at Using an ORM or plain SQL? seems to answer many of the questions and reinforce the point about performance)

So, to alter my question slightly

Has any built an app using an ORM in the early stages and then gradually replaced with with a handcoded DAL? What were the pitfalls of this approach?

[Further Edit - getting to the heart of the problem now] Having a website be able to execute any SQL against my database is scary. If all access is through sprocs my database lives in nice, safe, comfortable isolation. Using exclusively sprocs removes a lot of, if not all, SQL injection attack vectors. Any comments on that?

orm data-access
Edit
Report

3 Answers

3

I have struggled with this for a few years. Looked at a lot of things. And have over the last 3 months realized "why am I wasting all this time on this". I kind of view the ORM as a solved problem. I would rather trust some team that has complete focus on ORM to write an ORM layer than me. Mentally, I'm just ready for new challenges.

My choice is NHibernate. I'm really a newb to this right now. But I like the potential to use Fluent NHibernate or Castle ACtiveRecord also. This seems to be where the mindshare is.

But not sure what I would do in an "everything is a sproc" world.

answered 2009-02-19T03:03:44.287
3

I would argue that ORMs are only "quick to code" in the very beginning, particularly if you do not have an existing installed schema. Once you need to start squeezing more performance out of your system, you will need an ORM can be "quickly disposed of".

I find them to be a can of worms.

answered 2011-07-04T09:24:16.403
0

I have been VERY successful simply using a DataSets in .Net. We have a SOAP endpoint that will first validate the input with an .XSD. Then, you can use a rules engine to do some validation. Then...

If the request is to insert data:

  • Use GetXml() on the DataSet to convert to XML.
  • Send the XML to a sproc that uses OPENXML in SQL Server.

If the request is to select data:

  • Write a sproc to spit out the data with multiple SELECT statements.
  • Load that data into a DataSet (being sure to build "nested" relationships where needed).
  • Use GetXml() on the DataSet to covert to XML used in SOAP.

I've used this process with a great deal of success in several production environments. It's fast and simple if you know you'll only use SQL Server.

answered 2009-07-10T21:21:13.067

Your Answer