Click here to Skip to main content
13,139,569 members (55,068 online)
Click here to Skip to main content
Add your own
alternative version

Stats

10.4K views
19 bookmarked
Posted 16 Jul 2015

Reusing ADO.NET code using Template design pattern

, 16 Jul 2015
Rate this:
Please Sign up or sign in to vote.
In this article we will demonstrate Template Pattern reusing ADO.NET

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 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.

Understanding the problem

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");
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 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.

Step 1 :- Creating a base class

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");
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 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.

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

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 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


You may also be interested in...

Pro
Pro

Comments and Discussions

 
SuggestionGood for a basic tutorial, but... Pin
Daniel Santillanes20-Jul-15 18:47
professionalDaniel Santillanes20-Jul-15 18:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 17 Jul 2015
Article Copyright 2015 by Shivprasad koirala
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid