Ravi Singh

In most applications database access is restricted to a single database type. if you need to connect to multiple types of databases? Do you write a data access class for each database? When faced with this dilemma working on a project I decided to use a single class and create the data access objects through reflection.
############################################
DataProviderFactory.cs Class
############################################
using System.Data;
using System.Collections;
using Microsoft.VisualBasic;
using System.Diagnostics;
using System;

namespace PortableDAL
{
internal class DataProviderFactory
{
public static IDbConnection CreateConnection(Type providerType)
{
return ((IDbConnection) Activator.CreateInstance(providerType));
} //CreateConnection
public static IDbConnection CreateConnection(Type providerType, string connectionString)
{
object[] args = new object[] { connectionString };
return ((IDbConnection) Activator.CreateInstance(providerType, args));
} //CreateConnection
public static IDbCommand CreateCommand(Type providerType)
{
return ((IDbCommand) Activator.CreateInstance(providerType));
} //CreateCommand
public static IDbCommand CreateCommand(Type providerType, string cmdText)
{
object[] args = new object[] { cmdText };
return ((IDbCommand) Activator.CreateInstance(providerType, args));
} //CreateCommand
public static IDbCommand CreateCommand(Type providerType, string cmdText, IDbConnection connection)
{
object[] args = new object[] { cmdText, connection };
return ((IDbCommand) Activator.CreateInstance(providerType, args));
} //CreateCommand
public static IDbCommand CreateCommand(Type providerType, string cmdText, IDbConnection connection, IDbTransaction transaction)
{
object[] args = new object[] { cmdText, connection, transaction };
return ((IDbCommand) Activator.CreateInstance(providerType, args));
} //CreateCommand
public static IDbDataAdapter CreateDataAdapter(Type providerType)
{
return ((IDbDataAdapter) Activator.CreateInstance(providerType));
} //CreateDataAdapter
public static IDbDataAdapter CreateDataAdapter(Type providerType, IDbCommand selectCommand)
{
object[] args = new object[] { selectCommand };
return ((IDbDataAdapter) Activator.CreateInstance(providerType, args));
} //CreateDataAdapter
public static IDbDataAdapter CreateDataAdapter(Type providerType, string selectCommandText, IDbConnection selectConnection)
{
object[] args = new object[] { selectCommandText, selectConnection };
return ((IDbDataAdapter) Activator.CreateInstance(providerType, args));
} //CreateDataAdapter
public static IDbDataAdapter CreateDataAdapter(Type providerType, string selectCommandText, string selectConnectionString)
{
object[] args = new object[] { selectCommandText, selectConnectionString };
return ((IDbDataAdapter) Activator.CreateInstance(providerType, args));
} //CreateDataAdapter
public static IDbDataParameter CreateDataParameter(Type providerType)
{
return ((IDbDataParameter) Activator.CreateInstance(providerType));
} //CreateDataParameter
public static IDbDataParameter CreateDataParameter(Type providerType, string parameterName, object value)
{
object[] args = new object[] { parameterName, value };
return ((IDbDataParameter) Activator.CreateInstance(providerType, args));
} //CreateDataParameter
public static IDbDataParameter CreateDataParameter(Type providerType, string parameterName, object value, DbType dataType)
{
IDbDataParameter param = CreateDataParameter(providerType);
if (!(param == null))
{
param.ParameterName = parameterName;
param.DbType = dataType;
param.Value = value;
}
return param;
} //CreateDataParameter
public static IDbDataParameter CreateDataParameter(Type providerType, string parameterName, DbType dataType, int size)
{
IDbDataParameter param = CreateDataParameter(providerType);
if (!(param == null))
{
param.ParameterName = parameterName;
param.DbType = dataType;
param.Size = size;
}
return param;
} //CreateDataParameter
public static IDbDataParameter CreateDataParameter(Type providerType, string parameterName, DbType dataType, int size, string sourceColumn)
{
IDbDataParameter param = CreateDataParameter(providerType);
if (!(param == null))
{
param.ParameterName = parameterName;
param.DbType = dataType;
param.Size = size;
param.SourceColumn = sourceColumn;
}
return param;
} //CreateDataParameter
public static IDbDataParameter CreateDataParameter(Type providerType, string parameterName, DbType dataType, int size, ParameterDirection direction)
{
IDbDataParameter param = CreateDataParameter(providerType);
if (!(param == null))
{
param.ParameterName = parameterName;
param.DbType = dataType;
param.Size = size;
param.Direction = direction;
}
return param;
} //CreateDataParameter
public static IDbDataParameter CreateDataParameter(Type providerType, string parameterName, DbType dataType, object value, int size, ParameterDirection direction)
{
IDbDataParameter param = CreateDataParameter(providerType);
if (!(param == null))
{
param.ParameterName = parameterName;
param.DbType = dataType;
param.Size = size;
param.Direction = direction;
param.Value = value;
}
return param;
} //CreateDataParameter
}
}
#########################################
DataAccessor.cs Class
#########################################

using System.Data;
using System.Configuration;
using System.Collections;
using Microsoft.VisualBasic;
using System.Diagnostics;
using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.OracleClient;
using MySql.Data.MySqlClient;
namespace PortableDAL
{
public enum ProviderType
{
Odbc = 0,
OleDb,
Oracle,
SqlClient,
MySqlClient
}
///—————————————————————————–
/// Project : DataAccess
/// Class : DataAccessor
///
///—————————————————————————–
///
/// Helper for data access
/// Wraps the database access calls
///
/// this class requires a reference to System.Data.OracleClient.dll
/// this class requires a reference to MySql.Data.dll
/// This class is dependent on the DataProviderFactory
///
///—————————————————————————–
public class DataAccessor : IDisposable
{
#region “Private Fields”
private const string PARAMS_FAILED = “Could not attach parameters.”;
private const int COMMAND_TIMEOUT = 120;
private static Type[] m_connectionTypes = new Type[] { typeof(OdbcConnection), typeof(OleDbConnection), typeof(OracleConnection), typeof(SqlConnection),typeof(MySqlConnection) };
private static Type[] m_dataAdapterTypes = new Type[] { typeof(OdbcDataAdapter), typeof(OleDbDataAdapter), typeof(OracleDataAdapter), typeof(SqlDataAdapter),typeof(MySqlDataAdapter) };
private static Type[] m_parameterTypes = new Type[] { typeof(OdbcParameter), typeof(OleDbParameter), typeof(OracleParameter), typeof(SqlParameter),typeof(MySqlParameter) };
private static Type[] m_commandTypes = new Type[] { typeof(OdbcCommand), typeof(OleDbCommand), typeof(OracleCommand), typeof(SqlCommand),typeof(MySqlCommand) };
private static Type[] m_commandBuilderTypes = new Type[] { typeof(OdbcCommandBuilder), typeof(OleDbCommandBuilder), typeof(OracleCommandBuilder), typeof(SqlCommandBuilder),typeof(MySqlCommandBuilder) };
private IDbConnection m_connection; // connection used by the class
private IDbTransaction m_transaction; // transaction used by the class
private string m_connectionString; // connection string used for the connection
private ProviderType m_provider; // provider type to use for the data objects
private int m_commandTimeout = 120; // Timeout for the command execution
#endregion
#region “Constructors”
public DataAccessor(ProviderType provider)
{
m_provider = provider;
} //New
public DataAccessor(string connectionString, ProviderType provider)
{
m_provider = provider;
m_connectionString = connectionString;
} //New
public DataAccessor(ProviderType provider, string key)
{
//get the connection string from the configuration file
System.Configuration.AppSettingsReader appRead = new System.Configuration.AppSettingsReader();
m_connectionString = (string) appRead.GetValue(key, typeof(System.String));
m_provider = provider;
} //New
#endregion
#region “Properties”
public ProviderType Provider
{
get
{
return m_provider;
}
set
{
m_provider = value;
}
} //Provider
public string ConnectionString
{
get
{
return m_connectionString;
}
set
{
m_connectionString = value;
}
} //ConnectionString
public IDbConnection Connection
{
get
{
// if the connection does not exist, create and open it
if (m_connection == null)
{
m_connection = DataProviderFactory.CreateConnection(m_connectionTypes[ (int) m_provider], m_connectionString);
m_connection.Open();
}
else
{
if (m_connection.State != ConnectionState.Open)
{
m_connection.Open();
}
}
return m_connection;
}
set
{
m_connection = value;
}
} //Connection
public IDbTransaction Transaction
{
get
{
return m_transaction;
}
set
{
m_transaction = value;
}
} //Transaction
public int CommandTimeout
{
get
{
return m_commandTimeout;
}
set
{
m_commandTimeout = value;
}
} // CommandTimeout
#endregion
#region “Public Methods”
public void Dispose()
{
// dispose of the transaction if it exists
if (m_transaction != null)
{
m_transaction.Dispose();
}
// dispose of the connection
if (m_connection != null)
{
if (m_connection.State == ConnectionState.Open)
{
m_connection.Close();
}
m_connection.Dispose();
}
} //Dispose
public IDbTransaction BeginTransaction()
{
if (m_transaction == null)
{
m_transaction = Connection.BeginTransaction();
}
return m_transaction;
} //BeginTransaction
///
/// Execute a IDbCommand (that returns no resultset and takes commandText & commandType parameters) against the database specified in
/// the connection string
///
///
/// e.g.:
/// int result = ExecuteNonQuery(“GetOrders”,CommandType.StoredProcedure, 24, 36);
///
///
The stored procedure name or T-SQL command
///
The CommandType (stored procedure, text, etc.)
///
Array of objects holding the values to be assigned
/// An int representing the number of rows affected by the command
public int ExecuteNonQuery(string query, CommandType queryType, IDbDataParameter[] param)
{
IDbCommand cmd = CreateCommand(query, queryType);
AppendParameters(param, cmd);
cmd.Transaction = m_transaction;
int retVal = cmd.ExecuteNonQuery();
if (m_transaction == null)
{
m_connection.Close();
}
cmd.Dispose();
return retVal;
} //ExecuteNonQuery
///
/// Execute a IDbCommand (that returns no resultset and takes commandText & commandType parameters) against the database specified in
/// the connection string
///
///
/// e.g.:
/// DataSet ds = ExecuteDataset(“GetOrders”,CommandType.StoredProcedure,24,36 );
///
///
The CommandType (stored procedure, text, etc.)
///
The stored procedure name or T-SQL command
///
Array of objects holding the values to be assigned
///
A dataset containing the resultset generated by the command
/// A dataset containing the resultset generated by the command
public int ExecuteDataset ( string query, CommandType queryType, IDbDataParameter[] param, DataSet ds )
{
IDbCommand cmd = CreateCommand(query, queryType);
AppendParameters(param, cmd);
IDbDataAdapter da = DataProviderFactory.CreateDataAdapter(m_dataAdapterTypes[(int)m_provider]);
da.SelectCommand = cmd;
cmd.Transaction = m_transaction;
int retVal = da.Fill(ds);
if (m_transaction == null)
{
m_connection.Close();
}
cmd.Dispose();
return retVal;
} //ExecuteQuery
///
/// Execute a IDbCommand (that returns no resultset and takes commandText & commandType parameters) against the database specified in
/// the connection string
///
///
/// e.g.:
/// DataSet ds = ExecuteDataset(“GetOrders”,CommandType.StoredProcedure,24,36 );
///
///
The CommandType (stored procedure, text, etc.)
///
The stored procedure name or T-SQL command
///
Array of objects holding the values to be assigned
/// A dataset containing the resultset generated by the command
public DataSet ExecuteDataset(string query, CommandType queryType, IDbDataParameter[] param)
{
IDbCommand cmd = CreateCommand(query, queryType);
DataSet ds = new DataSet();
AppendParameters(param, cmd);
IDbDataAdapter da = DataProviderFactory.CreateDataAdapter(m_dataAdapterTypes[ (int) m_provider]);
da.SelectCommand = cmd;
cmd.Transaction = m_transaction;
da.Fill(ds);
cmd.Parameters.Clear();
if (m_transaction == null)
{
m_connection.Close();
}
cmd.Dispose();
return ds;
} //ExecuteDataset
///
/// Execute a IDbCommand (that returns no resultset and takes commandText & commandType parameters) against the database specified in
/// the connection string
///
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(“GetOrderCount”,CommandType.StoredProcedure,24 );
///
///
The CommandType (stored procedure, text, etc.)
///
The stored procedure name or T-SQL command
///
Array of objects holding the values to be assigned
/// An object containing the value in the 1×1 resultset generated by the command
public object ExecuteScalar(string query, CommandType queryType, IDbDataParameter[] param)
{
IDbCommand cmd = CreateCommand(query, queryType);
AppendParameters(param, cmd);
cmd.Transaction = m_transaction;
object retVal = cmd.ExecuteScalar();
if (m_transaction == null)
{
m_connection.Close();
}
cmd.Dispose();
return retVal;
} //ExecuteScalar
///
/// Execute a IDbCommand (that returns no resultset and takes commandText & commandType parameters) against the database specified in
/// the connection string
///
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader( “GetOrders”,CommandType.StoredProcedure,24,34);
///
///
The CommandType (stored procedure, text, etc.)
///
The stored procedure name or T-SQL command
///
Array of objects holding the values to be assigned
/// A IDataReader containing the resultset generated by the command
public IDataReader ExecuteReader ( string query, CommandType queryType, IDbDataParameter[] param )
{
IDbCommand cmd = CreateCommand(query, queryType);
AppendParameters(param, cmd);
IDataReader dr;
dr = cmd.ExecuteReader();
cmd.Dispose();
return dr;
} //GetReader
public IDbDataParameter[] GetParameters(string query, CommandType queryType)
{
IDbCommand cmd = CreateCommand(query, queryType);
m_commandBuilderTypes[ (int) m_provider].GetMethod(“DeriveParameters”).Invoke(null, new object[] {cmd});
IDbDataParameter[] parameters = new IDbDataParameter[cmd.Parameters.Count-1 + 1];
cmd.Parameters.CopyTo(parameters, 0);
return CloneParameters(parameters);
}
#endregion
#region “Private Methods”
///
/// Deep copy of cached IDbDataParameter array
///
///

///
private IDbDataParameter[] CloneParameters(IDbDataParameter[] originalParameters)
{
int i;
int j = originalParameters.Length – 1;
IDbDataParameter[] clonedParameters = new IDbDataParameter[j + 1];
for (i = 0; i <= j; i++)
{
clonedParameters[i] = (IDbDataParameter) (((ICloneable) originalParameters[i]).Clone());
}
return clonedParameters;
} // CloneParameters
///
/// This method is used to attach array of IDbDataParameter to a IDbCommand.
///
///
An array of IDbDataParameter to be added to command
///
The command to which the parameters will be added
private void AppendParameters(IDbDataParameter[] @params, IDbCommand cmd)
{
// add the parameters to the command
try
{
if (!(@params == null))
{
foreach (IDbDataParameter parameter in @params)
{
cmd.Parameters.Add(parameter);
}
}
}
catch (Exception e)
{
throw (new Exception(PARAMS_FAILED, e));
}
} //AppendParameters
///
/// This method opens (if necessary) and assigns a command type and command text
/// to the provided command
///
///
The IDbCommand to be prepared
///
The CommandType (stored procedure, text, etc.)
///
The stored procedure name or T-SQL command
private IDbCommand CreateCommand(string query, CommandType queryType)
{
// Associate the connection with the command
IDbCommand cmd = Connection.CreateCommand();
// Set the command text (stored procedure name or SQL statement)
cmd.CommandText = query;
// Set the command type
cmd.CommandType = queryType;
// Set the command timeout
cmd.CommandTimeout = m_commandTimeout;
return cmd;
} //CreateCommand
#endregion
} //DataAccessor
}
###############################################
Form1.cs
###############################################
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace PortableDataAccessLayer
{
public partial class Form1 : Form
{
public Form1 ()
{
InitializeComponent();
M_Test();
}
public void M_Test ()
{

// Using Portable Data Access Layer
// To get connection string form configuration file
string m_connectionString = ConfigurationManager.AppSettings.Get(“ConnectionString”);
// Passing provider type and configuration key for connection string in configuration file
DataAccessor helper = new DataAccessor(ProviderType.MySqlClient, “ConnectionString”);
#region Execute Scalar
// How to get scalar value
IDbDataParameter[] @params = new IDbDataParameter[1];
@params = helper.GetParameters(“p_GetGlobalBalance”, CommandType.StoredProcedure);
@params[0].Value = 2;
object dBal = helper.ExecuteScalar(“p_GetGlobalBalance”, CommandType.StoredProcedure, @params);
#endregion
#region DataSet
// How to get DataSet
//IDbDataParameter[] @params = new IDbDataParameter[1];
//@params = helper.GetParameters(“p_ItemUpdates”, CommandType.StoredProcedure);
//@params[0].Value = objRequest.TerminalID;
//DataSet ds = helper.ExecuteDataset(“p_ItemUpdates”, CommandType.StoredProcedure, @params);
#endregion
#region Data Reader
// How to get Data Reader
//IDbDataParameter[] @params = new IDbDataParameter[1];
//@params = helper.GetParameters(“p_ItemUpdates”, CommandType.StoredProcedure);
//@params[0].Value = objRequest.TerminalID;
//DbDataReader dr = (DbDataReader)helper.GetReader(“p_ItemUpdates”, CommandType.StoredProcedure, @params);
//while(dr.Read())
//{
// string aa = dr.GetValue(3).ToString();
//}
#endregion
helper.Dispose();
}
}
}