Agile ADO.Net Persistence Layer Part 4: Writing data access for new data shapes

I recently decided that I needed to take a fresh look at how to build a persistence architecture that would provide the flexibility of an ORM, that would embrace change instead of resist it (making maintenance code easier), and would use ADO.Net.  I started building from the ground up, threw away any best practices that resulted in friction, and Agile ADO.Net Persistence Layer is the result.  Part 1 in the series can be found here: Agile ADO.Net Persistence Layer Overview.

What’s a new data shape?

Has this ever happened to you?  You have a persistence architecture that works beautifully.  It’s effortless to get and save entities, everything works great.  Then you get a requirement for a search results page that will display data from your entity, fine. But then they also want the grid to display fields from another entity entirely.  It’s not even in the same aggregate!  You find yourself thinking things like what a stupid requirement, why would anyone want to do that. But the real problem isn’t with the requirement.  The problem is that your beautiful architecture is inflexible. It works great as long as you’re dealing with nicely segmented entities, but as soon as you need to handle data that is a composite of fields from two different entities, things fall apart.  The worst part of it is, in the back of your mind you’re thinking “it would be so easy to just write this in TSQL”.

Another scenario  you once again have your beautiful architecture.  This time you need to add a couple of fields to an entity.  It’s a pretty simple change, but you’re dreading it because you know that in order to make this simple change, first you have to modify all of the CRUD sprocs, then you need to modify the method signatures on every data access method for this entity in the DAL, then you need to modify the parser (or data mapper methods), then you need to modify the BAL methods that call the modified DAL methods, then you need to modify the entity class itself.  Once again, the architecture is inflexible.  Changes are painful.  I call this friction.

Changes like this happen all the time.  The majority of our time as developers is spent coding changes to existing systems.  Perversely, we usually design architectures to be really easy to use for green field projects, but really hard to use when we need to make changes to those same systems. Making it easy to deal with change was my main objective when coming up with this architecture.  In this post I’m going to go over solutions to the two scenarios above using Agile ADO.Net Persistence Layer.

Scenario 1:  Adding  fields to an existing entity

Just to refresh your memory, our solution looks like the screenshot below. Common contains a DataShapes folder that contains the class definition for every DTO (Data Transfer Object) used in our BAL.  For this scenario we’re going to be working with the Category DTO.  Our BAL contains a Services folder that contains a service class that provide business logic and persistence logic for each aggregate (that’s service in the DDD sense, not a web service).  We’ll be working with the CategoryService.

So, our Category data shape is a straight DTO. It has no methods, just properties.  It looks like this.

public class Category

{

    public Guid CategoryGuid { get; set; }

    public string CategoryName { get; set; }

    public string CategoryKey { get; set; }

 

    public Category()

    {

        CategoryGuid = NullValues.NullGuid;

        CategoryName = NullValues.NullString;

        CategoryKey = NullValues.NullString;

    }

}

Our CategoryService class contains business logic and data access methods.  Each data access method does three things, it defines a query, it selects a data shape to return, then it passes both the query and data shape to our DAO (Data Access Object) which executes the query and maps the results to the data shape. Our data access methods in the Category Service look like this.  You can see both methods returns a List<Category>.

public List<Category> GetAllCategories

{

      string query = @"SELECT *

                    FROM Category

                    ORDER BY CategoryName";

      SqlDao dao = SharedSqlDao;

      SqlCommand command = dao.GetSqlCommand(query);

      return dao.GetList<Category>(command);

}

 

 

public List<Category> GetAllCategoriesInUse

{

     string query = @"SELECT DISTINCT c.*

                    FROM Category c

                    JOIN PostCategory p on p.CategoryGuid=c.CategoryGuid

                    ORDER BY CategoryName";

     SqlDao dao = SharedSqlDao;

     SqlCommand command = dao.GetSqlCommand(query);

     return dao.GetList<Category>(command);

}

Now for our simple change.  We forgot to add a CreatedUtc field to our Category entity to store the date when a category was created.  Crap.  We already have a bunch of code written that uses these classes.  How hard is it going to be to make this change without breaking any of our data access methods and UI code.  Well the first step is obvious, we need to add CreatedUtc to the database table.  So we do that.  Now for the tough part.  Do we need to modify any sprocs? No.  Do we need to modify any DAL methods? No.  How about the data access methods in our service class, we need to change those right? Nope. The queries defined in the data access methods use “SELECT *” so they’ll pick up any new fields added to the Category table.  How about the DAO, surely we need to change some mapping code there?  Nope, it uses a little reflection to automatically map query results to a data shape based on field name.  As long as we use CateogryUtc for both our database column name and our Category DTO field name, no code changes are needed.  So what do we need to do?? Just one thing, we need to add CreatedUtc to our Category data shape like this.

public class Category

{

    public Guid CategoryGuid { get; set; }

    public DateTime CreatedUtc { get; set; }

    public string CategoryName { get; set; }

    public string CategoryKey { get; set; }

 

    public Category()

    {

        CategoryGuid = NullValues.NullGuid;

        CreatedUtc = NullValues.NullDateTime;

        CategoryName = NullValues.NullString;

        CategoryKey = NullValues.NullString;

    }

}

Done.  You have to admit, even for a simple change, that was pretty easy.  Just add the new field to the Category DTO, add the new column to the table, and the persistence layer handles the rest.  Now I know some of you are thinking “But what if I don’t want to use the same names for my columns and DTO fields” or “No way would I ever use reflection in my DAL, it’s too slow”.  Those are valid concerns, and I have an easy way to tune the DAO to address both of those issues, but the nice part is that if you’re willing to work within these constraints, your development can go quickly and with very little friction.

Scenario 2: Adding a new data shape

Now let’s look at the case where we need a new composite data shape that consists of data from two different aggregates.  Our site has Categories, Blogs, and BlogPosts. The customer tells us that we need to display a list of the the most popular categories along with a count of how many BlogPosts are in each Category. This doesn’t really fit with how we did our initial modeling for this project.  A BlogPost contains a collection of Categories not the other way around.  Still, the data model will support it, we just have a simple join table between the Category table and the BlogPost table.  There’s no reason we can’t write a query that uses the relationship in the other direction.

So, our solution is to add a new data shape called CategoryWithPostCount that will contain the category data plus a count of blog posts that are using that category.  Because we’re using an architecture that anticipates and embraces change, adding this new data shape will be a simple 2 step process.  First we create the CategoryWithPostCount DTO and add it to our Common/DataShapes folder.

public class CategoryWithPostCount

    {

        public Guid CategoryGuid { get; set; }

        public string CategoryName { get; set; }

        public string CategoryKey { get; set; }

        public int PostCount { get; set; }

 

        public CategoryWithPostCount()

        {

            CategoryGuid = NullValues.NullGuid;

            CategoryName = NullValues.NullString;

            CategoryKey = NullValues.NullString;

            PostCount = NullValues.NullInt;

        }

    }

Second, we open up our CategoryService class and add a new GetTopCategoryList data access method.  Remember that our framework does most of the heavy lifting for us.  All we need to do is define the query, pick a data shape, and then pass both to the DAO which will automatically map them by field name.  Writing the method is easy.  We fire up Sql Server Management Studio, write and test our query, copy one of the other data access methods, and paste our new query into it.  Here’s the result.

public List<CategoryWithPostCount> GetTopCategoryList(int pageSize)

{

    string query = string.Format(

                    @"SELECT TOP {0} c.*, q1.PostCount

                    FROM Category c

                    JOIN (

                        SELECT c.CategoryGuid, count(pc.PostGuid) as PostCount

                        FROM PostCategory pc

                        JOIN Category c ON c.CategoryGuid = pc.CategoryGuid

                        Group By c.CategoryGuid

                        ) q1 ON q1.CategoryGuid = c.CategoryGuid

                    ORDER BY q1.PostCount DESC

                    ", pageSize);

    SqlDao dao = SharedSqlDao;

    SqlCommand command = dao.GetSqlCommand(query);

    return dao.GetList<CategoryWithPostCount>(command);

}

That’s it, we’re done.  A brand new data shape complete with data access method ready to use in minutes.  When it’s this easy to add new data shapes, it really frees you up to work in a much more agile way.  You don’t need to worry about getting entity classes designed exactly right the first time, or missing pieces of data like a count or date created.  If you do, the architecture is designed to make it painless to add that stuff as it’s needed.  And none of this is hard, none of it is magic, we’ve just made some decisions and accepted some limitations (like DTO field names and table column names will be the same) that allow us to take the friction out of design changes.

Next time we’ll get into the DOA and the associated DataMapper classes.