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.com.  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

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

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

Tests

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

BAL

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;

blogService.Save(newPost);

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