A couple years back we often hear the buzz word: 3 tier architecture, which leads to the wide spread use of Transaction Scripts. Then developers start to realise that it is not easy to utilise Object Oriented techniques with Transaction Scripts, and starting to head towards a Domain Model architecture. Together with Service Layer, Domain Model forms the Business Logic in a conventional 3 tier architecture.

Putting maintainability and extendability (and testability and readability and performance and thread safety and... other things) aside, most developers would not have a problem building a 'working' Business Logic. However, the same does not seem to be true for Data Access Layer. This post talks about the some of the issues that I encountered when I was writing my own Data Access Layer in a Domain Driven Designed business application.

Looking at what Microsoft provides until recently, (the tools and framework) seem to favor bundling Business Logic into Data Access Layer. UI components are more than happy to interact directly with persister, tempting younger developer to never grow out of Transaction Scripts; duplicating complex business rules in numerous stored procedures, which in the end lead to maintenance hell.

This is no longer true with Microsoft heavily promoting LINQ, Entity Framework, MVC framework, etc.

The requirement for a Data Access Layer is often similar from project to project:

1. as we write most of the Business Logic ourselves, we often are expected to write our own Data Access Layer too.
2. use what the framework and the tools naturally provide (remember what I have just said earlier to how Microsoft seem to favor bundling Business Logic and Data Access Layer together)

Often companies would like to own as much as intellectual property as possible, and have little reliance on third party libraries- which I personally do not think the wisest decision to make. Producing at higher cost and lower innovation are not the way to have a competitive product.

Experience also have taught us, going against the grain can lead to catastrophic events, encountering 'breaking changes' during framework/library upgrade.

For my case, this happened in .Net 2.0 days. In the spirit of DIY (Do-It-Yourself), our options were left to:

1. wrapper to DataSet, or
2. a set of Gateway classes to access the database (probably have them automatically generated from database stored procedures or tables).
3. building our own Data Mapper

With the above requirements, I had heard arguments for crossing out Object Relational Mapping frameworks off the list; NHibernate and Castle Project Active Record. Using these libraries would actually provide us with a Data Mapper so arguably the best options around up to date. I have to make it clear here: crossing them off WAS and STILL IS not the right thing to do.

We chose option 2 (writing wrapper to Sql commands) in the past and I'm sure many have fallen into the same trap. Here are a couple of things to note from my experience
(I'll save the discussion of going down the DataSet path for another time) :

1. Relationships
   When Domain Model starts to contain relationship information, accessing related models from another can start to be very inefficient.
   Lazy loading and Unit of work are the key to solve this problem, but implementing them is not a small task.
   
   Lazy loading allows for the model to only fetch the information as it needs. Whereas Unit of work allow creation, deletion, updates and fetches be bundled into one execution (batch queries in a single business transaction as opposed to numerous small calls).
2. Single point of control
   As the relationship of the Domain Model gets complex (so is probably the database), the effort to add new/update/delete (existing) business rules becomes more and more difficult. Business rules are scattered and intertwined all over the place (and probably duplicated too).
3. Transaction scope
   trasaction scoping according to the business transaction scope is hard to do. Need the ability to daisy-chain transaction scope across Data Access Layer commands.
4. Dependency ordering
   dependency ordering (child need to be created first before parent due to foreign key constraints) can causes a lot of code duplication between Data Access Layer commands.
5. Custom query is hard to implement on Domain Model level. A common usage is; paging, sorting, filtering a list of items. Often in this case we then have to resort to dynamic Sql, which means we do not benefit for having a rich Domain Model.

Martin Fowler in Patterns of Enterprise Architecture goes to a complete depth discussing how to build a Data Mapper.
Oren Eini had a similar experience and goes into the issue deeper than I do here. Recommended read. I also took the liberty of using similar term that he uses in his post: 25 Reasons Not To Write Your Own Object Relational Mapper.

One thing I learn from my experience is that it is difficult to implement Domain Model access layer with Gateway. By the end of the day, I was barking up onto option 3, to write my own poor man's Data Mapper, which was nowhere near as good as available products in the market (NHibernate, Castle Project Active Record, etc).

The lesson learned is pretty obvious; there are always room for stored procedures, as there is always room for CLR optimisation. Performance is often lead to going anti-pattern and build a custom framework for your specific domain. However, it's important to choose the right tool for the job. We must not create our own tool if we do not have enough reason to do so.

The bottom line, Data Access Layer can look very humble in its responsibility. As we see above, can be very difficult to get right. As Martin Fowler and Oren Eini suggest, the complexity goes even higher when dealing with larger enterprise application due to concurrency and caching support.

We do not write our own textbox, datagrid (or do we?), why bother write another Object Relational Mapping platform or high performance multi dimensional data access layer when there are many available in the market and work great?

References
Oren Eini. 25 Reasons Not To Write Your Own Object Relational Mapper.
Martin Fowler. Patterns of Enterprise Architecture (Boston: Addison Wesley, 2007) 33-53.