High Performance Data Access Layer Architecture Part 3

This is the final post in a series that describes one design that I use for high performance data access.  In Part 1 we covered overall architecture and design of the PersonDb.  In Part 2 we covered the DALBase implementation.  If you haven’t read those posts I would recommend going back and looking them over before getting into today’s topic, the DTOParser classes. 

We’re implementing the architecture below.

So, at the beginning we decided that we’re using Data Transfer Objects (DTO) to move data between our BAL and DAL and that our DAL would have only two base return types; either a single DTO, or a generic List<DTO>.  At this point we’ve written our PersonDb, which encapsulates our data access methods, and we’ve written our DALBase, which encapsulates our GetSingleDTO() and GetDTOList() methods as well as helper methods for other things like getting a connection string and creating sproc parameters.  Below is the code that we wrote for GetSingleDTO().

// GetSingleDTO

protected static T GetSingleDTO<T>(ref SqlCommand command) where T : DTOBase

{

    T dto = null;

    try

    {

        command.Connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)

        {

            reader.Read();

            DTOParser parser = DTOParserFactory.GetParser(typeof(T));

            parser.PopulateOrdinals(reader);

            dto = (T)parser.PopulateDTO(reader);

            reader.Close();

        }

        else

        {

            // Whever there's no data, we return null.

            dto = null;

        }

    }

    catch (Exception e)

    {

        // Throw a friendy exception that wraps the real

        // inner exception.

        throw new Exception("Error populating data", e);

    }

    finally

    {

        command.Connection.Close();

        command.Connection.Dispose();

    }

    // return the DTO, it's either populated with data or null.

    return dto;

}

So this method encapsulates all of our repeatable logic for getting a single DTO from a reader, and we use .Net generics to create a generalized method that can return any type that inherits from DTOBase.  However, notice that the details of which data fields we’re getting from the reader and how these fields map to our DTO properties are delegated to another object, the DTOParser.

What are ordinals and why use them?

Before we get into the DTOParser, let’s take a minute to talk about ordinals.  An ordinal is basically just an index number that tells you where a given data field is in the stream that you’re accessing through your SqlDataReader.  Let’s say you have a data field “date_created” and you need to get the data for date_created out of a reader.  Most developers would use code that looks like this.

Object field = reader["date_created"];

DateTime dateCreated = (field == DBNull.Value) ? DateTime.MinValue : (DateTime)field;

The data is gotten from the reader by data field name, it’s stored in an Object, we do a DBNull check, and if our data value passes we cast it to DateTime.  This is pretty solid code and I like the fact that we’re always doing a null check, but there are still some problems with it from a performance perspective.

First, we’re getting our data from the reader by name “date_created”.  The reader doesn’t know which specific field “date_created” is.  It has to go find the index associated with that name and then it can then use that index to access the data. That index value is the ordinal and the SqlDataReader can work in a much more efficient manner if we give it an ordinal to work with instead of a data field name.

Second, we’re getting a DateTime value but we’re first casting it to Object. I’d rather not do that cast since I know that I’m looking for DateTime data, but the reader[“field_name”] syntax returns an Object, plus I need to do a null check.  What other choice do I have?  If I’m using ordinals, the answer is that the SqlDataReader has a strongly typed GetDateTime() method that was made for this exact purpose.  SqlDataReader has strongly typed GetXXX() methods for every data type which will allow us to avoid this cast to Object.  SqlDataReader also has an IsDBNull() method which we can use to do our DBNull check. The catch is, these methods won’t accept data field names, they require you to use ordinals.

So, let’s write the same code assuming that we know the ordinal for “date_created” is 4.  The result would look like this.

DateTime dateCreated = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4);

This code uses the most efficient method possible to get data from our SqlDataReader, and we save ourselves a cast to Object and a cast from Object (boxing and unboxing).  If we’re really serious about maximizing performance, this is the code we want to use.

How the DTOParser is Used

We covered this in the last post, but just to refresh our memories, let’s take a quick look at how our DTOParser object is used in DALBase.  The code below is taken from our generic method DALBase.GetSingeDTO<T>( SqlCommand command).   We create a return object of type T, and we use the command object that was passed in to get a reader.  If the reader has any rows, we call Read() on it. Next we use the DTOParserFactory to get a parser object.  The DTOParserFactory.GetParser() method takes the desired DTO type as a parameter and returns an instance of the appropriate concrete DTOParser class. At that point all we have to do is pass our reader to the parser, cast the returned DTO to type T, and do a little cleanup.

      T dto = null;

      command.Connection.Open();

      SqlDataReader reader = command.ExecuteReader();

      if (reader.HasRows)

      {

          reader.Read();

          DTOParser parser = DTOParserFactory.GetParser(typeof(T));

          parser.PopulateOrdinals(reader);

          dto = (T)parser.PopulateDTO(reader);

          reader.Close();

      }

      else

      {

          // Whever there's no data, we return null.

          dto = null;

      }

The DTOParser Base Class

Now we can finally get back to writing our DTOParser classes. We’re going to have a separate concrete DTOParser class for each DTO type in our application.  There are two things that we need every concrete DTOParser to do.  First, our parser needs a method that takes an  SqlDataReader then gets and saves the ordinals for all of our data fields. Second, the parser needs a method that takes a reader and returns a single DTO populated with the data for the reader’s current record. We’re going to define the interface for these two methods using a DTOParser abstract base class.  All of our concrete DTOParser classes will now inherit from DTOParser and implement these two methods.  Note that the return type for PopulateDTO is DTOBase, which is the base type for all of our DTOs.

abstract class DTOParser

{

    abstract public DTOBase PopulateDTO(SqlDataReader reader);

    abstract public void PopulateOrdinals(SqlDataReader reader);

}

The DTOParser_Person Concrete Class

Now we can finally get into our concrete parser class for the PersonDTO.  DTOParser_Person will encapsulate all of our logic for getting data field/column values from our data.  The class needs to do three things:

1. provide properties to store ordinal for each data field/column
2. implement the PopulateOrdinals() method
3. implement the PopulateDTO method

To refresh your memory, this is what our PersonDTO class looks like:

So we’ll start off by creating Ord_DataMemberName properties to hold the ordinal value for each one of our PersonDTO data members. The Ord_DataMemberName properties are of type int.  You may wonder why we’re bothering to create properties for each ordinal, why not just get these on the fly in our PopulateDTO() method?  The answer is that we really don’t need these properties when we’re accessing a single DTO.  However, when we’re getting a list of DTO’s we want to be able to get an instance of our parser, call PopulateOrdinals() one time, and then call PopulateDTO() for each item in our list.  In that situation we only populate the ordinals one time and because we are saving them to local properties, we can use them for each subsequent call to PopulateDTO(). The resulting DTOParser_Person class will look like this:

Now we need to implement the PopulateOrdinals() method.  This logic is pretty simple.  We take a reference to the SqlDataReader as our only parameter.  The reader has a GetOrdinal method that we can use to get the value of each ordinal by field/column name.  We just need to do this lookup for each field/column and then store the result in the corresponding Ord_XXX property.

public override void PopulateOrdinals(SqlDataReader reader)

{

    Ord_PersonGuid = reader.GetOrdinal("person_guid");

    Ord_PersonId = reader.GetOrdinal("person_id");

    Ord_UtcCreated = reader.GetOrdinal("utc_created");

    Ord_UtcModified = reader.GetOrdinal("utc_modified");

    Ord_Password = reader.GetOrdinal("password");

    Ord_Name = reader.GetOrdinal("name");

    Ord_Nickname = reader.GetOrdinal("nickname");

    Ord_PhoneMobile = reader.GetOrdinal("phone_mobile");

    Ord_PhoneHome = reader.GetOrdinal("phone_home");

    Ord_Email = reader.GetOrdinal("email");

    Ord_ImAddress = reader.GetOrdinal("im_address");

    Ord_ImType = reader.GetOrdinal("im_type");

    Ord_TimeZoneId = reader.GetOrdinal("time_zone_id");

    Ord_LanguageId = reader.GetOrdinal("language_id");

    Ord_City = reader.GetOrdinal("city");

    Ord_State = reader.GetOrdinal("state_code");

    Ord_ZipCode = reader.GetOrdinal("zip_code");

}

The only other thing we need to do is implement the PopulateDTO() method.  The logic for this is also simple.  The first thing we do is create a new PersonDTO.  Remember that PersonDTO, and all DTO types, inherit from DTOBase so we can use a PersonDTO as our return value.  Also, remember that the constructor for PersonDTO initializes all data members to they null value for their type (null values are defined in the CommonBase class). So, every data member starts off with a null value, which in our application means unassigned.  That means that if a field doesn’t pass the DBNull check, we don’t have to do anything to the corresponding PersonDTO data member, it’s already set to it’s null value.

So, after we have our PersonDTO object created, we just need to do a simple conditional for each data member.  First use the corresponding ordinal to make sure the value returned by the reader isn’t null.  Second, if that value isn’t null, then use the reader’s typed GetXXX() method to get the value.  Once all of the PersonDTO’s data members have been set, we return it. The resulting code looks like this.

public override DTOBase PopulateDTO(SqlDataReader reader)

{

    // We assume the reader has data and is already on the row 

    // that contains the data we need. We don't need to call read.

    // As a general rule, assume that every field must be null

    // checked. If a field is null then the nullvalue for that

    // field has already been set by the DTO constructor, we

    // don't have to change it.

 

    PersonDTO person = new PersonDTO();

 

    // PersonGuid

    if (!reader.IsDBNull(Ord_PersonGuid)) { person.PersonGuid = reader.GetGuid(Ord_PersonGuid); }

    // PersonId

    if (!reader.IsDBNull(Ord_PersonId)) { person.PersonId = reader.GetInt32(Ord_PersonId); }

    // UtcCreated

    if (!reader.IsDBNull(Ord_UtcCreated)) { person.UtcCreated = reader.GetDateTime(Ord_UtcCreated); }

    // UtcModified

    if (!reader.IsDBNull(Ord_UtcModified)) { person.UtcModified = reader.GetDateTime(Ord_UtcModified); }

    // Password

    if (!reader.IsDBNull(Ord_Password)) { person.Password = reader.GetString(Ord_Password); }

    // Name

    if (!reader.IsDBNull(Ord_Name)) { person.Name = reader.GetString(Ord_Name); }

    // Nickname

    if (!reader.IsDBNull(Ord_Nickname)) { person.Nickname = reader.GetString(Ord_Nickname); }

    // PhoneMobile

    if (!reader.IsDBNull(Ord_PhoneMobile)) { person.PhoneMobile = reader.GetString(Ord_PhoneMobile); }

    // PhoneHome

    if (!reader.IsDBNull(Ord_PhoneHome)) { person.PhoneHome = reader.GetString(Ord_PhoneHome); }

    // Email

    if (!reader.IsDBNull(Ord_Email)) { person.Email = reader.GetString(Ord_Email); }

    // ImAddress

    if (!reader.IsDBNull(Ord_ImAddress)) { person.ImAddress = reader.GetString(Ord_ImAddress); }

    // ImType

    if (!reader.IsDBNull(Ord_ImType)) { person.ImType = reader.GetInt32(Ord_ImType); }

    // TimeZoneId

    if (!reader.IsDBNull(Ord_TimeZoneId)) { person.TimeZoneId = reader.GetInt32(Ord_TimeZoneId); }

    // LanguageId

    if (!reader.IsDBNull(Ord_LanguageId)) { person.LanguageId = reader.GetInt32(Ord_LanguageId); }

    // City

    if (!reader.IsDBNull(Ord_City)) { person.City = reader.GetString(Ord_City); }

    // State

    if (!reader.IsDBNull(Ord_State)) { person.State = reader.GetString(Ord_State); }

    // ZipCode

    if (!reader.IsDBNull(Ord_ZipCode)) { person.ZipCode = reader.GetInt32(Ord_ZipCode); }

    // IsNew

    person.IsNew = false;

 

    return person;

}

Summary

That’s it!  We have a DTO!  We now have a framework to easily create, parse and return strongly typed DTOs and due to the optimizations that we made like choosing a lightweight data container, using SqlDataReaders with ordinals, and minimizing casts, our DAL will perform like lightning. 

Looking back over this code, I realize that there really are quite a few pieces.  However, I also noticed that most of the pieces are very small and easy to understand.  I try to employ SOLID principles, especially the Single Responsibility Principle, and looking back over a DAL design like this I think it really pays off in terms of maintainability and code readability. When you look at methods like PopulateOrdinals() or PopulateDTO(), those methods do only one thing, it’s very obvious from the method names and from code itself what the methods are designed to do, and it’s easy to see what needs to be done to implement this code for different DTO types.  I think the clarity and understandability created by designing code in this way is well worth the extra effort it requires.

So that’s pretty much it.  The one thing I did not cover is the DTOParserFactory class.  It’s just a simple factory class and I’m including the code for it as well as the full code listing for the DTOParser_Person class below.

- rudy

 

internal static class DTOParserFactory

{

    // GetParser

    internal static DTOParser GetParser(System.Type DTOType)

    {

        switch (DTOType.Name)

        {

            case "PersonDTO":

                return new DTOParser_Person();

                break;

            case "PostDTO":

                return new DTOParser_Post();

                break;

            case "SiteProfileDTO":

                return new DTOParser_SiteProfile();

                break;

        }

        // if we reach this point then we failed to find a matching type. Throw

        // an exception.

        throw new Exception("Unknown Type");

    }

}

 

class DTOParser_Person : DTOParser

{

    private int Ord_PersonGuid;

    private int Ord_PersonId;

    private int Ord_UtcCreated;

    private int Ord_UtcModified;

    private int Ord_Password;

    private int Ord_Name;

    private int Ord_Nickname;

    private int Ord_PhoneMobile;

    private int Ord_PhoneHome;

    private int Ord_Email;

    private int Ord_ImAddress;

    private int Ord_ImType;

    private int Ord_TimeZoneId;

    private int Ord_LanguageId;

    private int Ord_City;

    private int Ord_State;

    private int Ord_ZipCode;

 

 

    public override void PopulateOrdinals(SqlDataReader reader)

    {

        Ord_PersonGuid = reader.GetOrdinal("person_guid");

        Ord_PersonId = reader.GetOrdinal("person_id");

        Ord_UtcCreated = reader.GetOrdinal("utc_created");

        Ord_UtcModified = reader.GetOrdinal("utc_modified");

        Ord_Password = reader.GetOrdinal("password");

        Ord_Name = reader.GetOrdinal("name");

        Ord_Nickname = reader.GetOrdinal("nickname");

        Ord_PhoneMobile = reader.GetOrdinal("phone_mobile");

        Ord_PhoneHome = reader.GetOrdinal("phone_home");

        Ord_Email = reader.GetOrdinal("email");

        Ord_ImAddress = reader.GetOrdinal("im_address");

        Ord_ImType = reader.GetOrdinal("im_type");

        Ord_TimeZoneId = reader.GetOrdinal("time_zone_id");

        Ord_LanguageId = reader.GetOrdinal("language_id");

        Ord_City = reader.GetOrdinal("city");

        Ord_State = reader.GetOrdinal("state_code");

        Ord_ZipCode = reader.GetOrdinal("zip_code");

    }

 

 

 

 

 

    public override DTOBase PopulateDTO(SqlDataReader reader)

    {

        // We assume the reader has data and is already on the row 

        // that contains the data we need. We don't need to call read.

        // As a general rule, assume that every field must be null

        // checked. If a field is null then the nullvalue for that

        // field has already been set by the DTO constructor, we

        // don't have to change it.

 

        PersonDTO person = new PersonDTO();

 

        // PersonGuid

        if (!reader.IsDBNull(Ord_PersonGuid)) { person.PersonGuid = reader.GetGuid(Ord_PersonGuid); }

        // PersonId

        if (!reader.IsDBNull(Ord_PersonId)) { person.PersonId = reader.GetInt32(Ord_PersonId); }

        // UtcCreated

        if (!reader.IsDBNull(Ord_UtcCreated)) { person.UtcCreated = reader.GetDateTime(Ord_UtcCreated); }

        // UtcModified

        if (!reader.IsDBNull(Ord_UtcModified)) { person.UtcModified = reader.GetDateTime(Ord_UtcModified); }

        // Password

        if (!reader.IsDBNull(Ord_Password)) { person.Password = reader.GetString(Ord_Password); }

        // Name

        if (!reader.IsDBNull(Ord_Name)) { person.Name = reader.GetString(Ord_Name); }

        // Nickname

        if (!reader.IsDBNull(Ord_Nickname)) { person.Nickname = reader.GetString(Ord_Nickname); }

        // PhoneMobile

        if (!reader.IsDBNull(Ord_PhoneMobile)) { person.PhoneMobile = reader.GetString(Ord_PhoneMobile); }

        // PhoneHome

        if (!reader.IsDBNull(Ord_PhoneHome)) { person.PhoneHome = reader.GetString(Ord_PhoneHome); }

        // Email

        if (!reader.IsDBNull(Ord_Email)) { person.Email = reader.GetString(Ord_Email); }

        // ImAddress

        if (!reader.IsDBNull(Ord_ImAddress)) { person.ImAddress = reader.GetString(Ord_ImAddress); }

        // ImType

        if (!reader.IsDBNull(Ord_ImType)) { person.ImType = reader.GetInt32(Ord_ImType); }

        // TimeZoneId

        if (!reader.IsDBNull(Ord_TimeZoneId)) { person.TimeZoneId = reader.GetInt32(Ord_TimeZoneId); }

        // LanguageId

        if (!reader.IsDBNull(Ord_LanguageId)) { person.LanguageId = reader.GetInt32(Ord_LanguageId); }

        // City

        if (!reader.IsDBNull(Ord_City)) { person.City = reader.GetString(Ord_City); }

        // State

        if (!reader.IsDBNull(Ord_State)) { person.State = reader.GetString(Ord_State); }

        // ZipCode

        if (!reader.IsDBNull(Ord_ZipCode)) { person.ZipCode = reader.GetInt32(Ord_ZipCode); }

        // IsNew

        person.IsNew = false;

 

        return person;

    }

 

}