Post
Ask Question

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?
 

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
  1. public class DoSomething  
  2. {  
  3.     private int m_number;  
  4.     public int GetTheFancyNumber()  
  5.     {  
  6.         int theNumber = m_number;  
  7.         // DO LOTS OF CRAZY STUFF TO theNumber HERE.  
  8.         return theNumber;  
  9.     }  
  10. }  
  11. public class DoSomethingElse  
  12. {  
  13.     private int m_number;  
  14.     public int GetTheFancyNumber()  
  15.     {  
  16.         int theNumber = m_number;  
  17.         // DO LOTS OF CRAZY STUFF TO theNumber HERE.  
  18.         return theNumber;  
  19.     }  
  20. }  
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:
  1. public abstract class DoSometingBase  
  2. {  
  3.     protected abstract int GetTheNumber();  
  4.     public int GetTheFancyNumber()  
  5.     {  
  6.         int theNumber = GetTheNumber();  
  7.         // LOTS LOTS OF CRAZY STUFF TO theNumber HERE.  
  8.         return theNumber;  
  9.     }  
  10. }  
Then when we create the parent classes, we use the logic encapsulated by the template and implement the things that change (getting the number):
  1. public class DoSomethingElse : DoSometingBase  
  2. {  
  3.     private int m_number;  
  4.     protected override int GetTheNumber()  
  5.     {  
  6.         return m_number;  
  7.     }  
  8. }  
  9. public class DoSomething : DoSometingBase  
  10. {  
  11.     private int m_number;  
  12.     protected override int GetTheNumber()  
  13.     {  
  14.         return m_number;  
  15.     }  
  16. }  
So, that's the Template pattern in a nut-shell.  Where it really shines is when we couple it with generics.
 

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.
  1. CREATE TABLE [tblPerson] (  
  2.  [PersonID] [int] IDENTITY (1, 1) NOT NULL ,  
  3.  [FirstName] [nvarchar] (50),  
  4.  [LastName] [nvarchar] (50),  
  5.  [Email] [nvarchar] (50) ,  
  6.  CONSTRAINT [PK_tblPerson] PRIMARY KEY  CLUSTERED   
  7.  (  
  8.   [PersonID]  
  9.  )  ON [PRIMARY]   
  10. ) ON [PRIMARY]  
So now we have this exciting table to hold our data. :
 
generics1.gif 
 
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).
 
generics2.gif 
 
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.
 
generics3.gif
  1. abstract class MapperBase<T>  
  2. {  
  3.     protected abstract T Map(IDataRecord record);  
  4.     public Collection<T> MapAll(IDataReader reader)  
  5.     {  
  6.         Collection<T> collection = new Collection<T>();  
  7.         while (reader.Read())  
  8.         {  
  9.             try  
  10.             {  
  11.                 collection.Add(Map(reader));  
  12.             }  
  13.             catch  
  14.             {  
  15.                 throw;  
  16.                 // NOTE:  
  17.                 // consider handling exeption here instead of re-throwing  
  18.                 // if graceful recovery can be accomplished  
  19.             }  
  20.         }  
  21.         return collection;  
  22.     }  
  23. }  
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. 
 
generics4.gif
  1. class PersonMapper: MapperBase<Person>  
  2. {  
  3.     protected override Person Map(IDataRecord record)  
  4.     {  
  5.         try  
  6.         {  
  7.             Person p = new Person();  
  8.             p.Id = (DBNull.Value == record["PersonID"]) ?  
  9.                 0 : (int)record["PersonID"];  
  10.             p.FirstName = (DBNull.Value == record["FirstName"]) ?  
  11.                 string.Empty : (string)record["FirstName"];  
  12.             p.LastName = (DBNull.Value == record["LastName"]) ?  
  13.                 string.Empty : (string)record["LastName"];  
  14.             p.Email = (DBNull.Value == record["Email"]) ?  
  15.                 string.Empty : (string)record["Email"];  
  16.             return p;  
  17.         }  
  18.         catch  
  19.         {  
  20.             throw;  
  21.             // NOTE:  
  22.             // consider handling exeption here instead of re-throwing  
  23.             // if graceful recovery can be accomplished  
  24.         }  
  25.     }  
  26. }  
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.
 

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:
  1. Getting the connection.
  2. Getting the Sql Command
  3. Getting the Sql Command Type
  4. Getting the mapper (from part II)
  1. IDbConnection GetConnection();  
  2. string CommandText { get; }  
  3. CommandType CommandType { get; }  
  4. Collection<IDataParameter> GetParameters(IDbCommand command);  
  5. 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.
  1. public Collection<T> Execute()  
  2. {  
  3.     Collection<T> collection = new Collection<T>();  
  4.     using (IDbConnection connection = GetConnection())  
  5.     {  
  6.         IDbCommand command = connection.CreateCommand();  
  7.         command.Connection = connection;  
  8.         command.CommandText = this.CommandText;  
  9.         command.CommandType = this.CommandType;  
  10.         foreach(IDataParameter param in this.GetParameters(command))  
  11.             command.Parameters.Add(param);  
  12.         try  
  13.         {  
  14.             connection.Open();  
  15.             using (IDataReader reader = command.ExecuteReader())  
  16.             {  
  17.                 try  
  18.                 {  
  19.                     MapperBase<T> mapper = GetMapper();  
  20.                     collection = mapper.MapAll(reader);  
  21.                     return collection;  
  22.                 }  
  23.                 catch  
  24.                 {  
  25.                     throw;  
  26.                     // NOTE:  
  27.                     // consider handling exeption here  
  28.                     // instead of re-throwing  
  29.                     // if graceful recovery can be accomplished  
  30.                 }  
  31.                 finally  
  32.                 {  
  33.                     reader.Close();  
  34.                 }  
  35.             }  
  36.         }  
  37.         catch  
  38.         {  
  39.             throw;  
  40.             // NOTE:  
  41.             // consider handling exeption here instead of re-throwing  
  42.             // if graceful recovery can be accomplished  
  43.         }  
  44.         finally  
  45.         {  
  46.             connection.Close();  
  47.         }  
  48.     }  
  49. }  
So, here is the class we end up with:
 
generics5.gif
 
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>.
  1. abstract class ObjectReaderWithConnection<T> : ObjectReaderBase<T>  
  2. {  
  3.     private static string m_connectionString =   
  4.          @"Data Source=DATA_SOURCE_NAME;Initial Catalog=Test;Integrated Security=True";  
  5.     protected override System.Data.IDbConnection GetConnection()  
  6.     {  
  7.         // update to get your connection here  
  8.         IDbConnection connection = new SqlConnection(m_connectionString);  
  9.         return connection;  
  10.     }  
  11. }  
So we have:
 
generics6.gif
 
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.
  1. class PersonReader: ObjectReaderWithConnection<Person>  
  2. {  
  3.     protected override string CommandText  
  4.     {  
  5.         get { return "SELECT PersonID, FirstName, LastName, Email FROM tblPerson"; }  
  6.     }  
  7.     protected override CommandType CommandType  
  8.     {  
  9.         get { return System.Data.CommandType.Text; }  
  10.     }  
  11.     protected override Collection<IDataParameter> GetParameters(IDbCommand command)  
  12.     {  
  13.         Collection<IDataParameter> collection = new Collection<IDataParameter>();  
  14.         return collection;  
  15.         //// USE THIS IF YOU ACTUALLY HAVE PARAMETERS  
  16.         //IDataParameter param1 = command.CreateParameter();  
  17.         //param1.ParameterName = "paramName 1"; // put parameter name here  
  18.         //param1.Value = 5; // put value here;  
  19.         //collection.Add(param1);  
  20.         //return collection;    
  21.     }  
  22.     protected override MapperBase<Person> GetMapper()  
  23.     {  
  24.         MapperBase<Person> mapper = new PersonMapper();  
  25.         return mapper;  
  26.     }  
  27. }  
So now we have:
 
generics7.gif
 
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.
 

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:
  1. static voidMain(string[] args)  
  2. {  
  3.     PersonReader reader = new PersonReader();  
  4.     Collection<Person> people = reader.Execute();  
  5.     foreach (Person p in people)  
  6.         Console.WriteLine(string.Format("{0}, {1}: {2}",   
  7.             p.LastName, p.FirstName, p.Email));  
  8.     Console.ReadLine();  
  9. }  

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.
 
Until next time,

Happy coding


Similar Articles