Understanding the problem
Step 1 :- Creating a base class
Step 2 :- Make it Generic
Step 3:- Abstract Protected and Virtual
Step 4:- Making some Methods private
Step 5:- Inherit and create concrete classes
You just ended up with Template Design pattern
Six other uses of Template design pattern
Design patterns are in every code with different forms. Lot of times we unknowingly use these patterns. But knowing them upfront avoids you to reinvent the wheel.
Via this article I would like to share one such experience where we successfully used Template pattern for reusing ADO.NET basic code.
The best way to learn design pattern in by doing a project I would encourage you to read this article series Learn C# design pattern step by step with a project.
So let us understand the problem. If you look at the below ADO.NET data access layer code its adding “Customer” object to the database. If you visualize the code we are doing the following steps:-
- Opening an ADO.NET connection and providing connection string.
- Creating Command, Writing SQL and firingthe SQL.
- Once the SQL is executed you we are closing the connection.
public class CustomerDataLayer
public void Add(Customer obj)
//Step 1 :- Open connection
SqlConnection objConnection = new SqlConnection(@"Connectionstring");
SqlCommand objCommand = new SqlCommand();
objCommand.Connection = objConnection;
//Step 2:- Execute SQL
objCommand.CommandText = "insert into tblCustomer values('"
+ obj.CustomerName + "','"
+ obj.CustomerCode + "')";
//Step 3:- Close connection
If you analyze following things are common in the above code:-
- The SEQUENCE OF STEPS i.e. Open connection, Execute SQL and close connection remain same for all ADO.NET calls.
- The code for opening and closing the connection object remains same for all entities. The only thingthat changes is the SQL statement.
So now the question is how to reuse the connection objects and the sequence of steps when we create data access layers for other objects like “Customer” , “Supplier” etc.
So first thing which comes logically to my mind is create a base class with the above discussed common things i.e. Sequence and common Connection code for opening and closing the connection.
Above is a simple base class which has four methods “Open”, “ExecuteSql” , “Close” and “Execute”.
In “Open” and “Close” methods we have the common code for creating connection object , opening it and closing it.
In the “ExecuteSql” method we will be passing the “Customer” object which will help us do CRUD operation.
In “Execute” methods we have the sequence of calling the above three methods. No one should be able to change this sequence because this sequence is fixed.
public void Open()
objConnection = new SqlConnection(@"Connectionstring");
objCommand = new SqlCommand();
objCommand.Connection = objConnection;
public void Close()
Think logically what if you want to add supplier, accounts and other different types of entities. You would need more such 10 base classes which is definitely not a good design.
So to avoid multiple base classes for multiple entitytypes ,lets make the base class “Generic”. In case you are new to generics and if you think generics means just generic collection , you should watch this C# generics video once.
You can see in the below code the base class is now a generic and can be consumed with any entity type. We do not need to create different base classes for different types.
Now the above class defines the sequence of steps and common connection code but the SQL is different for different entities. That means this class is a half defined class. So the great way to represent such kind of class is by making this class “ABSTRACT” and the “ExecuteSql” method “ABSTRACT METHOD”.
So by making this class abstract no one can create object of this class and by defining the “ExecuteSql” method abstract the child classes have to compulsorily define this method in the below child classes.
In order that child classes are able write SQL and execute them we need to expose the connection and command object. So you can see the connection and command objectsare made protected.
The “Open” and “Close” methods are to be called only via “Execute” method and that also in a particular sequence and order. So making these methods public will be harmful as they can be called without following that sequence. So making these methods private would prevent such kind of calls made outside the sequence.
Now that our BASE ABSTRACT GENERIC class is complete we can inherit from the half defined class and attach the domain class with the same.
So if you want to write CRUD operation for customer entity, you will inherit and attach the class to the generic abstract class and write the CRUD logic.
public class CustomerDal : AbstractDal<customer>
public override void ExecuteSql(Customer obj)
objCommand.CommandText = "insert into tblCustomer values'("
+ obj.CustomerName + "')";
And same we can do for supplier class and any other type entity class.
public class SupplierDal : AbstractDal<supplier>
public override void ExecuteSql(Supplier obj)
objCommand.CommandText = "insert into tblSupplier values'("
+ obj.SupplierName + "')";
Template pattern belongs to the behavioral pattern category. Template pattern defines a main process template and this main process template calls sub-processes in a fixed sequential manner( Open, Execute and Close). This sequence cannot be altered.
Later the sub processes can be extended via inheritance to create a different type of process.
Four years back I blogged about six other useful scenarios of template design pattern. Do read more about the same from here http://www.codeproject.com/Articles/307452/common-use-of-Template-Design-pattern-Design-pat
Scenario 1: Flexible extendable generalized specialized user interfaces
Scenario 2: ASP.NET page life cycle
Scenario 3: Code generators
Scenario 4: XML parser
Scenario 5: Validation in business components
Scenario 6: Customizable logging utility
Below is a nice Design Pattern youtube video which explains step by step how to use Design pattern in C# projects.