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.
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
get { return ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString; }
}
{
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)
{
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
}
// return the DTO, it's either populated with data or null.
return dto;
}
// GetDTOList
{
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)
{
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
}
return dtoList;
}
}
