High Performance Data Access Layer Architecture Part 1

Why write data access code when we have ORMs?

Remember data access patterns?  It seems like there is a huge focus these days placed on Linq, ADO.Net Entity Framework, and other ORM-like tools that are intended to make programmers more efficient by doing all of that time consuming data access for you.  There definitely are productivity improvements to be had using these tools, but how does the resulting application perform? In most cases where we have a standard line of business application for which the primary concerns are functionality and workflow the answer is “good enough”.  These applications are a great fit for tools like this. However if you have a high volume application where performance is the primary concern, these tools may not be the right choice.  Both Linq and ADO.Net are significantly slower than well written ADO.Net code.  According to a post on the ADO.net Team Blog titled ADO.NET Entity Framework Performance Comparison, ADO.Net Entity Framework can be 50%-300% slower than ADO.Net using ordinals and SqlDataReaders.

So, my opinion is that ORMs are great for most applications, but when performance is a factor it’s best to roll your own DAL.  This post will demonstrate some of the patterns that I use for the Data Access Layer that allow for rapid development and lightning fast performance.

Use DTOs not DataSets or DataTables

First, what container are we going to use to pass data from our DAL to the other layers of our application?  The usual answers I get are either DataTables/DataSets or full business objects.  I don’t like either of these. DataSets and DataTables come with significant overhead and they don’t contain strongly typed data.  Business objects do contain strongly typed data, but they typically contain a lot of extra business logic that I don’t need, and they may even contain persistence logic.  I really don’t want any of that.  I want the lightest weight, simplest possible container that will give me strongly typed data, and that container is a Data Transfer Object (DTO). DTOs are simple classes that contain only properties.  They have no real methods, just mutators and accessors for their data. Below is a class diagram of a PersonDTO as well as the DTOBase and CommonBase classes that are in it’s inheritance chain. PersonDTO contains all of the data needed for a Person entity in my application.

 

Here’s how I typically construct a DTO.  First, DTOs are designed to move between layers of the application.  So, they don’t belong in the DAL.  I put them in a separate project/assembly named “Common”.  Then I create a reference to Common in my DAL, BAL, WebUI, and in any other project in my application.

Now we can start creating classes in Common. the first class we need to create is CommonBase. CommonBase’s only purpose is to contain static properties that define null values.  Our DTOs are going to contain both value type and reference type data, and since value types always have a value and are never null, this can make null checking a challenge in higher layers of the application. To further complicate things, some developers will use String.Empty or “” to represent a null value for a string. Others will use null (string is a reference type after all).  To avoid all this confusion, I like to define actual null values for each type in my Common assembly.  That way we have a predefined value that we can use for null checking and null setting throughout the application.  Here is the code for CommonBase.

    public class CommonBase

    {

        // Let's setup standard null values

        public static DateTime DateTime_NullValue = DateTime.MinValue;

        public static Guid Guid_NullValue = Guid.Empty;

        public static int Int_NullValue = int.MinValue;

        public static float Float_NullValue = float.MinValue;

        public static decimal Decimal_NullValue = decimal.MinValue;

        public static string String_NullValue = null;

    }

The next class is DTOBase. This base class encapsulates any common functionality for my DTOs.  Right now, the only thing I’m putting in DTOBase is an IsNew flag that can be used to indicate if a DTO contains newly created data (as opposed to data that was pulled from the database).

    public abstract class DTOBase:CommonBase

    {

        public bool IsNew { get; set; }

    }

Now I can create my PersonDTO class.  PersonDTO is just a bunch of properties that represent the data for a person record, and a constructor that initializes each property to to the null value for it’s type.

public class PersonDTO : DTOBase

    {

        public Guid PersonGuid { get;set; }

        public int PersonId { get; set; }

        public DateTime UtcCreated { get; set; }

        public DateTime UtcModified { get; set; }

        public string Password { get; set; }

        public string Name { get; set; }

        public string Nickname { get; set; }

        public string PhoneMobile { get; set; }

        public string PhoneHome { get; set; }

        public string Email { get; set; }

        public string ImAddress { get; set; }

        public int ImType { get; set; }

        public int TimeZoneId { get; set; }

        public int LanguageId { get; set; }

        public string City { get; set; }

        public string State { get; set; }

        public int ZipCode { get; set; }

 

        // Constructor

        // No parameters and all types are intialized to their

        // null values as defined in CommonBase.

        public PersonDTO()

        {        

            PersonGuid = Guid_NullValue;

            PersonId = Int_NullValue;

            UtcCreated = DateTime_NullValue;

            UtcModified = DateTime_NullValue; 

            Name = String_NullValue;

            Nickname = String_NullValue;

            PhoneMobile = String_NullValue;

            PhoneHome = String_NullValue;

            Email = String_NullValue;

            ImAddress = String_NullValue;

            ImType = Int_NullValue;

            TimeZoneId = Int_NullValue;

            LanguageId = Int_NullValue;

            City = String_NullValue;

            State = String_NullValue;

            ZipCode = Int_NullValue;

            IsNew = true;

        }

    }

How should the DAL send data to other layers?

When you’re building framework code or plumbing as I like to call it, it’s a good idea to stop periodically and really think about how the code you’re writing is going to be used.  The most useful technique that I use is to stop and visualize what I want the consuming code to look like. We’ve already decided that we’re using DTOs to contain data. Let’s take a moment to think about how we want our BAL code to look and what functionality it will require from our DAL. In my BAL, I’m probably going to have a PersonRepository, and in that repository I’m going to have methods that will want to get individual PersonDTOs and generic lists of PersonDTOs from the DAL, and I will probably want to have a single DAL object that provides methods for getting those DTOs.  So I want to create a PersonDb class in my DAL that will allow me to write BAL code that looks like this:

    PersonDb db = new DAL.PersonDb;

    PersonDTO dto = db.GetPersonByPersonGuid(personGuid);

    PersonDTO dto = db.GetPersonByEmail(email);

    List<PersonDTO> people = db.GetPersonList();

With that target in mind I’m going to create a PersonDb class in my DAL.  PersonDb is going to provide methods that will either return a single PersonDTO or a List<PersonDTO>.

The DAL Architecture

We’re going to have a DALBase that encapsulates all of our repeated logic for doing things like creating connections, TSQL commands, sproc commands, and parameters.  The DALBase will also contain methods for getting our 2 main return types, DTO and List<DTO>, from an SqlDataReader. To act as the one stop shop for all of our data access methods that get and set person data, we will create a PersonDB class.  PersonDB will inherit from DALBase and will contain all of our methods that return or save person data like GetPersonByEmail(), GetPersonById() and SavePerson().

We will also need to find a place to put the logic for reading our person data out of an open SqlDataReader and putting it into a PersonDTO.  This involves finding the ordinal for a data field, checking to see if it is null, and if it isn’t, storing the data value in the DTO.  This is really parsing logic so we’ll put it in a separate DTOParser_Person class.  Right now we’re only looking at the classes for PersonDTO, but we will need to have a different parser for each DTO type that the DAL can return (PersonDTO, CompanyDTO, UserDTO, etc.).  We’ll use an abstract DTOParser class to define the interface for all DTOParsers and to encapsulate any repeated functionality. Lastly, we’ll create a static DTOParserFactory class that will return an instance of the appropriate DTOParser for any DTO type that we pass in to it. So if we need to parse a PersonDTO out of a reader we just call

DTOParser parser = DTOParserFactory.GetParser(typeof(PersonDTO))

and we’ll get an instance of the DTOParser_Person class.  Here’s what our DAL classes will look like.

 

PersonDb

Once again employing the principle of thinking first about how we want to consume our code then writing code to that target, we’re going to write our PersonDb first, then we’ll write our DALBase.  The PersonDb class will need to use DALBase methods for things like creating SqlCommand objects and getting a list of PersonDTOs. Once we see how we want to use these things in PersonDb, we’ll have a better idea of how we want DALBase to work.

First let’s write the GetPersonByPersonGuid() method. Pulling data from a database, then populating a DTO with that data and returning it takes quite a bit of code. But if we think about it, most of that code is duplicated for each data access method that we write.  If we extract out only the things that change for each method we get the following list:

• We’re going to use sprocs on the SQL Server side so the first thing we need to do is get an SqlCommand object for the named sproc.
• Next we’ll need to add any parameters and set their values. 
• The last thing we need to do is run the command and get back the desired return type (either a DTO or List<DTO>) populated with the data. 

These are the only things that really change.  What sproc we’re calling, what parameters we need to add, and what the return type is.  So we’re going to write DALBase helper methods that will enable us to do each one of these tasks with a single line of code.  The resulting GetPersonByPersonGuid() code will look like this:

    SqlCommand command = GetDbSprocCommand("Person_GetByPersonGuid");

    command.Parameters.Add(CreateParameter("@PersonGuid", PersonGuid));

    return GetSingleDTO<PersonDTO>(command);

If we need a GetPersonByEmail() method, we can use the above code with minor modifications.  The things that change are just the sproc name and the parameter.  The modified code looks like:

    SqlCommand command = GetDbSprocCommand("Person_GetByEmail");

    command.Parameters.Add(CreateParameter("@Email", Email));

    return GetSingleDTO<PersonDTO>(command);

Then if we need a GetAll() method that returns all person records, we can do that easily too.  This time the sproc name, the parameters (this time there aren’t any), and the return type all change.

    SqlCommand command = GetDbSprocCommand("Person_GetAll");

    return GetDTOList<PersonDTO>(command);

So with a few helper methods we can put together a simple and easy to maintain PersonDb class.  If you were watching closely you noticed a couple of requirements for DALBase that emerged while writing the PersonDb code.  First, we want to use GetSingleDTO() and GetDTOList() methods but we need to be able to tell them to return specific types of DTOs, like PersonDTO.  Therefore these will need to be generic methods that take the DTO as the type parameter, such as GetSingleDTO<PersonDTO>().

Second, we used the same CreateParameter() method to create a string parameter and a Guid parameter.  So we’ll have to do a little polymorphism and write CreateParameter() overload methods for each type of parameter that we want to create. 

We’ll get into the details next time when we finish up our DAL by coding up the DALBase, the DTOParser classes, and the DTOParserFactory.  BTW, next post is when we’ll get into the real performance oriented code. For data access we’ll use ordinals to pull data from the reader in the most efficient way possible and then use the SqlDataReader’s strongly typed Get methods to do a null check and write the data values to our DTO all without casting the value to Object. For now, here’s the full PersonDb class complete with a SavePerson() method that takes a PersonDTO as it’s only parameter. 

public class PersonDb:DALBase

   {

       // GetPersonByPersonGuid

       public static PersonDTO GetPersonByPersonGuid(Guid PersonGuid)

       {

           SqlCommand command = GetDbSprocCommand("Person_GetByPersonGuid");

           command.Parameters.Add(CreateParameter("@PersonGuid", PersonGuid));

           return GetSingleDTO<PersonDTO>(ref command);

       }

 

 

       // GetPersonByEmail

       public static PersonDTO GetPersonByEmail(string email)

       {

           SqlCommand command = GetDbSprocCommand("Person_GetByEmail");

           command.Parameters.Add(CreateParameter("@Email", email, 100));

           return GetSingleDTO<PersonDTO>(ref command);

       }

 

       // GetAll

       public static List<PersonDTO> GetAll()

       {

           SqlCommand command = GetDbSprocCommand("Person_GetAll");

           return GetDTOList<PersonDTO>(ref command);

       }

 

       // SavePerson

       public static void SavePerson(ref PersonDTO person)

       {

           // The sproc will handle both inserts and updates.  We

           // just need to return the appropriate person guid.  If

           // this is a new person then we return the NewPersonGuid.

           // If this is an update we just return the PersonGuid.

           bool isNewRecord = false;

           if (person.PersonGuid.Equals(Common.DTOBase.Guid_NullValue)){isNewRecord=true;}

 

           // Create the command and parameters. When creating parameters

           // we don't need to check for null values. The CreateParameter

           // method will handle that for us and will create null parameters

           // for any DTO members that match the DTOBase.NullValue for

           // that member's data type.

           SqlCommand command = GetDbSprocCommand("Person_Save");

           command.Parameters.Add(CreateParameter("@PersonGuid", person.PersonGuid));

           command.Parameters.Add(CreateParameter("@Password", person.Password, 20));

           command.Parameters.Add(CreateParameter("@Name", person.Name, 100));

           command.Parameters.Add(CreateParameter("@Nickname", person.Nickname, 50));

           command.Parameters.Add(CreateParameter("@PhoneMobile", person.PhoneMobile, 25));

           command.Parameters.Add(CreateParameter("@PhoneHome", person.PhoneHome, 25));

           command.Parameters.Add(CreateParameter("@Email", person.Email, 100));

           command.Parameters.Add(CreateParameter("@ImAddress", person.ImAddress, 50));

           command.Parameters.Add(CreateParameter("@ImType", person.ImType));

           command.Parameters.Add(CreateParameter("@TimeZoneId", person.TimeZoneId));

           command.Parameters.Add(CreateParameter("@LanguageId", person.LanguageId));

           SqlParameter paramIsDuplicateEmail = CreateOutputParameter("@IsDuplicateEmail", SqlDbType.Bit);

           command.Parameters.Add(paramIsDuplicateEmail);

           SqlParameter paramNewPersonGuid = CreateOutputParameter("@NewPersonGuid", SqlDbType.UniqueIdentifier);

           command.Parameters.Add(paramNewPersonGuid);

 

           // Run the command.

           command.Connection.Open();

           command.ExecuteNonQuery();

           command.Connection.Close();          

 

           // Check for duplicate email.

           if ((bool)paramIsDuplicateEmail.Value) {throw new Common.Exceptions.DuplicateEmailException();}

 

           // If this is a new record, let's set the Guid so the object

           // will have it.

           if(isNewRecord) {person.PersonGuid = (Guid)paramNewPersonGuid.Value;}       

       }

   }