My quest for the holy grail of data access. The story so far

My holy grail is finding the right solution for working with database / persistence technologies. Looking back at over my career (all 13 years of it) I have been on this quest for most of it, but more actively in my .Net years. Before .NET I used be a VB/ASP guy using SQL Server  and database access was easy, ADO commands and recordsets talking to stored procedures (sprocs),  it was simple building applications in a data centric manor.  Then .NET 1.0, I jumped straight into .NET and ADO.NET from one of the early beta’s . Microsoft brought datasets to the table (or tables to the dataset) that changed the way we went about accessing data from a data store. By .net 1.1, I favoured data-readers over datasets, but my real concerns at this point was about the many lines of duplicate code I was writing. Here was the key moment.

The search started for a database access framework that removed the duplicated ADO.Net and kept data access in its own layer and generally make my life easier. I had mainly been developing applications using MS SQL server so sprocs were the API to the database and always a must do (every developer and DBA I worked with and knew had brought into the fact that sprocs were the right way to communicate with MS SQL server and would laugh at anyone who thought different). I had at the time reviewed code generation tools that I could point at my database tables and after watching a lovely progress bar, produced all my sprocs for CRUD behaviour accompanied by a C# class file for each table in my database. Although it worked and built a big percentage of an application for me, it was very procedural and still was not satisfied with it as a solution.

I then developed 2 different applications to address this. My first attempt was to create a business object template and a stored procedure template, then write my own code generator to fill in the gaps. This did work, but was still to procedural. My second app was to use an Aspect Oriented approach using reflection. This also worked and I was very proud of my creation. I showed this to a couple of developers I was working with at the time and they slammed it and I took it to heart. Looking back at this now, I wish I took their criticism with a pinch of salt as they showed me no better alternative and now I know they opinions were wrong. Effectively I development my own Castle ActiveRecord sitting on top of ADO.NET using sprocs.

Along came .NET 2005, along side digging deep into the world of OO, Java, design patterns, Agile, TDD, XP  and all the new stuff in C# 2005. My data access quest was still unfinished so I entered the quest again. There was huge amount of business application frameworks on the market. I reviewed and experimented with many frameworks from the Enterprise data access block, .Net tiers, CSLA etc. I still felt that I was on a false trail.

From the start of my quest in the .net age to where I was in 2005, my goals had changed. I no longer brought into the belief that sproc’s are truly wonderful and all the other propaganda around them. I developed my application around the domain problem not the database schema. In fact, I didn’t even care what my database technology was. I finally found Object Relational Mapping (ORM) as the right way to address my needs.

Thinking that my quest was coming to an end I faced a new problem . I was working in Microsoft shops and although I brought into ORM, no one else did. Trying to educate people came with resistance. Some people believe what they once learned, and are closed minded to new ideas and generally don’t question their current methods.  This problem spanned into another problems:

  1. Gaining ORM experience in a commercial project becomes hard to get.
  2. The problem is not just getting ORM buy in from others in the business, but getting people to think  “domain centric” instead of a “data centric” .

I had to admit, faced with these problems I was close to jumping out of land of Microsoft and into the world of Java where ORM and many other development approaches are openly encouraged. Before I jumped the fenced, the whispering of LINQ and WPF has kept my feet in the land of Microsoft. Knowing that Microsoft will be releasing their own ORM tool the commercial uptake of ORM will increase in time as it did with TDD and MSTest. Happier times lie ahead.

Its 2008, I practice Domain Driven Design (DDD) and have been building up my experience in nHibernate (I found learning Castle ActiveRecord first a good help). I am working with nHibernate in my day job and in my opinion nHibernate is still the powerhouse ORM for .NET. I have used Linq for SQL and I think its a good starting point for developers getting into ORM but nHibernate suites Domain Driven Design out of the box, you can bend Linq for SQL for DDD by using mapping files to kept for domain objects persistent ignorant as well as using the Repository pattern but its still second place to nHibernate. Linq for Entity has been long awaited, its big product it still too early for me to pass judgement.

NHibernate is still not perfect, working with XML mapping files is not fun. I have been using Fluent-nHibernate which replaces the XML mappings with lambda’s and has a DSL for creating a map for you entities. Creating queries with DetachedCriteria is not as nice as writing a linq query but if you achieve this with nNibernate.Linq.

I think i have found my holy grail (at last) using Fluent-nHibernate , Linq for Objects and nNibernate.Linq with the Repository and Finder pattern, which I will be providing code examples in my next post.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s