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.