Abstract Factory Pattern Multiple Connection strings with multiple data providers
In this walkthrough I'll try to explain GOF (Gang of Four) Abstract Design pattern. This article is about managing multiple connection strings of different data providers (SQL, Oracle, OleDB etc.).
Introduction
First of all let me give a little introduction about Abstract Factory Design pattern. According to official GOF book definition it says:
Abstract Factory pattern should: "Provide an interface for creating families of related or dependent objects without specifying their concrete classes."
![Factory Pattern.PNG]()
Figure1: Abstract Factory sample diagram.
The essence of the Factory Pattern is to "Define an interface for creating an object, but let the subclasses decide which class to instantiate. The Factory method lets a class defer instantiation to subclasses." Factory methods encapsulate the creation of objects. This can be useful if the creation process is very complex, for example if it depends on settings in configuration files or on user input.
An Abstract Factory is usually implemented as an abstract class that real, concrete factories extend. That unifies what the concrete factories do, while allowing leeway to fit differing requirements, as when you are using a different look and feel for each application.
Problem/Scenario: many of us have seen multiple connection strings in web.config file. Here the problem is how to manage them.
<connectionStrings>
<add name="SqlConnectionStringA" connectionString="Data Source=A; Initial
Catalog=AA; Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
<add name="SqlConnectionStringB" connectionString="Data Source=B; Initial
Catalog=AA; Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
<add name="SqlConnectionStringC" connectionString="Data Source=C; Initial
Catalog=AA; Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
<add name="OracleConnectionStringA" connectionString="Data Source=A; Integrated
Security=SSPI;" providerName="System.Data.OracleClient" />
<add name="OracleConnectionStringB" connectionString="Data Source=B; Integrated
Security=SSPI;" providerName="System.Data.OracleClient" />
<add name="OracleConnectionStringC" connectionString="Data Source=B; Integrated
Security=SSPI;" providerName="System.Data.OracleClient" />
<add name="OleDbConnectionStringA" connectionString="Data=A;Provider=SQLOLEDB;
Integrated Security=SSPI;" providerName="System.Data.OleDbClient" />
<add name="OleDbConnectionStringB" connectionString="Data=A;Provider=SQLOLEDB;
Integrated Security=SSPI;" providerName="System.Data.OleDbClient" />
<add name="OleDbConnectionStringC" connectionString="Data=A;Provider=SQLOLEDB;
Integrated Security=SSPI;" providerName="System.Data.OleDbClient" />
</connectionStrings>
Description:
So let's start to create the actual problem and the solution. Your manager asked you to create a new SQL connection; "What a big deal huh!" you thought and type following:
SqlConnection connection = new SqlConnection();
Manager said that's great, now I want to connect to Oracle, MySQL and other Databases also. You added following:
OracleConnection connection = new OracleConnection();
OleDbConnection connectionMySQL = new OleDbConnection ();
Now you have got 3 different types of connections, you might want to wrap it in a function(as there may be 100s of places in your project where you want create connection) which returns a connection based on connection type as parameter passed like "Oracle", "SQL" etc.
public object CreateConnection(string type)
{
object connectionString;
switch (type)
{
case "System.Data.SqlClient":
connectionString = new SqlConnection();
break;
case "System.Data.OracleClient":
connectionString = new OracleConnection();
break;
default:
connectionString = new OleDbConnection();
break;
}
return connectionString;
}
Perfect isn't it. Your manager asked to modify the class for making connections secure. "JEEZ!" you thought. All this code is changing a lot. Moreover this is code core part and you are editing it lot. May be its time recall those best practices we learnt in theories:
"Always separate part of code that keep on changing from rest of the code and make it reusable".
We need to separate out the "connection creation code part" and wrap it in an object, i.e. a factory object.
Building the factory:
First of all create an abstract class let's say it "ConnectionFactory" and our CreateConnection() function in it; and the easiest way to do this is using switch statement or if-else-if statement to instantiate concrete factories like below:
public static ConnectionFactory CreateConnection(string type)
{
ConnectionFactory factory = null;
switch (type)
{
case "System.Data.SqlClient":
factory = new MsSqlFactory();
break;
case "System.Data.OracleClient":
factory = new OracleFactory();
break;
default:
factory = new OleDbFactory();
break;
}
return factory;
}
There is a small problem with this and that is it does not follow "Open for extension but closed for modification principle".
One thing that a developer has to cope up with is the continuous change in development process. We as a developer spend a great deal of time in extending and changing code rather than originally developing it.
Open for Extension but closed for modification principle states that core code should be designed such that it doesn't have to modify a lot but may be extended as needed.
Here in the class "ConnectionFactory" where our CreateConnection () is defined, we need to add a new case statement for every type of provider. So it doesn't follow Open for Extension but closed for modification principle.
Solution to this lies in reflection. We can instantiate our factory using reflection like below:
public abstract class ConnectionFactory
{
private static string provider;
private static ConnectionFactory instance;
private static string connectionStringName;
public static string Provider
{
private get { return provider ?? "System.Data.OleDbClient"; } //sets the default provider to OleDb if not set explicitly
set { provider = value; }
}
public static string ConnectionStringName
{
private get { return connectionStringName ?? "A"; } //sets the default Connectionstring to A if not set explicitly
set { connectionStringName = value; }
}
public static ConnectionFactory Instance
{
get
{
if (instance == null)
{
foreach (Type type in Assembly.GetExecutingAssembly().GetTypes())
{
if (type.BaseType == typeof(ConnectionFactory))
{
string providerName = type.GetField("Provider").GetRawConstantValue().ToString();
if (providerName == Provider)
{
instance = (ConnectionFactory)Activator.CreateInstance(type, true);
break;
}
}
}
}
return instance;
}
}
public abstract IDbConnection Connection { get; }
public abstract IDbCommand Command { get; }
protected static string ConnectionString
{
get
{
ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
foreach (ConnectionStringSettings setting in settings)
{
if (setting.ProviderName == Provider && setting.Name.ToString() == connectionStringName)
{
return setting.ConnectionString;
}
}
return null;
}
}
}
We are almost done just need to dvelop types of connection providers factories (MSqlFactory, OracleFactory, OleDbFactory) like:
public class MsSqlFactory : ConnectionFactory
{
public const string Provider = "System.Data.SqlClient";
public MsSqlFactory()
{
}
public override IDbConnection Connection
{
get { return new SqlConnection(ConnectionString); }
}
public override IDbCommand Command
{
get { return new SqlCommand { Connection = (SqlConnection)this.Connection };
}
}
Hope it would help!!