October 2017 : Online course in ASP.NET MVC / Core. Conducted by Bipin Joshi. Read more...
Registration for October 2017 batch of ASP.NET MVC / Core online course has already started. Conducted by Bipin Joshi. Book your seat today ! Click here for more details.

<%@ Page %>

Developing Factory Class for SQL and OLEDB Data Providers

Introduction

While developing a real life software project you need to take in to account many things. One of these things is the choice of database. Most of the times your client will have predefined database (e.g. SQL Server or Oracle). However, some times situation arises such that your database is not fixed at development time. For example if you are developing a product then you will not be knowing the database of your prospective clients in advance. Similarly, if your client demands for database independence (may be because he is migrating his database) then also the database is not fixed. The common solution can be to develop two different sets of data access components - one for SQL server and another for Oracle. However, this will increate your maintenance and version tracking. .NET provides an easy way to tackle this problem. All the data access classes i.e. classes from System.Data.SQLClient and System.Data.OleDb namespaces actually implement certain interfaces from System.Data namespace. In order to make your data access layer components independent of any database you can code against these interfaces rather than actual classes. In this article we will develop a factory class that returns objects from appropriate namespace based on the data provider type you supply. We will also see how to code your data access layer against these interfaces.

System.Data Interfaces

All the .NET providers (SQL and OleDB) implement certain set of interfaces found in System.Data namespace. Following is the list of some of these interfaces:
  • IDbConnection
  • IDbCommand
  • IDbDataAdapter
  • IDataParameter
You will easily be able to identify the mapping between the interfaces and actual classes.

Developing a factory class

We will develop a factory class that will simply return you objects of specified data provider. For example if you specify that your data provider is SQL server, you will be returned a SQLConnection object where as if you specify that your data provider is any OLEDB compatible database then a OleDbConnection will be returned. Following is the complete code for the class.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace DALFactory
{
public class DALObjects
{

public static IDbConnection
GetConnection(DataProviderType type)
{
if(type==DataProviderType.SQL)
{
		return new SqlConnection();
	}
	else
	{
		return new OleDbConnection();
	}
}

public static IDbCommand
GetCommand(DataProviderType type)
{
	if(type==DataProviderType.SQL)
	{
		return new SqlCommand();
	}
	else
	{
		return new OleDbCommand();
	}
}

public static IDbDataAdapter
GetDataAdapter(DataProviderType type)
{
	if(type==DataProviderType.SQL)
	{
		return new SqlDataAdapter();
	}
	else
	{
		return new OleDbDataAdapter();
	}
}


public static IDataParameter
GetParameter(DataProviderType type)
{
	if(type==DataProviderType.SQL)
	{
		return new SqlParameter();
	}
	else
	{
		return new OleDbParameter();
	}
}
}

public enum DataProviderType
{
	SQL=0,OLEDB=1
}
}

As you will notice we are returning objects that can be instantiated on their own. That is why we have not included DataReader here. Note that we have created all the methods as static. This way we can easily make use of our methods without creating class instance. Also, note that we will be using DataProviderType enum in our code that indicates which namespaces to use. Now that your have your factory class ready let us see how to use it.

Coding against System.Data interfaces

As mentioned earlier in this section we will see how to use the factory class in our code. Typically when you develop a data access class your code looks like this:
OleDbConnection cnn;
OleDbDataAdapter cmd;
OleDbDataReader dr;

cnn = New OleDbConnection(connectionstring);
cmd = New OleDbDataAdapter(sqlquery,cnn);

dr=cmd.ExecuteReader();
Now, since we need to make our class database independent we will not explicitly declare objects from OleDb or SqlClient namespaces. Instead we will code as follows:
IDbConnection cnn=
DALObjects.GetConnection(DataProviderType.OLEDB);
cnn.Open();
IDbCommand cmd=
DALObjects.GetCommand(DataProviderType.OLEDB);
cmd.ConnectionString="some_sql";
cmd.Connection=cnn;
IDataReader dr=cmd.ExecuteReader();
Here, we have avoided using explicit classes of specific data provider. Note that since you are using interfaces here you will not be able to invoke methods and properties specific to a particular provider. For example, if you want to use IDbTransaction interface then you can not call Save() method which is specific to SQL Server. However, what we achieve is database independence. Now, in above example the data provider type parameter can be stored in some external XML file that can be easily changed after installation at client´┐Żs place.

Summary

We saw how all the .NET data providers implement certain interfaces from System.Data namespace. Instead of coding against a specific data provider you can make use of these interfaces to achieve database independence. We also created a factory class that you can reuse in your projects with little or no modification.

Bipin Joshi is a software consultant, an author and a yoga mentor having 22+ years of experience in software development. He also conducts online courses in ASP.NET MVC / Core and Design Patterns. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Meditation and Mindfulness to interested individuals. To know more about him click here.

Get connected : Twitter  Facebook  Google+  LinkedIn

Posted On : 02 February 2002


Tags : ADO.NET Data Access SQL Server Architecture Components Programming Languages