I just read Mahesh's article Writing a Generic Data Access Component.
Another way to solve this problem is to utilize the System.Activator class and a factory pattern to create the concrete provider classes as was pointed-out in Dan Fox's article "Design an Effective Data-Access Architecture" (.netmagazine, vol. 2, no. 7). I took this idea and refined it a bit so that the only steps necessary to add a new provider to the factory is to add a new enum and the associated type values to the factory's static type arrays (a total of 5 lines of code).
Here is the sample code. See the attached source code for a test application.
Source code
using
System;
using System.Reflection;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace CSharpCorner.ProviderFactory
{
/// <summary>
/// The collection of ADO.NET data providers that are supported by <see
ref="ProviderFactory"/>.
/// </summary>
public enum ProviderType
{
/// <summary>
/// The OLE DB (<see cref="System.Data.OleDb"/>) .NET data provider.
/// </summary>
OleDb = 0,
/// <summary>
/// The SQL Server (<see cref="System.Data.SqlClient"/>) .NET data provider.
/// </summary>
SqlClient
};
/// <summary>
/// The <b>ProviderFactory</b> class abstracts ADO.NET relational data providers through creator methods which return.
/// the underlying <see cref="System.Data"/> interface.
/// </summary>
/// <remarks>
/// This code was inspired by "Design an Effective Data-Access Architecture" by Dan Fox (.netmagazine, vol. 2, no. 7)
/// </remarks>
public class ProviderFactory
{
#region private variables
private static Type[] _connectionTypes = new Type[] { typeof(OleDbConnection), typeof(SqlConnection) };
private static Type[] _commandTypes = new Type[] { typeof(OleDbCommand), typeof(SqlCommand) };
private static Type[] _dataAdapterTypes = new Type[] { typeof(OleDbDataAdapter), typeof(SqlDataAdapter) };
private static Type[] _dataParameterTypes = new Type[] { typeof(OleDbParameter), typeof(SqlParameter) };
private ProviderType _provider;
#endregion
#region ctors
private ProviderFactory() { } // force user to specify provider
public ProviderFactory(ProviderType provider)
{
_provider = provider;
}
#endregion
#region Provider property
public ProviderType Provider
{
get
{
return _provider;
}
set
{
_provider = value;
}
}
#endregion
#region IDbConnection methods
public IDbConnection CreateConnection()
{
IDbConnection conn = null;
try
{
conn = (IDbConnection) Activator.CreateInstance(_connectionTypes[(int) _provider]);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return conn;
}
public IDbConnection CreateConnection(string connectionString)
{
IDbConnection conn = null;
object[] args = {connectionString};
try
{
conn = (IDbConnection) Activator.CreateInstance(_connectionTypes[(int) _provider], args);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return conn;
}
#endregion
#region IDbCommand methods
public IDbCommand CreateCommand()
{
IDbCommand cmd = null;
try
{
cmd = (IDbCommand) Activator.CreateInstance(_commandTypes[(int) _provider]);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return cmd;
}
public IDbCommand CreateCommand(string cmdText)
{
IDbCommand cmd = null;
object[] args = {cmdText};
try
{
cmd = (IDbCommand) Activator.CreateInstance(_commandTypes[(int) _provider], args);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return cmd;
}
public IDbCommand CreateCommand(string cmdText, IDbConnection connection)
{
IDbCommand cmd = null;
object[] args = {cmdText, connection};
try
{
cmd = (IDbCommand) Activator.CreateInstance(_commandTypes[(int) _provider], args);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return cmd;
}
public IDbCommand CreateCommand(string cmdText, IDbConnection connection, IDbTransaction transaction)
{
IDbCommand cmd = null;
object[] args = {cmdText, connection, transaction};
try
{
cmd = (IDbCommand) Activator.CreateInstance(_commandTypes[(int) _provider], args);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return cmd;
}
#endregion
#region IDbDataAdapter methods
public IDbDataAdapter CreateDataAdapter()
{
IDbDataAdapter da = null;
try
{
da = (IDbDataAdapter) Activator.CreateInstance(_dataAdapterTypes[(int) _provider]);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return da;
}
public IDbDataAdapter CreateDataAdapter(IDbCommand selectCommand)
{
IDbDataAdapter da = null;
object[] args = {selectCommand};
try
{
da = (IDbDataAdapter) Activator.CreateInstance(_dataAdapterTypes[(int) _provider], args);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return da;
}
public IDbDataAdapter CreateDataAdapter(string selectCommandText, bConnection selectConnection)
{
IDbDataAdapter da = null;
object[] args = {selectCommandText, selectConnection};
try
{
da = (IDbDataAdapter) Activator.CreateInstance(_dataAdapterTypes[(int) _provider], args);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return da;
}
public IDbDataAdapter CreateDataAdapter(string selectCommandText, string selectConnectionString)
{
IDbDataAdapter da = null;
object[] args = {selectCommandText, selectConnectionString};
try
{
da = (IDbDataAdapter) Activator.CreateInstance(_dataAdapterTypes[(int) _provider], args);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return da;
}
#endregion
#region IDbDataParameter methods
public IDbDataParameter CreateDataParameter()
{
IDbDataParameter param = null;
try
{
param = (IDbDataParameter) Activator.CreateInstance(_dataParameterTypes[(int) _provider]);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return param;
}
public IDbDataParameter CreateDataParameter(string parameterName, object value)
{
IDbDataParameter param = null;
object[] args = {parameterName, value};
try
{
param = (IDbDataParameter) Activator.CreateInstance(_dataParameterTypes[(int) _provider], args);
}
catch (TargetInvocationException e)
{
throw new SystemException(e.InnerException.Message, e.InnerException);
}
return param;
}
public IDbDataParameter CreateDataParameter(string parameterName, DbType dataType)
{
IDbDataParameter param = CreateDataParameter();
if (param != null)
{
param.ParameterName = parameterName;
param.DbType = dataType;
}
return param;
}
public IDbDataParameter CreateDataParameter(string parameterName, DbType dataType, int size)
{
IDbDataParameter param = CreateDataParameter();
if (param != null)
{
param.ParameterName = parameterName;
param.DbType = dataType;
param.Size = size;
}
return param;
}
public IDbDataParameter CreateDataParameter(string parameterName, DbType dataType, int size, string sourceColumn)
{
IDbDataParameter param = CreateDataParameter();
if (param != null)
{
param.ParameterName = parameterName;
param.DbType = dataType;
param.Size = size;
param.SourceColumn = sourceColumn;
}
return param;
}
#endregion
}
}