software solutions / project leadership / agile coaching and training

Why I don’t like hand rolled data access layers

Posted on October 13, 2009

In my last post, I mentioned that I think that hand rolled data access layers using stored procedures are a bad thing, and one of my co-workers asked me to elaborate.

The main reason that I don’t like hand rolled data access layers is that by writing your own DAL, you are basically reimplementing a solved problem. Think about all of the things you have worry about if you write your own DAL:

  • Creating the CRUD stored procs
  • Writing ADO.NET code to call the stored procs
  • Figuring out which entities are dirty and need to be saved, and which properties have changed
  • What relationships do you load when you load an entity from the database? How do you do lazy loading of relationships?
  • Writing tests to test all of this custom code that you have to write

There are numerous object-relational mapping (ORM) tools out there which take care of all of this. NHibernate is my favorite (more specifically, Fluent NHibernate). NHibernate has been around for several years and is very mature. There are ORMs from Microsoft (LINQ to SQL, Entity Framework), and numerous other ORMs out there.

These ORMs have thousands of people using them, so they have been well tested and have proven over time to be very effective at what they do. Countless hours have gone into the development, design, and testing of these ORMs.

Think about what you are trying to do by writing your own hand rolled data access layer. Who are you to think that you can do a better job in a short amount of time than the people who developed these mature solutions over several years? Data access is a very complicated thing to try and implement, and some of the most painful code that I’ve ever had to deal with was found in someone’s hand rolled data access layer.

Here’s the thing — a large percentage of your data access is vanilla loading and saving of objects from the database. In these cases, performance is not a concern and you do not need any special queries in order to optimize the loading and saving of these objects.

For a very small percentage of your data access, performance may be a concern and you may need to use custom stored procedures. So in these cases, you can bypass the ORM and write your stored procedures and custom code in order to optimize the loading and saving of these specific objects.

If you use stored procedures and custom code, you have more control over things. This also comes with a cost (longer time to develop). If you are accepting this cost for cases where you don’t need to optimize the data access, I would say that you’ve wasted time on premature optimization, not to mention that you’ve probably had to spend time implementing all of that data access code.

I would rather use an ORM that has implemented everything that is difficult about data access. Now all I have to tell it how to map to the database (which isn’t that hard) and tell it to do things for me. With lazy loading, it won’t load entity relationships (e.g. child collections on an entity object) unless it needs to. It knows when objects are dirty and if they need to be saved or not. I have an easy hook so that I can validate entities when they are saved.

The other day I configured a cache in NHibernate in 15 minutes. With very little code I was able to set NHibernate up so that it will cache entities that don’t ever change in memory so that it doesn’t have to get to the database to get them every time. There were numerous examples on the internet telling me how to do this, and I’m extremely confident that it’s going to work (I didn’t have to write any of the difficult code for caching because NHibernate has solved that problem).

I want to write code that solves business problems. Thankfully other people have written libraries that will help me do that. So I’m going to use them.


  1. Hello from Russia!
    Can I quote a post in your blog with the link to you?

    Polprav — October 16, 2009 @ 11:58 pm

  2. @Polprav,

    Fine with me.


    Jon Kruger — October 18, 2009 @ 8:54 pm

  3. Thank you very much for expanding. When you say you use lazy loading, do you use some sort of session-per-request pattern, or what?

    mgroves — October 19, 2009 @ 1:37 pm

  4. @mgroves,

    For web sites, I do session-per-request. For thick client apps, it depends.

    Jon Kruger — October 19, 2009 @ 4:19 pm

Leave a comment

I have over 15 years of software development experience on several different platforms (.NET, Ruby, JavaScript, SQL Server, and more). I recognize that software is expensive, so I'm always trying to find ways to speed up the software development process, but at the same time remembering that high quality is essential to building software that stands the test of time.
I have experience leading and architecting large Agile software projects and coordinating all aspects of a project's lifecycle. Whether you're looking for technical expertise or someone to lead all aspects of an Agile project, I have proven experience from multiple projects in different environments that can help make your project a success.
Every team and every situation is different, and I believe that processes and tools should be applied with common sense. I've spent the last 10+ years working on projects using Agile and Lean concepts in many different environments, both in leadership roles and as a practitioner doing the work. I can help you develop a process that works best in your organization, not just apply a prescriptive process.
Have any questions? Contact me for more information.
Iteration Management - Your Key to Predictable Delivery
From Stir Trek 2016 and QA or the Highway 2015
From CodeMash 2016, QA or the Highway 2014, Stir Trek 2012
The Business of You: 10 Steps For Running Your Career Like a Business
From CodeMash 2015, Stir Trek 2014, CONDG 2012
From Stir Trek 2013, DogFoodCon 2013
(presented with Brandon Childers, Chris Hoover, Laurel Odronic, and Lan Bloch from IGS Energy) from Path to Agility 2012
From CodeMash 2012 and 2013
(presented with Paul Bahler and Kevin Chivington from IGS Energy)
From CodeMash 2011
An idea of how to make JavaScript testable, presented at Stir Trek 2011. The world of JavaScript frameworks has changed greatly since then, but I still agree with the concepts.
A description of how test-driven development works along with some hands-on examples.
From CodeMash 2010
From CodeMash 2010