Wednesday, February 25, 2009

High Performance Data Access Layer Architecture Part 2

This is the second part of a post on an architecture that I use for high performance data access.  If you haven’t read part 1 yet, you can find it here: High Performance Data Access Layer Architecture Part 1. Below is a diagram of the DAL design that we’re using.

image

In part 1 we talked about overall DAL architecture and the use of DTOs for moving data in and out of the DAL.  We also covered the PersonDb class.  This time we’re going to look at the DALBase and how it provides plumbing to make the PersonDb and other EntityDb classes work.

The DALBase

Our PersonDb class contains all of our data access methods for getting and setting data for a Person entity. Last time we wrote PersonDb methods that look like

       // GetPersonByPersonGuid

       public static PersonDTO GetPersonByPersonGuid(Guid PersonGuid)

       {

           SqlCommand command = GetDbSprocCommand("Person_GetByPersonGuid");

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

           return GetSingleDTO<PersonDTO>(ref command);

       }

So now we need to write the DALBase methods required to make that code work.  We’re going to need a GetDbSprocCommand() method, some overloaded CreateParameter() methods for each type of parameter we need to create, and most important we’ll need the GetSingleDTO() and GetDTOList() generic methods which will take a command object and then parse out either a single DTO or a generic List of DTOs. I’m going to go through each part of DALBase, then at the end of this post I’ll list the complete class code.

Connection String

First we need to handle the connection string.  We’ll just create a property to encapsulate pulling the connection string from ConfigurationManager.  

// ConnectionString

protected static string ConnectionString

{

    get { return ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString; }

}

SqlConnection and Command Objects

Now we’ll create methods for getting a SQLConnection instance, a regular TSQL Command instance, and since we’ll be using sprocs, a sproc Command instance .  Notice that GetDbSprocCommand() is the method that we used in our PersonDb code.  Now that we’re writing it we know exactly what parameters it needs and what it’s return type should be.

// GetDbConnection

protected static SqlConnection GetDbConnection()

{

    return new SqlConnection(ConnectionString);

}

// GetDbSqlCommand

protected static SqlCommand GetDbSQLCommand(string sqlQuery)

{

    SqlCommand command = new SqlCommand();

    command.Connection = GetDbConnection();

    command.CommandType = CommandType.Text;

    command.CommandText = sqlQuery;

    return command;

}

 

// GetDbSprocCommand

protected static SqlCommand GetDbSprocCommand(string sprocName)

{

    SqlCommand command = new SqlCommand(sprocName);

    command.Connection = GetDbConnection();

    command.CommandType = CommandType.StoredProcedure;

    return command;

}

Creating SqlParameters

Now that we have a method for creating a sproc Command, we need to be able to create parameters. When we wrote our PersonDb code, we set a target for how we want the CreateParameter() methods to work.  We want a single CreateParameter() method that can be used to add int, guid, string, and datetime parameters; and we want it to have simple syntax that works on a single line as shown below.

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

This should be possible.  Our CreateParameter() method will take at least two parameters.  The first will be the SqlParameter name which will always be a string.  The second will be the value of the SqlParameter, and the type of this paramter tells us what type of SqlParameter we need to create.  So, if we have a method signature that looks like CreateParameter(string name, Guid value), we know that we need to return a SqlParameter of type SqlDbType.UniqueIdentifier.  If we have a method signature that looks like CreateParameter(string name, int value), we know that we need to return a parameter of type SqlDbType.Int. Here is what our CreateParameter() overloads are going to look like.  Note we added an extra "size” parameter to the signature for the overload that creates an NVarchar SqlParameter. 

// CreateParameter - uniqueidentifier

protected static SqlParameter CreateParameter(string name, Guid value)

{

    if (value.Equals(Common.DTOBase.Guid_NullValue))

    {

        return CreateNullParameter(name, SqlDbType.UniqueIdentifier);

    }

    else

    {

        SqlParameter parameter = new SqlParameter();

        parameter.SqlDbType = SqlDbType.UniqueIdentifier;

        parameter.ParameterName = name;

        parameter.Value = value;

        parameter.Direction = ParameterDirection.Input;

        return parameter;

    }

}

 

// CreateParameter - nvarchar

protected static SqlParameter CreateParameter(string name, string value, int size)

{

    if (value == Common.DTOBase.String_NullValue)

    {

        return CreateNullParameter(name, SqlDbType.NVarChar);

    }

    else

    {

        SqlParameter parameter = new SqlParameter();

        parameter.SqlDbType = SqlDbType.NVarChar;

        parameter.Size = size;

        parameter.ParameterName = name;

        parameter.Value = value;

        parameter.Direction = ParameterDirection.Input;

        return parameter;

    }

}

So, we make a method like the ones above for each SqlParameter type that we need to create (datetime, int, etc.).  We also need to have a way to create SqlParameters with a null value, and output parameters. The easiest way I’ve found to do this is to use the techniques above to make additional CreateOutputParamer() methods and a CreateNullParameter() method.  The code for these can be found in the complete class listing at the end of this post.

GetSingleDTO()

Now we get to something a little more interesting. We’ve coded the helper methods needed to create a command object and add it’s associated parameters. Now we need to take that command object and get a usable piece of data out of it, our PersonDTO. That’s the purpose of GetSingleDTO().  GetSingleDTO takes a command object, opens its connection, calls ExecuteReader() on it, tries to parse a single DTO from the reader, then returns it.  Since we don’t want to rewrite this code for every type of DTO, we made GetSingleDTO a generic method that accepts any type that inherits from DTOBase.    You’ll notice that GetSingleDTO() encapsulates logic that would otherwise be repeated in every data access method that we write.  Things like opening the connection, getting a reader, checking if the reader has rows are things that we do the same way every time we need to get data.  So why write them over and over?  We can just encapsulate them all here in our GetSingleDTO() method. In fact, we really don’t have a choice. We have to encapsulate them all here because we’ve already written our PersonDb data access code and it requires that GetSingleDTO() take a command object as a parameter and return a DTO of the requested type. We’re coding our method to that target.

// 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 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;

}

The meat of this method takes place after we confirm that the reader really does have rows.  At that point we call Read() and create a DTOParser for the the type that we want to return.  The next 2 steps are key. First we call parser.PopulateOrdinals(). This method gets and saves the ordinals for each data field that we need to pull from the reader.  An ordinal is basically an index number that tells you where to find a specific data field value in the reader. Getting values by ordinal (index) is much more efficient than getting values by data field name.  More on that next week. After we populate the ordinals, we are then able to use the parser to populate the DTO object that our method will return.

You’ll notice that the code above does not contain any logic for parsing an actual DTO out of the reader.  That’s because that logic changes depending on the type of DTO that we’re returning.  So, we encapsulate what changes in a DTOParser object.  We then just use the DTOParser to get the type of DTO that we’re looking for and we let the DTOParser worry about how the actual parsing is done.

The GetDTOList() method uses the same design as above, it just returns a generic list of DTOs instead of a single DTO.  The code for GetDTOList() is included as part of the full class listing at the end of this post.

Summary

So now we’ve covered the DALBase class.  I was hoping to get into the DTOParsers but we’ll have to save that until next week.  So next time we’ll get into the real performance magic where we take that reader that we passed to the DTOParser, and use ordinals and strongly typed get methods to pull data out of the reader in the most efficient way possible with no casts to Object.  For now, here’s the full listing for DALBase.

public abstract class DALBase

{

    // ConnectionString

    protected static string ConnectionStrin
    {

        get { return ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString; }

    }

 

 

    // GetDbSqlCommand
    protected static SqlCommand GetDbSQLCommand(string sqlQuery)

    {

        SqlCommand command = new SqlCommand();

        command.Connection = GetDbConnection();

        command.CommandType = CommandType.Text;

        command.CommandText = sqlQuery;

        return command;

    }

 

 

    // GetDbConnection

    protected static SqlConnection GetDbConnection()

    {

        return new SqlConnection(ConnectionString);

    }

 

 

    // GetDbSprocCommand

    protected static SqlCommand GetDbSprocCommand(string sprocName)

    {

        SqlCommand command = new SqlCommand(sprocName);

        command.Connection = GetDbConnection();

        command.CommandType = CommandType.StoredProcedure;

        return command;

    }

 

    // CreateNullParameter

    protected static SqlParameter CreateNullParameter(string name, SqlDbType paramType)

    {

        SqlParameter parameter = new SqlParameter();

        parameter.SqlDbType = paramType;

        parameter.ParameterName = name;

        parameter.Value = null;

        parameter.Direction = ParameterDirection.Input;

        return parameter;

    }

 

 

    // CreateNullParameter - with size for nvarchars

    protected static SqlParameter CreateNullParameter(string name, SqlDbType paramType, int size)

    {

        SqlParameter parameter = new SqlParameter();

        parameter.SqlDbType = paramType;

        parameter.ParameterName = name;

        parameter.Size = size;

        parameter.Value = null;

        parameter.Direction = ParameterDirection.Input;

        return parameter;

    }

 

 

    // CreateOutputParameter

    protected static SqlParameter CreateOutputParameter(string name, SqlDbType paramType)

    {

        SqlParameter parameter = new SqlParameter();

        parameter.SqlDbType = paramType;

        parameter.ParameterName = name;

        parameter.Direction = ParameterDirection.Output;

        return parameter;

    }

 

 

    // CreateOuputParameter - with size for nvarchars

    protected static SqlParameter CreateOutputParameter(string name, SqlDbType paramType, int size)

    {

        SqlParameter parameter = new SqlParameter();

        parameter.SqlDbType = paramType;

        parameter.Size = size;

        parameter.ParameterName = name;

        parameter.Direction = ParameterDirection.Output;

        return parameter;

    }

 

 

    // CreateParameter - uniqueidentifier

    protected static SqlParameter CreateParameter(string name, Guid value)

    {

        if (value.Equals(Common.DTOBase.Guid_NullValue))

        {

            // If value is null then create a null parameter

            return CreateNullParameter(name, SqlDbType.UniqueIdentifier);

        }

        else

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = SqlDbType.UniqueIdentifier;

            parameter.ParameterName = name;

            parameter.Value = value;

            parameter.Direction = ParameterDirection.Input;

            return parameter;

        }

    }

 

 

    // CreateParameter - int

    protected static SqlParameter CreateParameter(string name, int value)

    {

        if (value == Common.DTOBase.Int_NullValue)

        {

            // If value is null then create a null parameter

            return CreateNullParameter(name, SqlDbType.Int);

        }

        else

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = SqlDbType.Int;

            parameter.ParameterName = name;

            parameter.Value = value;

            parameter.Direction = ParameterDirection.Input;

            return parameter;

        }

    }

 

 

    // CreateParameter - datetime

    protected static SqlParameter CreateParameter(string name, DateTime value)

    {

        if (value == Common.DTOBase.DateTime_NullValue)

        {

            // If value is null then create a null parameter

            return CreateNullParameter(name, SqlDbType.DateTime);

        }

        else

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = SqlDbType.DateTime;

            parameter.ParameterName = name;

            parameter.Value = value;

            parameter.Direction = ParameterDirection.Input;

            return parameter;

        }

    }

 

 

    // CreateParameter - nvarchar

    protected static SqlParameter CreateParameter(string name, string value, int size)

    {

        if (value == Common.DTOBase.String_NullValue)

        {

            // If value is null then create a null parameter

            return CreateNullParameter(name, SqlDbType.NVarChar);

        }

        else

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = SqlDbType.NVarChar;

            parameter.Size = size;

            parameter.ParameterName = name;

            parameter.Value = value;

            parameter.Direction = ParameterDirection.Input;

            return parameter;

        }

    }

 

 

    // 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 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;

    }

 

 

    // GetDTOList

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

    {

        List<T> dtoList = new List<T>();

        try

        {

            command.Connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)

            {

                // Get a parser for this DTO type and populate

                // the ordinals.

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

                parser.PopulateOrdinals(reader);

                // Use the parser to build our list of DTOs.

                while (reader.Read())

                {

                    T dto = null;

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

                    dtoList.Add(dto);

                }

                reader.Close();

            }

            else

            {

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

                dtoList = null;

            }

        }

        catch (Exception e)

        {

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

        }

        finally

        {

            command.Connection.Close();

            command.Connection.Dispose();

        }

        return dtoList;

    }

}

Wednesday, February 18, 2009

Do Test Driven Development Without the Tests

I’m taking a quick break from the High Performance Data Access Layer Posts to utter what some people will consider blasphemy about TDD. Test Driven Development is creating quite a buzz these days.  One thing that I find interesting about Test Driven Development is that it’s not really about tests. If you have any doubts, listen to the TDD pundits.  You’ll notice that many of them are trying to rename TDD to Behavior Driven Design.  Why would they do that?  Because a lot of developers get hung up on the word “Test”.  They assume that TDD is primarily a coding methodology that produces code with good unit tests and high test coverage.  That’s missing the point.  TDD is a design methodology that forces you to write better code by first thinking about how that code is going to be consumed, and doing that doesn’t require tests.

For anyone who isn’t familiar with TDD, the idea is simple.  Before you write any code for your application, you first write an automated unit test.  If you want to write a C# method that returns the highest number from an array, you first write a unit test that creates an array of numbers, passes it to your method (which hasn’t been written yet), and then checks the value that your method returns to make sure it is the highest number.  Since you haven’t written the method yet, the code fails.  This failed test then drives your development of the method. When you get the method working correctly, the test will pass. The idea is that every piece of code you write is driven by a failing test.  This practice results in very testable code, high test coverage, and it prevents you from writing a lot of extraneous code that is never used.  But it also does something else that is even more important. By making you write tests that consume your code first, TDD forces you to think about how your code is going to be consumed on a very fine grained level.  This is the real benefit of Test Driven Development, write consuming code first because it forces you to think about how your code will be used and will drive better code design.

That makes perfect sense to me.  In fact I realized that I’d been doing TDD for years before I ever wrote a single unit test.  If you’ve read any of my other posts, you’ve probably heard me say things like “Start at the end” or “the principle of thinking first about how we want to consume our code then writing code to that target”.  That is really the essence of TDD, write your consuming code first.  That doesn’t require the consuming code to be automated tests.

Here’s a quick example.  In my post on High Performance Data Access we had a situation where we needed to create a PersonDb class that contained all of the data access methods for person data like GetPersonByGuid(), and we also needed to make a DALBase class that encapsulated our repeated data access logic like getting a connection string, getting a command object, etc.  The normal way to approach that problem is to write the DALBase first, then write the PersonDb since PersonDb will be using the methods provided by DALBase.  That approach would work, but it’s also how we wind up with API code that’s not as efficient as it could be.  My approach was to write the PersonDb class first.  This forced me to start by thinking about what code I wanted to keep out of PersonDb and what code should really go in my data access methods. What changes between each data access method?  I came up with 3 things: the command name; the parameters list; and the return type. Since those are the only things that change, those are the only things that should go in my data access methods. Everything else that doesn’t change should be encapsulated behind DALBase.  The resulting data access code looked like this PersonDb.GetPersonByPersonGuid() method:

       // GetPersonByPersonGuid

       public static PersonDTO GetPersonByPersonGuid(Guid PersonGuid)

       {

           SqlCommand command = GetDbSprocCommand("Person_GetByPersonGuid");

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

           return GetSingleDTO<PersonDTO>(command);

       }

That is some simple data access code.  Now if I had started by writing my DALBase first, the code I wrote would have worked fine.  After all, I have written a DAL before.  But, I don’t think it would have been this clean.

So, my conclusion is that TDD is a great DESIGN methodology, and when possible I think that writing automated tests first and using a full TDD process is probably the best way to do it.  But, if you’re in an environment where that’s not possible, remember that the core idea of TDD is simply to write your consuming code first.  This is a practice that can be used in any environment, even if you don’t use any automated unit tests.  Try it and I think that you’ll find yourself addressing design problems earlier, and writing much cleaner code.

Monday, February 16, 2009

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.

  image

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.

image 

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;}       

       }

   }