An Elegant C# Data Access Layer using the Template Pattern and Generics
The GOF Template pattern coupled with .NET 2.0 Framework generics provides an awesome synergistic alliance. This article demonstrates how to drastically reduce the amount of code required in building a data access layer. Less code to debug... less code to break... less code to maintain... what could be better?
Part I: Introduction to the Template Pattern
To get started, let's talk about the GOF Template pattern. As with all GOF patterns, its primary purpose is to separate out what changes in your code from what does not change. The Template pattern deals with repetitive coding within a class. If you find yourself coding the same thing over-and-over (and over), you can get rid of the repetition of code using the Template pattern.
Here is an example. A perfect opportunity is if you have a ton of classes where you are doing similar logic as in GetTheFancyNumber() below
public class DoSomething
{
private int m_number;
public int GetTheFancyNumber()
{
int theNumber = m_number;
// DO LOTS OF CRAZY STUFF TO theNumber HERE.
return theNumber;
}
}
public class DoSomethingElse
{
private int m_number;
public int GetTheFancyNumber()
{
int theNumber = m_number;
// DO LOTS OF CRAZY STUFF TO theNumber HERE.
return theNumber;
}
}
If the logic is similar in the classes and you can identify what changes, you can encapsulate it. You take the code shared by the classes and put it in a base class and make the parent class responsible for what changes. In this example we would pub the "GetTheFancyNumber()" method in a base class and encapsulate setting the "theNumber" variable (the first line of the method), forcing the parent classes to take care of it.
This would be our Template:
public abstract class DoSometingBase
{
protected abstract int GetTheNumber();
public int GetTheFancyNumber()
{
int theNumber = GetTheNumber();
// LOTS LOTS OF CRAZY STUFF TO theNumber HERE.
return theNumber;
}
}
Then when we create the parent classes, we use the logic encapsulated by the template and implement the things that change (getting the number):
public class DoSomethingElse : DoSometingBase
{
private int m_number;
protected override int GetTheNumber()
{
return m_number;
}
}
public class DoSomething : DoSometingBase
{
private int m_number;
protected override int GetTheNumber()
{
return m_number;
}
}
So, that's the Template pattern in a nut-shell. Where it really shines is when we couple it with generics.
Part II. Template Pattern + Generics = Mapping Synergy
Where is the one place where we all do the same thing over-and-over (and over) again? You guess it! – accessing a database and getting objects built from the data. This is where we'll implement the Template pattern to create an elegant DAL (Data Access Layer).
First, let's build a simple table to use in this example that will hold some data for a person.
CREATE TABLE [tblPerson] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (50),
[LastName] [nvarchar] (50),
[Email] [nvarchar] (50) ,
CONSTRAINT [PK_tblPerson] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
) ON [PRIMARY]
So now we have this exciting table to hold our data. :
Now, we will build a class to hold the data (the code is in the project files – I won't bore you with it here).
There are two things we do repeatedly when accessing data. First we have to access the database, issue a command, and get the results. Second, we have to map those results to our objects. Both of these steps are candidates for templatizing (templatizing(?)... that's a real word, right?).
Let's look at the mapping part first because it's the easier one of the two. (The mapper pattern is an awesome enterprise pattern introduced in a book by Fowler.)
In this example we'll be coding to the IDataReader and IDataRecord interfaces in order to map our data to objects (IDataReader basically inherits from and iterates through IDataRecords).
I would always recommend coding to existing framework interfaces wherever possible so your code is more flexible (check out my article on interface based development here:
By coding to these interfaces, we are only tightly coupled to the database where we actually create the connection. This makes our code easily portable to any database so when someone says "Hey, this is great! Let's move it to MySQL!" you don't have to pull your hair out.
So anyways... back to the subject. Here is our mapper base object. The parent class will take care of the mapping specifics. The base class will take care of taking each mapped object and putting it in a collection. Because generics allow us to specify logic for any type the template can be used for all of our objects pulled from the DAL, not just the Person.
abstract class MapperBase<T>
{
protected abstract T Map(IDataRecord record);
public Collection<T> MapAll(IDataReader reader)
{
Collection<T> collection = new Collection<T>();
while (reader.Read())
{
try
{
collection.Add(Map(reader));
}
catch
{
throw;
// NOTE:
// consider handling exeption here instead of re-throwing
// if graceful recovery can be accomplished
}
}
return collection;
}
}
When we inherit the MapperBase to actually map to a Person object we only have to implement the specifics of creating an object and mapping the data from the IDataRecord to the object's properties.
class PersonMapper: MapperBase<Person>
{
protected override Person Map(IDataRecord record)
{
try
{
Person p = new Person();
p.Id = (DBNull.Value == record["PersonID"]) ?
0 : (int)record["PersonID"];
p.FirstName = (DBNull.Value == record["FirstName"]) ?
string.Empty : (string)record["FirstName"];
p.LastName = (DBNull.Value == record["LastName"]) ?
string.Empty : (string)record["LastName"];
p.Email = (DBNull.Value == record["Email"]) ?
string.Empty : (string)record["Email"];
return p;
}
catch
{
throw;
// NOTE:
// consider handling exeption here instead of re-throwing
// if graceful recovery can be accomplished
}
}
}
Can you see how easy it would be to create a new mapper for any class we have defined that pulls data from a table? If you see the possibilities, I imagine you are getting a bit excited right about now. Hold on though, we aren't even to the good part yet.
Part III. Template Pattern + Generics = DataAccess Synergy
The other thing we have to do is actually hit the database with a request and get the IDataReader back.
Here is what changes for each table we are hitting and each object we are creating:
- Getting the connection.
- Getting the Sql Command
- Getting the Sql Command Type
- Getting the mapper (from part II)
IDbConnection GetConnection();
string CommandText { get; }
CommandType CommandType { get; }
Collection<IDataParameter> GetParameters(IDbCommand command);
MapperBase<T> GetMapper();
Here is what stays the same which we'll encapsulate in an Execute() method that will return a collection of our objects.
public Collection<T> Execute()
{
Collection<T> collection = new Collection<T>();
using (IDbConnection connection = GetConnection())
{
IDbCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandText = this.CommandText;
command.CommandType = this.CommandType;
foreach(IDataParameter param in this.GetParameters(command))
command.Parameters.Add(param);
try
{
connection.Open();
using (IDataReader reader = command.ExecuteReader())
{
try
{
MapperBase<T> mapper = GetMapper();
collection = mapper.MapAll(reader);
return collection;
}
catch
{
throw;
// NOTE:
// consider handling exeption here
// instead of re-throwing
// if graceful recovery can be accomplished
}
finally
{
reader.Close();
}
}
}
catch
{
throw;
// NOTE:
// consider handling exeption here instead of re-throwing
// if graceful recovery can be accomplished
}
finally
{
connection.Close();
}
}
}
So, here is the class we end up with:
One of the things that will be the same for all of the objects inheriting from ObjectReaderBase is getting the connection: "GetConnection()". We'll put this in an abstract object implementing the ObjectReaderBase<T>.
abstract class ObjectReaderWithConnection<T> : ObjectReaderBase<T>
{
private static string m_connectionString =
@"Data Source=DATA_SOURCE_NAME;Initial Catalog=Test;Integrated Security=True";
protected override System.Data.IDbConnection GetConnection()
{
// update to get your connection here
IDbConnection connection = new SqlConnection(m_connectionString);
return connection;
}
}
So we have:
Here is the implementation of a PersonReader which handles things specific to reading a person from the database and building a collection of Person objects.
class PersonReader: ObjectReaderWithConnection<Person>
{
protected override string CommandText
{
get { return "SELECT PersonID, FirstName, LastName, Email FROM tblPerson"; }
}
protected override CommandType CommandType
{
get { return System.Data.CommandType.Text; }
}
protected override Collection<IDataParameter> GetParameters(IDbCommand command)
{
Collection<IDataParameter> collection = new Collection<IDataParameter>();
return collection;
//// USE THIS IF YOU ACTUALLY HAVE PARAMETERS
//IDataParameter param1 = command.CreateParameter();
//param1.ParameterName = "paramName 1"; // put parameter name here
//param1.Value = 5; // put value here;
//collection.Add(param1);
//return collection;
}
protected override MapperBase<Person> GetMapper()
{
MapperBase<Person> mapper = new PersonMapper();
return mapper;
}
}
So now we have:
Once you have the general concept, you will probably have many ideas on how you can tweak this base reader object for improvements or to fit your specific projects needs.
Also, you will have to create a different base object for each "type" of data access you'll be performing. For example you may need other abstract base classes for when you will be executing IDbCommand.ExecuteNonQuery() or IDbCommand.ExecuteScalar() requests against the database.
Part IV. Mapping Synergy + DataAccess Synergy = Elegant Code
The important thing to realize is that we have completely separated out normally repetitive code which will make for much easier maintenance because of a smaller code base. For each new table-class relationship you have in your project you only have to specify the things that are different. So, while this approach may look like more classes and complexity upfront, once you understand and implement this approach you'll actually save a lot of time as the number of "data holding" classes in your project grows and you'll have fewer places to look when debugging because of all the shared-code-synergy.
This is how we'll use our framework to retrieve everyone from the database and print them to the console:
static void
{
PersonReader reader = new PersonReader();
Collection<Person> people = reader.Execute();
foreach (Person p in people)
Console.WriteLine(string.Format("{0}, {1}: {2}",
p.LastName, p.FirstName, p.Email));
Console.ReadLine();
}
Part V. Conclusion.
So now you have the general idea you can use it as a base to build a DAL. There are many places to improve upon this approach such as using an identity map (yet another awesome Fowler pattern) to avoid unnecessary database hits or a better way to get the IDbConnection, such as using the strategy pattern and maybe using a factory, or how about a facade pattern to expose all of this functionality through one object.... anyways the possibilities are (almost) endless depending on your project.
You will most likely have to make changes to have this approach work for any specific project, but as you can see, it is possible to have less code to debug... less code to break... less code to maintain... what could be better? We can synergize our code by combining the Template Pattern with generics.
