An Elegant C# Data Access Layer using the Template Pattern and Generics

Matthew Cochran

32 Comments

Join the conversation! Your thoughts help the community grow.

Neil Hattingh4y ago
Great article! Thank you for making the time to do this, much appreciated!
Richard Davison5y ago
Awesome, especially when you consider the new code Generator possibilities. all tables, procs and views can be easily retrieved into specific classes...... I am going to use this pattern for sure
Dinesh Gabhane6y ago
Good One. Thanks
Dan Hampleman13y ago
I had to declare ObjectReaderWithConnection and MapperBase as abstract public to get it to compile
snh snh14y ago
Hi, it is a good design pattern, But my question is, how we have to deal with this pattern when we want to retrive data on the basis of some parameters? And what would be the modifications required in this pattern for saving data?
arun prasath14y agoedited
Thanks
Joe Wolter15y ago
The ObjectReader and associated files work great. Is there any chance there is an ObjectWriterBase, ObjectWriterWithConnection similar to the object reader classes?
Joe Wolter15y agoedited
Problem Solved: <?xml version="1.0" encoding="utf-8" ?> <configuration> <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0"/> </startup> </configuration> Original Problem: I've created an application that generates my table classes using your approach by reading the sql I use to create all of my database tables in CreateDatabase.sql. Your framework works great in .net 2.0!!! But when I try to build my application in .Net 4 Client Profile it get this exception in the "public abstract class ObjectReaderBase<T>" in "using (IDbConnection connection = GetConnection())" "Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information." What am I missing? Thanks, Joe
Jon16y ago
Hi Matt, This is an excellent article, well done! It's such a common scenario in any application, and I can't tell you the number of times I've written a complete ExecuteReader() method for different domain objects. This solution really takes advantage of generics and the end result is indeed elegant. Good job! Jon
Christophe Mignot16y ago
Hello Mathew, Can you tell me how to implement when i want return multiple record set in one Stored procedure.... -Thanks and regards Chris
Shaun Sharples16y ago
I am currently trying this example out, but I have an issue where I am only getting one record back from the database where I know there is two, (I have debugged the command.CommandText attribute and put that into a SQL statement within SQL Server, and it does return the two records). Now I have attempted to debug the Reader.Execute, however at the point it attempts to do a MapAll, if I am debugging, the reader object returns zero records. However if I let it run its course, without debugging, at the end it returns one record. Being the first in the table. Thanks very much.
Biplab Banerjee16y ago
Hi Matthew, First of all thank you for writing such a wonderful article. I have considered using your approach for the DAL but I have a question. In section III at the bottom of the section you wrote "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." Now if I create other abstract base classes for different type of data access, how will the reader (e.g. PersonReader) inherit from all of them - I mean they can't inherit from multiple abstract base. Do we end up creating different classes on the reader instead of a single PersonReader? Appreciate your response on my query. Thanks, Biplab banerjee
Giorgio Bozio18y ago
Hi there, Nice article! Suppose I'm separating Dal and business logic layers on two different assemblies and let both depend on on a third assembly containing interfaces for abstracting dependencies. Where would the PersonMapper class be? It seems to me that the business logic layer would be the correct place but how would the class PersonMapper be instantiated by the dal class without breaking separation of the layers?
Prashant Devkota18y ago
Hi, I like this article. Taking basic ideas from the DataAccess, I have created a simple framework. I might be posting the framework soon. Pras
Prashant Devkota18y ago
Hi, I like this article. Taking basic ideas from the DataAccess, I have created a simple framework. I might be posting the framework soon. Pras
Nivitha Vasudevan18y ago
Hi, This article has been very useful to me. Can you please give an example to perform Insert, Update, Delete operations?
Tom Ash19y ago
Hello, I find this piece of code indeed an elegant but it lacks of usability or am I missing something? I need to parametrise Readers. Lets take PersonReader. I need to make method like: Person person = personReaderInstance.getPerson(personId); And I wish to have couple of methods like this per reader with different parameters. What then? I'm trying to walk around this issue but my conceptions violate the elegancy :/
- shirkdaddy18y ago
  Did anyone ever find a solution for parametrized readers? I'm running into the same issue. Every path I go down seems to contradict the elegance of this solution. I thought about providing another Execute method like: public T Execute(int objectId) { ... map here ...} but that doesn't seem right either (CommandText and parameters are set in the PersonReader). I suppose some flag could be set in ObjectReaderBase and some logic added to PersonReader to handle this, but bleck! Makes the elegance in the rest of the code seem pointless. Not to mention we'd have to create Execute methods for every combination of parameters needed to return a single object ref of any possible type...
- Lorenzo16y ago
  Hi i've just been reading this article:- http://www.c-sharpcorner.com/UploadFile/rmcochran/elegant_dal05212006130957PM/elegant_dal.aspx I was wondering if you ever found an answer to your question or not? I've noticed the same thing regarding the lack of getPerson(int Id) method etc... just wondering if you ever came up with anything. Thanks
csharpcorner20y ago
If the names of your data class properties are the same as the data table column names, you can use the following implementation of the Map method in the MapperBase class to avoid having to write manual mapping code for each data class. using System.Reflection; public abstract class MapperBase<T> where T : new() protected T Map(IDataRecord record) { T instance = new T(); string fieldName; PropertyInfo[] properties = typeof(T).GetProperties(); for (int i = 0; i < record.FieldCount; i++) { fieldName = record.GetName(i); foreach (PropertyInfo property in properties) { if (property.Name == fieldName) { property.SetValue(instance, record[i], null); } } } return instance; }
- Dennis Woodruff19y agoedited
  Hi rodrigod, Check that your class has a parameterless contructor: new() constraint - The type argument must expose a public, parameterless (default) constructor It works fine for me. D
- Ashok Guduru19y ago
  To get compile your code please add constructor constriant ( where T: new()) to other base classes also. to ObjectReaderWithConnection and to ObjectReaderBase like class ObjectReaderBase<T> where T : new() class ObjectReaderWithConnection<T> : ObjectReaderBase<T> where T : new() hope this helps Regards Ashok
- Matthew Cochran20y ago
  Nice. Thanks.
Stephen Long20y ago
Wouldn't it make sense to use a BindingList collection so that you can throw the events for when items are added/deleted/changed?
- Matthew Cochran20y ago
  Yeah, especially if you need to monitor the list. I guess it would depend on how you are monitoring the state of the objects. If they are self-monitoring, you may not want the extra overhead, if they are being monitored by another state-management object, the BindingList would certainly make life easier.
Christoffer20y ago
I have implemented your approach for reading data, and it works like a charm. But I have som thougts about updating and inserting (where you don't need generics) Does this look like a reasonable approach?: ---------- CODE ----------- class CustomerUpdater : DataUpdaterBase { private Customer _customer; private string _commandText; public CustomerUpdater(Customer customer) { _customer = customer; } protected override string CommandText { set { _commandText = value; } get { return _commandText; } } protected override System.Data.CommandType CommandType { get { return System.Data.CommandType.StoredProcedure; } } protected override Collection<IDataParameter> GetParameters(IDbCommand command) { Collection<IDataParameter> collection = new Collection<IDataParameter>(); IDataParameter custGuid = command.CreateParameter(); custGuid.ParameterName = "GUID"; custGuid.DbType = DbType.Guid; custGuid.Direction = ParameterDirection.Input; custGuid.Value = _customer.GUID; collection.Add(custGuid); return collection; } }
- Christoffer20y ago
  Thanks for the reply I kind a see what your getting at, but a static class cannot implement an interface or inherit from an abstract class as far as I know. What are your thoughts about this article: http://www.codeproject.com/useritems/BusinessObjectHelper.asp Kind regards Christoffer
- Christoffer20y ago
  Yes a singleton would of course solve it :). Anyways, great article, you left me greatly inspired. Keep up the good flow of articles Kind regards Christoffer
- Matthew Cochran20y ago
  Nice article. I could see that approach being perfect for some situations, maybe overkill for others. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p> You may want to look into combining either approach with code generation and see what you can come up with. Check out CodeSmith, it is a nice tool: http://codesmithtools.com/. Once you have your basic code base down, code generation can really expedite the creation of the data access layer and it makes it easy to quickly adapt your core classes to minor data structure changes.<o:p></o:p> and.. you are 100% correct. We can't implement interfaces with static classes (my fault for madly typing away without thinking). You can achive this result with a singleton (or just abandon all interest in playing nice with the memory and have a ton of instances lying about).
MrMM20y agoedited
Hi there, First I have to say, great article !! could you give me an example how to let this example communicate with MySQL And what would you have to do if you want to use stored procedures. thanks already Greets

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.

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.

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 voidMain(string[] args)
{
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();
}

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

An Elegant C# Data Access Layer using the Template Pattern and Generics

Introduction to the Template Pattern

Template Pattern + Generics = Mapping Synergy

Template Pattern + Generics = DataAccess Synergy

Mapping Synergy + DataAccess Synergy = Elegant Code

Conclusion