Saturday, January 9, 2010

Agile Ado.Net Persistence Layer Part 1: Design Overview

Last year I did a blog post series on how to design a High Performance DAL using Ado.Net.  Judging by the response I’ve gotten from that series, there must be a lot of developers out there who believe that even with the availability of LINQ, Entity Framework, and a host of other ORM technologies, Ado.Net is still your best option when designing a persistence layer.  BTW, I’m one of them.

After that series I started digging into Entity Framework and LINQ, and I was impressed by how effortless those technologies made certain parts of application development.  Once the EF or LINQ mappings were in place, I found myself writing much less code and focusing more on the business logic of my application.  I also found myself driven right back to ADO.Net as I found myself struggling with how do something that I already knew how to do in T-SQL, or fighting errors resulting from an attached data context object that I really didn’t want to start with.

So, I found myself back with ADO.Net, but I didn’t want to give up the ease of development and coding efficiencies that I got from the ORMs.  I decided to take a fresh look at how to design an ADO.Net persistence layer.  I started at the top (the application layer) and thought about how I want my app code to consume business logic, and then I worked down from there. I incorporated many of the best practices that I’ve used over the years, but I also looked critically at each one and whenever I found that something was slowing me down or leading to duplicate code, I threw it out.  The resulting architecture is quick to develop on,testable, easily maintainable, and can be easily optimized for performance.  This series of posts will detail the entire design, from application code to database.

A peek at the final design

I always find it’s easier to follow along if I have some idea where I’m going, so this is a quick look at where we’re headed.  We’re going to cover the entire architecture for a simple blog aggregator called  RelevantAssertions is an Asp.Net MVC application that uses our new Agile Ado.Net Persistence Layer.  We have 4 projects in the RA solution, WebUI, Tests, Common, and BAL Here’s a quick look.



WebUI is our Asp.Net MVC application, that’s our application layer.  This contains all UI and presentation logic, but it contains absolutely no business logic.


Common contains classes that we need at all layers of our code. The DataShapes folder is where we define all of our DTO classes.


This project contains all of our automated tests for both the BAL and the UI.


I know it’s probably more correct to say BLL, but I like the term BAL.  It just sounds better. This is the big class where everything interesting happens.  The main workhorses of the BAL are the service classes.  These are not web services.  They are service classes in the DDD sense.  The service classes are going to be the one stop shop where our application code goes to do whatever it needs to do.  The service classes will also contain query logic, that’s right I said query logic.  Behind the scenes the service classes will use DAOs (Data Access Objects), Data Mappers, and Persisters to do their thing in an efficient object oriented way, but the only classes our application code will use directly are the services.  

What, no DAL??

You’ll notice that there is no DAL.  It seems a little strange to have an architecture that focuses on ADO.Net but doesn’t have a DAL, but there’s a reason.  Usually, the DAL is where I’ll put my query logic, mappings to query results, and any other database specific code.  The DAL would allow me to keep all of my TSQL and ADO.Net code separated from the rest of my application, and this separation provided me with some important benefits like:

1) Separation is it’s own virtue, that’s just the right way to do it.
2)  I wouldn’t have leakage of db or query logic into my business logic.
3)  I could easily swap out SQL Server with another database if needed.
4)  It encapsulates code that would otherwise be repeated.
5)  We need to hide TSQL from programmers, it scares them.
6)  It’s fun to make changes to 3 layers of code every time I add a new data member to an entity class.

At least that’s what I was always taught.  But after working with more ORM oriented architectures and the Domain Driven Design way of doing things, I started to look at things differently. Let’s look at some of these benefits (at least the ones that aren’t sarcastic).

I’ve never met anyone who’s ever switched out their database

YAGNI means You Ain’t Gonna Need It. The idea is that we spend a lot of time building stuff that we don’t really need. We build it because it seems like the architecturally correct way to do it, or we think we’ll need the feature one day, or maybe we’re just used to doing it that way.  Whatever the reason, the result is that we spend a lot of time coding features that are never used, and that’s not good.  After doing this for 14 years or so, I’ve never, ever, run into a single project where they’ve decided “hey, let’s trash the years of investment we’ve made in SQL Server and switch over to MySQL, or any other database.  Now I am aware that a db switch is likely if you’re writing a product that clients install onsite and it has to work with whatever their environment is, but for 99% of .Net developers this is just never going to happen.  I call YAGNI on this one.

Query logic IS business logic

One of the biggest gripes I had when I started investigating LINQ, EF, and Hibernate (yes I was looking at Java code) architectures is that they had query logic in their repository classes.  Now the query logic was written in LINQ, or EntitySQL, or some other abstracted query language, but it was still query logic.  Blasphemy!!  You can’t put query logic in a BAL class!  That stuff has to be abstracted away in the DAL or it will contaminate the rest of the application architecture! Our layered architecture is being violated!  Worlds are colliding! It’ll be chaos!! Then I started to notice something, it’s really easy to develop business logic when you include queries in the BAL.  In the past I would put my queries in a sproc, then I would write a DAL wrapper for the sproc, and a BAL wrapper for the DAL method.  Then, if the query changed, or if I needed an identical query but with a slightly different parameter list I would write a new sproc, then write a new DAL wrapper, then write yet another BAL wrapper method for the DAL wrapper method.  By the time all was said and done I would have this crazy duplication of methods across all layers of my application including my database!  And don’t even get me started on the crazy designs that I implemented to try and pass query criteria structures (basically the stuff that goes in the where clause) between my BAL an my DAL.  I came up with these crazy layers of abstraction that basically existed so that I wouldn’t have to create a simple TSQL WHERE clause in my BAL.  Then there’s the problem of handling sorting and data paging, that required even more DAL methods, and each of these DAL methods had corresponding wrapper methods in the BAL that did nothing but pass the call through to the DAL!  Why?? I was doing the right thing by separating my business logic from my query logic, why was it so painful?   The answer I finally arrived at is simply that query logic is business logic.  I’d been putting a separation where no separation belonged.  

All real programmers know TSQL

I’ve heard the argument that TSQL is too hard for programmers so we’re going to create something much easier for programmers to use like LINQ or EF.  The problem is that these tools require almost exactly the same syntax as TSQL but they put an extra layer of stuff in there that can break and a data context (or session for you nHibernate folks) that throws errors whenever you try to save a complex object graph.  How did this attitude that TSQL is a problem for programmers gain any traction?  Have you ever met a real programmer who can’t write TSQL?  And if you did meet such a person, would you let them touch your business layer code?  Why would we ever want to abstract TSQL away?  It’s the perfect DSL for accessing SQL Server data and every programmer in the world is already familiar with it.

Using good object oriented design and encapsulating data access code is a good thing

I fully believe this one, but once we decide that query logic is business logic and that we don’t need to hide TSQL from programmers, there’s no reason to put our well designed object oriented data access code in a separate project and call it a DAL.  I decided to just put it in a Persistence folder in my BAL and now I have one less DLL to worry about.

So, that’s some of what I was thinking when I made the decisions that I did.  It made sense to me.  I’m sure it won’t make sense to everyone, but I do think that it resulted in a very usable architecture.  Before I wrap up for today, I want to look at one more thing.

The target application code experience. What will it be like to use?

When I’m writing code in my application layer, consuming the logic that is provided through my BAL service classes, what does that code look like. Well I know a couple of examples of code I don’t want it to look like. 

I’ve been in a few environments where there were huge libraries of BAL classes, any of which could contain the logic I want.  I would often have to resort to a solution wide text search looking for sproc names or keywords that might exist in the method that I needed. I don’t want that.  I want everything I need to be in one easy to find place.

I’ve also seen a practice that’s common in the DDD (Domain Driven Design) crowd where you’ll need to go to a factory class to create a new entity, you need to go to a repository class to get an entity from the database,  if you have complex logic that involves more than one entity you need to go to a service class, and saving entities is a toss up between using either the repository or a separate service class. There may be a good reason to use that kind of class design inside of the BAL, but when I’m writing code in my application layer,  I don’t want to have to worry about which of 4 different classes I’m going to use.  So again, I’m a simple guy, when I need to get, save, or validate a BlogPost entity, I want a single service class that I can go to for everything. My app code should look something like this.

// instantiate service classes

BlogService blogService = new BlogService();

CategoryService categoryService = new CategoryService();

// Get data shaped as lists and pages of our DTOs

DataPage<BlogPost> page = blogService.GetPageOfBlogPosts(pageSize.Value, pageIndex.Value, sortBy);

List<Category> categoryList = categoryService.GetTopCategoryList(30);

// create and save a new BlogPost

BlogPost newPost = new BlogPost();

newPost.BlogGuid = blog.BlogGuid;

newPost.PostTitle = item.Title.Text;

newPost.PostUrl = item.Links[0].Uri.AbsoluteUri;

newPost.PostSummary = item.Summary.Text;

newPost.Score = 0;


Next time we’ll focus less on discussion and more on code.  We’ll look at DTO classes and the 4 main data shapes that will go into and come out of our BAL: DTO, List<DTO>, DataPage<DTO>, and String. 

Next Post:  Agile ADO.Net Persistence Layer Part 2: Use DTOs


  1. Rudy,
    Can you share the sample code that you have highlighted above.


  2. "We need to hide TSQL from programmers, it scares them." - Super!! )))

  3. Very interesting blog. I am looking forward for your next posts.
    Could you show how your persistence layer can be merged with latest technologies like RIA Services – ADO.NET Data Services and WCF.

  4. I am going to publish the code up on either codeplex or github. I just need to pull the framework out of my existing app. Re RIA Services and ADO.Net Data Services, I haven't used them yet so I don't have much to add there.

  5. Just wanna ask... BAL stand for what?

  6. Yeah, BAL is a bad term. It doesn't really mean anything it just sounds like DAL and is easier to day than BLL. If I had to pick, I would say it means business access layer. I think I'm going to start using the name Core instead.

  7. Think this could be used this on Compact Framework without having to rewrite it entirely?

  8. I like the new template, especially the links to the store deals. Good job!
    Flash template

  9. ive been struggling with having this secondary query language in the "business layer". seems hypocritical sometimes..why not skip the abstraction and query directly with sql? its much more flexible and you're right... ive never ever ever switched the database vendor. ever. this article is a breath of fresh air.

  10. I read you first articles on DTOs and liked and agreed with the concept.

    I, too have spent many hours trying to find a reasonably simple, yet flexible method for serving application layers with lightweight data (DTOs) from a back-end platform that is not overly convoluted for the sake of some pie-in-the sky design principle. I've tried nHibernate and have considered third party ORMs like LLBLGen (sounds decent, but I don't want to pay for them and the commitment to upgrades). Then there's the ever-evolving and changing Microsoft best practices and technologies that take more and more study, and which appear to be so heavy and complex that I can't imagine them to result in a realistically maintainable and speedy application.

    I agree with your arguments on the new concept - why do you need a DAL when the database platform is highly unlikely to change and the BLL ends up having to duplicate just about all the same methods for the sake of pass-through to a DAL. I look forward to reading on.

  11. I also am an author of a ORM framework which works quite similarly as this one and frankly, I came to the same conclusion on my own - I needed to stop putting separation where is no separation needed.

    Now I implemented an architecture in which simply the BAL gets the name of a stored procedure or SQL to execute and invokes a method to execute it, returning generic List. Parameters are bound through Reflection, and stored procedures are examined for their parameters and then cached! Perfect for Agile Projects.

    In general I would just recommend that you guys use more Generics and Inheritance in C# and you would not need to duplicate the code. My ORM framework leverages that and basically parameter projection happens and in the DAL and the BAL only names the procedure. Sort of like : You name it, I do it - thing. It comes down to only writing a stored procedure name and absolutely no code duplication. Soon I will write a post about the architecture that utilizes the framework

  12. Hey, thanks so much for posting this article.
    Please check out my blog for ADO.NET Interview Questions and Answers

    ADO.NET Interview Questions and Answers


  13. "After doing this for 14 years or so, I’ve never, ever, run into a single project where they’ve decided “hey, let’s trash the years of investment we’ve made in SQL Server and switch over to MySQL, or any other database."

    You are lucky. In 7 years of my experiences I had to change database for many projects.

    1. Ouch. What databases were you changing between?

  14. Rudy, Awesome article.
    I COMPLETELY agree with this conception and that's mainly the way i use to code my applications (even those in C).
    I have really never understood separation between DAL/BLL (always appeared to me as coding for coding, adding a layer that most of the time is mapped 1:1).
    In 20 years i have never changed of database (using Oracle 7->Oracle 11g) simply because investment in time is so important (most of SQL code is PL/SQL and we have developped a framework that would be time consuming (for same result ?) porting to another db).
    I no longer try to make generic DB provider, use code generation to generate first raw source code of BAL (enhanced later if necessary) and performances are there.

    Thanks for sharing those great articles; very interesting to see people focusing on the most important (efficiency) not coding for coding (defining tenth of empty classes doing nothing only for the beauty of the art).

  15. Thank you for your article.

    However, I think we sould not go so "extremist" when applying the Agile YAGNI principle otherwise I will say that separating UI and BL is useless also, and as a result we'll end up by having a very dirty spaghetti code!

    If you never worked on a project that has a requirement to change the way data is saved (you're lucky) that doesn't mean that you'll never have it! And believe me when it comes You Ain't Gonna Love It .. the YAGLY principle will be here!


  16. Hi Rudy,

    I'm 5 years behind you, it seems, and I'd like to know if you still hold to the same beliefs 5 years later? (In regards to disdain for muffin-topped architecture and a separation of BAL and DAL).