Friday, March 6, 2009

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.

image

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:

image

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:

image

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;

    }

 

}

kick it on DotNetKicks.com

12 comments:

  1. Hi, good and interesting approach.

    One thing I don't understand: the GetSingleDTO code calls the PopulateOrdinals method every time for every row so where's the performance advantage?

    Wouldn't be nice if the ordinals where populated at DTOParser instantiation and if it was a singleton? The ordinals would be populated just the first time the object is referenced. The only drawback being that if the database changes you'll have to "refresh" either by some flush method or by restarting the application.

    ReplyDelete
  2. Hi Giorgio,
    PopulateOrdinals actually doesn't get called for every row. That function is separated out into a separate method so that when we're getting a list of DTOs, we can call it once and then we have the ordinals cached in the parser object (code sample in part 2, look at GetDTOList method).

    The approach that you mention where ordinals are initialized on instansiation definitely works. I opted to go the route of a method call because I wanted to make sure the programmer knew what was going on. I was afraid that if I hid setting ords in the constructor that some programmer down the line would try to reuse a parser object for multiple sprocs that return fields in a different order.

    ReplyDelete
  3. Ok, saw the code sample and it makes sense.

    I see the issue you talk about regarding using a singleton and the only way to avoid the problem would be to tie the parser to a stored proc but probably it would make things too complicated.

    I'm not sure exposing the inner workings of the parser class by requiring the populate method in the DTOParser abstract class is always desirable. Suppose you want to change the way you get the ordinals or cache them.

    Perhaps one could hide the populate method by populating the single ordinals in lazy properties.

    BTW, nice mechanism, it's clear and enables the separation of the DAL I like.

    ReplyDelete
  4. Thanks for the comments Giorgio. You might be right about the hiding the ordinals. I always try to code defensively and I can be overcautious some times. Either way the design is pretty easy to adapt to your needs.

    Right now I'm working in a completely different direction. I'm trying to come up with BAL patterns for ADO.Net Entity Framework. I've been trying to wrap the data context in a repository, but it seems like a lot of redundant code for very little benefit. I'm starting to experiment with using the data context itself as my repository. If anyone has any good patterns for using EF please email me a link. I'd love to see them. rlacovara@gmail.com.

    ReplyDelete
  5. I didn't look at ADO.Net Entity Framework with great attention but it seems to me very "heavy" and complex in it's inner workings, I fear it dictates over the application model making it depend over being persisted with EF, where it should be the opposite. Anyway, I don't like ORM in general, I can't see enough benefit to justify for the added dependency and the losing of control.

    ReplyDelete
  6. Really helpful, thanks.

    I was wondering, could you use:

    internal static class DTOParserFactory<T> where T : DTOParser
    {
    internal static T GetParser<T>()
    {
    return default(T);
    }
    }

    ReplyDelete
  7. Hi ilivewithian, The main reason I don't use static classes for the parsers is that I want to store the ordinal values in an instance. But even without that, I'm starting to avoid static classes in my persistence framework code because I'm concerned that they may become blocking points for multi-threaded apps. I'm not completely sure about the blocking thing but I've discussed it with some pretty smart software engineers and they have the same concerns.

    ReplyDelete
  8. Hi Rudy,

    I am using framework given in this post. How can I solve the following problem:

    Class A
    {

    variable x

    }


    Class B

    {

    A a;

    PopulateA()

    {

    }

    }


    B b = new B();

    b.a.variable = something

    In the above code I want that when I run this code (b.a.variable) if a is null it should not throw Null reference exception and should call PopulateA function to instantiate the object a. is there any way to do it without using if/else or try/catch.

    ReplyDelete
  9. You will have really hard problems to implement transactions with this approach...

    ReplyDelete
  10. Rudy,
    Certainly impressive article for those in persuit of a light weight DAl for their data access endeavour. I'm planning to use this approach in my recent project for Mobile Recovery system in for Windows Mobile 6 utilizing the ADO.NET CE and full scale versions for client and web service respectively. One thing I notitced here if I use same approach for my various modules of a full scale Banking System then the DTOs in the Common will create problems. As different programmers working on different modules and they should be able to edit their part of DTOs and DB/Persistence classes what will you suggest the workaround to this will be. Also suggest how could the Parser logic be merged with the DB classes or in DTO.

    Thanks,
    Khalid

    ReplyDelete
  11. Rudy,

    Thank you very much for this - it's exactly what I was looking for.

    You mentioned in another post that you manually bind DTOs against, say, a gridview control. But how do you handle updates? I tried using the ObjectDataSource for this, but I found that when a particular data item wasn't displayed in the data-bound control, the field's value would get set to null because the ODS uses the object's parameterless constructor and doesn't know the object's old values.

    I would be really grateful if you could give some guidance.

    Regards,
    Fergus

    ReplyDelete
  12. A very stimulating post. Regarding multithreading, would a simple SyncLock solve the problem and ensures that only a single thread can at every time access the database ? I would also like to second Cerebrado and ask you if this approach is easily compatible with transactions...

    ReplyDelete