Reusing ADO.NET Code using Template Design Pattern

Table of Contents

• Introduction
• 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

Introduction

Design patterns are in every code with different forms. Lot of times, we unknowingly use these patterns. But knowing them upfront prevents you from reinventing 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.

Understanding the Problem

So let us understand the problem. If you look at the below ADO.NET data access layer code, it's 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 firing the SQL.
• Once the SQL is executed, we are closing the connection.

public class CustomerDataLayer
{
public void Add(Customer obj)
{

//Step 1 :- Open connection
SqlConnection  objConnection = new SqlConnection(@"Connectionstring");
objConnection.Open();
SqlCommand objCommand = new SqlCommand();
objCommand.Connection = objConnection;

//Step 2:- Execute SQL
objCommand.CommandText = "insert into tblCustomer values('" 
                          + obj.CustomerName + "','" 
                           + obj.CustomerCode + "')";
objCommand.ExecuteNonQuery();

//Step 3:- Close connection
objConnection.Close();      
}
}

If you analyze, the following things are common in the above code:

• The SEQUENCE OF STEPS, i.e., Open connection, Execute SQL and close connection remains the same for all ADO.NET calls.
• The code for opening and closing the connection object remains the same for all entities. The only thing that 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.

Step 1: Creating a Base Class

So the first thing which comes logically to my mind is to 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");
objConnection.Open();
objCommand = new SqlCommand();
objCommand.Connection = objConnection;
}
public void Close()
{
 objConnection.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.

Step 2: Make It Generic

So to avoid multiple base classes for multiple entity types, let's 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.

Step 3: Abstract Protected and Virtual

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 objects are made protected.

Step 4: Making Some Methods Private

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.

Step 5: Inherit and Create Concrete Classes

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 + "')";
objCommand.ExecuteNonQuery();
        }
    }
</customer>

And we can do the same for the 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 + "')";
objCommand.ExecuteNonQuery();
        }
    }

You Just Ended Up With Template Design Pattern

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.

Six Other Uses of Template Design Pattern

Four years ago, I blogged about six other useful scenarios of template design pattern. Do read more about the same from here.

• 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

For further reading do watch the below interview preparation videos and step by step video series.

• C# Design Pattern step by step Tutorial
• C# Interview Questions & Answers
• ASP.NET MVC Interview Questions & Answers
• Angular Interview Questions with Answers
• Learn Azure step by step.
• SQL Server step by step
• C# Is vs As Keywords
• C# Throw vs Throw ex
• C# Concurrency vs Parallelism
• C# abstract class vs Interfaces
• C# String are Immutable