June 26
Data Access Debate
I’ve been yet again involved in some discussion about ORM tools and data access architectures. Here is my take.
There’s a lot of “religious” discussion when talking about ORM in general; few people have unbalanced views. Maybe because when you are fresh in the field you simply can’t have an opinion – it’s a very complicated matter. Later on when you do have experience you also have a lot of baggage in the form of assumptions and preconceptions about how things do and don’t work. I consider myself in the “against ORM” group; I think that ORM tools like NHibernate would do more harm than good in 90% of the projects I’ve been involved in and are bound to be misused by 98% of the developers I met, if left to their own devices.
My focus lately is mostly with LOB applications developed by non-rocket-scientists developers. In this environment things are not individually complex but are extensive: we are talking hundreds or even thousands of forms/pages/reports. High productivity using run-of-the-mill developers is paramount. Having half a dozen very senior and experienced people is not an option. I particularly like typed DataTables because of the Visual Studio designer support and easy data binding.
This is what I have been doing so far with some success, in a nutshell:
- Do some light requirement/analysis phase. Do use fake screenshots/prototypes. The UI s the place where the conceptual model of the end user and the software intersect; take advantage of that.
- Design the database very carefully based on the analysis. Discuss with the user again in case of doubt.
- Create a stateless DAL layer using the designers available with Visual Studio 2005/2008; Typed DataTables/DataAdapters for CRUD operations and others simple things. We have some heuristics as to what to create “mechanically” based on the database, all in the solution domain. I wish I had a code generation tool that would use the heuristics, like “add a method that returns a row given the primary key” . Maybe the developer would use an even simpler designer than the one that already comes with Visual Studio, but as Billy Wilder put in “Some Like it Hot”, “nobody is perfect”. I used to have a code generator tool for VS 2002/2003 but I didn’t upgrade it. Besides not supporting the “heuristics”, Visual Studio lacks support to sets (IN SQL operator) and some form of meta-WHERE clause immune to SQL injection. I can do both with some code and partial classes.
- Based on the requirements, design a stateless business layer. This layer defines and exposes new typed DataTables (not TableAdapters) as an important data type. Those DataTables are based on the problem domain (screens, for instance), *not* the database. This layer basically translates via code from the problem domain to the solution domain, something ORM tools propose to do via “mappings”. BTW, I have no shame using high level imperative languages such as C# rather than some supposedly superior declarative “magic sauce”. It also implements business rules and impose sequence, things not easily achievable (or at all achievable) by “ORM mappings”. This layer also implements authorization (usually role-based) and transactions (usually with the Lightweight Transaction Manager). If you need to expose services, this layer already has mostly the right semantics; you can mechanically create an asmx/WCF layer around it.
- For some simple, peripheral things like management tables used internally or perishable features I might even do 2-tier, using SQLDataSource on the forms/pages. A component package such as DevExpress rocks in that scenario (and is beeeeaaaaaaauuuuutiful, makes you look great).
Tools 
Help
