A Generic Data Access Component using Factory Pattern

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 cref="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, IDbConnection 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
}
}