Generic DataBase connection code in C#.net

using System;

using System.Reflection;using System.Data;using System.Data.OleDb;using System.Data.SqlClient;using System.Data.OracleClient;namespace KundanCorner.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,
OracleClient
};
/// <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), typeof(OracleConnection) };
private static Type[] _commandTypes = new Type[] { typeof(OleDbCommand), typeof(SqlCommand), typeof(OracleCommand) };
private static Type[] _dataAdapterTypes = new Type[] { typeof(OleDbDataAdapter), typeof(SqlDataAdapter), typeof(OracleDataAdapter) };
private static Type[] _dataParameterTypes = new Type[] { typeof(OleDbParameter), typeof(SqlParameter), typeof(OracleParameter) };
private ProviderType _provider;#endregion
#region ctors
private ProviderFactory() { } // force user to specify providerpublic 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}
}