articles >> asp.net / ado.net >> Implementing a Generic Data Access Layer in ...

Implementing a Generic Data Access Layer in ADO.NET Part 2

By : Joydip Kanjilal Jan 28, 2008

Page 2 / 3


The following is the complete code for the DatabaseHelper class.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;

using ApplicationFramework.Configuration;

namespace ApplicationFramework.DataAccessLayer
{
    public class DatabaseHelper : IDisposable
    {
        private string strConnectionString;
        private DbConnection objConnection;
        private DbCommand objCommand;
        private DbProviderFactory objFactory = null;
        private ParameterCache parameterCache = ParameterCache.GetParameterCache();       

        public DatabaseHelper(string connectionstring, ProviderType provider)
        {
            this.strConnectionString = connectionstring;
            objFactory = DBFactory.GetProvider(provider);
            objConnection = objFactory.CreateConnection();
            objCommand = objFactory.CreateCommand();
            objConnection.ConnectionString = this.strConnectionString;
            objCommand.Connection = objConnection;        
        }
      
        internal int AddParameter(string name, object value)
        {
            DbParameter dbParameter = objFactory.CreateParameter();
            dbParameter.ParameterName = name;
            dbParameter.Value = value;
            return objCommand.Parameters.Add(dbParameter);
        }

        internal int AddParameter(DbParameter parameter)
        {
            return objCommand.Parameters.Add(parameter);
        }

        internal int AddParameter(string name, StoredProcedureParameterDirection parameterDirection)
        {
            DbParameter parameter = objFactory.CreateParameter();

            parameter.ParameterName = name;
            parameter.Value = String.Empty;
            parameter.DbType = DbType.String;
            parameter.Size = 50;

            switch (parameterDirection)
            {
                case StoredProcedureParameterDirection.Input:
                    parameter.Direction = System.Data.ParameterDirection.Input;
                break;
            case StoredProcedureParameterDirection.Output:
                parameter.Direction = System.Data.ParameterDirection.Output;
                break;
            case StoredProcedureParameterDirection.InputOutput:
                parameter.Direction = System.Data.ParameterDirection.InputOutput;
                break;
            case StoredProcedureParameterDirection.ReturnValue:
                parameter.Direction = System.Data.ParameterDirection.ReturnValue;
                break;
            }          

            return objCommand.Parameters.Add(parameter);
        }

        internal int AddParameter(string name, object value, StoredProcedureParameterDirection parameterDirection)
        {
            DbParameter parameter = objFactory.CreateParameter();

            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.DbType = DbType.String;
            parameter.Size = 50;

            switch (parameterDirection)
            {
                case StoredProcedureParameterDirection.Input:
                    parameter.Direction = System.Data.ParameterDirection.Input;
                    break;
                case StoredProcedureParameterDirection.Output:
                    parameter.Direction = System.Data.ParameterDirection.Output;
                    break;
                case StoredProcedureParameterDirection.InputOutput:
                    parameter.Direction = System.Data.ParameterDirection.InputOutput;
                    break;
                case StoredProcedureParameterDirection.ReturnValue:
                    parameter.Direction = System.Data.ParameterDirection.ReturnValue;
                    break;
            }    

            return objCommand.Parameters.Add(parameter);
        }

        internal int AddParameter(string name, StoredProcedureParameterDirection parameterDirection, int size, DbType dbType)
        {
            DbParameter parameter = objFactory.CreateParameter();

            parameter.ParameterName = name;
            parameter.DbType = dbType;
            parameter.Size = size;

            switch (parameterDirection)
            {
                case StoredProcedureParameterDirection.Input:
                    parameter.Direction = System.Data.ParameterDirection.Input;
                    break;
                case StoredProcedureParameterDirection.Output:
                    parameter.Direction = System.Data.ParameterDirection.Output;
                    break;
                case StoredProcedureParameterDirection.InputOutput:
                    parameter.Direction = System.Data.ParameterDirection.InputOutput;
                    break;
                case StoredProcedureParameterDirection.ReturnValue:
                    parameter.Direction = System.Data.ParameterDirection.ReturnValue;
                    break;
            }    

            return objCommand.Parameters.Add(parameter);
        }

        internal int AddParameter(string name, object value, StoredProcedureParameterDirection parameterDirection, int size, DbType dbType)
        {
            DbParameter parameter = objFactory.CreateParameter();

            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.DbType = dbType;
            parameter.Size = size;

            switch (parameterDirection)
            {
                case StoredProcedureParameterDirection.Input:
                    parameter.Direction = System.Data.ParameterDirection.Input;
                    break;
                case StoredProcedureParameterDirection.Output:
                    parameter.Direction = System.Data.ParameterDirection.Output;
                    break;
                case StoredProcedureParameterDirection.InputOutput:
                    parameter.Direction = System.Data.ParameterDirection.InputOutput;
                    break;
                case StoredProcedureParameterDirection.ReturnValue:
                    parameter.Direction = System.Data.ParameterDirection.ReturnValue;
                    break;
            }    

            return objCommand.Parameters.Add(parameter);
        }

        internal DbCommand Command
        {
            get
            {
                return objCommand;
            }
        }

        internal DbConnection Connection
        {
            get
            {
                return objConnection;
            }
        }

        internal void BeginTransaction()
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
            {
                objConnection.Open();
            }

            objCommand.Transaction = objConnection.BeginTransaction();
        }

        internal void CommitTransaction()
        {
            objCommand.Transaction.Commit();
            objConnection.Close();
        }

        internal void RollbackTransaction()
        {
            objCommand.Transaction.Rollback();
            objConnection.Close();
        }

        internal int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, CommandType.Text, DatabaseConnectionState.CloseOnExit);
        }

        internal int ExecuteNonQuery(string query, CommandType commandtype)
        {
            return ExecuteNonQuery(query, commandtype, DatabaseConnectionState.CloseOnExit);
        }

        internal int ExecuteNonQuery(string query, DatabaseConnectionState connectionstate)
        {
            return ExecuteNonQuery(query, CommandType.Text, connectionstate);
        }

        internal int ExecuteNonQuery(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;

            int i = -1;

            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }

                i = objCommand.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (connectionstate == DatabaseConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return i;
        }

        internal object ExecuteScalar(string query)
        {
            return ExecuteScalar(query, CommandType.Text, DatabaseConnectionState.CloseOnExit);
        }

        internal object ExecuteScalar(string query, CommandType commandtype)
        {
            return ExecuteScalar(query, commandtype, DatabaseConnectionState.CloseOnExit);
        }

        internal object ExecuteScalar(string query, DatabaseConnectionState connectionstate)
        {
            return ExecuteScalar(query, CommandType.Text, connectionstate);
        }

        internal object ExecuteScalar(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            object o = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }

                o = objCommand.ExecuteScalar();
            }
            catch
            {
                throw;
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == DatabaseConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return o;
        }

        internal DbDataReader ExecuteReader(string query)
        {
            return ExecuteReader(query, CommandType.Text, DatabaseConnectionState.CloseOnExit);
        }

        internal DbDataReader ExecuteReader(string query, CommandType commandtype)
        {
            return ExecuteReader(query, commandtype, DatabaseConnectionState.CloseOnExit);
        }

        internal DbDataReader ExecuteReader(string query, DatabaseConnectionState connectionstate)
        {
            return ExecuteReader(query, CommandType.Text, connectionstate);
        }

        internal DbDataReader ExecuteReader(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            DbDataReader reader = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                if (connectionstate == DatabaseConnectionState.CloseOnExit)
                {
                    reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = objCommand.ExecuteReader();
                }

            }
            catch
            {
               
            }
            finally
            {
                objCommand.Parameters.Clear();
            }

            return reader;
        }

        internal DataSet ExecuteDataSet(string query)
        {
            return ExecuteDataSet(query, CommandType.Text, DatabaseConnectionState.CloseOnExit);
        }

        internal DataSet ExecuteDataSet(string query, CommandType commandtype)
        {
            return ExecuteDataSet(query, commandtype, DatabaseConnectionState.CloseOnExit);
        }

        internal DataSet ExecuteDataSet(string query, DatabaseConnectionState connectionstate)
        {
            return ExecuteDataSet(query, CommandType.Text, connectionstate);
        }

        internal DataSet ExecuteDataSet(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
        {
            DbDataAdapter adapter = objFactory.CreateDataAdapter();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            adapter.SelectCommand = objCommand;
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch
            {
                throw;
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == DatabaseConnectionState.CloseOnExit)
                {
                    if (objConnection.State == System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }
            return ds;
        }

        public void Dispose()
        {
            if (objConnection.State == ConnectionState.Open)
            {
                objConnection.Close();
                objConnection.Dispose();
            }

            objCommand.Dispose();
        }

        internal IDataReader ExecuteReader(string storedProcedureName, params object[] parameters)
        {
            objCommand.CommandText = storedProcedureName;
            objCommand.CommandType = CommandType.StoredProcedure;
            DbDataReader reader = null;
            try
            {
                RetrieveParameters(objCommand);
                SetParameterValues(objCommand, parameters);

                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
               
                reader = objCommand.ExecuteReader();
            }
            catch
            {
                throw;
            }
            finally
            {
                objCommand.Parameters.Clear();
            }
            return reader;          
        }

        internal void SetParameterValues(DbCommand objCommand, object[] parameters)
        {
            int index = 0;
            for (int i = 0; i < parameters.Length; i++)
            {
                DbParameter parameter = objCommand.Parameters[i + index];
                SetParameterValue(objCommand, parameter.ParameterName, parameters[i]);
            }
        }

        internal virtual void SetParameterValue(DbCommand dbCommand, string parameterName, object value)
        {
            dbCommand.Parameters[parameterName].Value = (value == null) ? DBNull.Value : value;
        }

        internal void RetrieveParameters(DbCommand dbCommand)
        {
            if (parameterCache.ContainsParameters(Connection.ConnectionString, dbCommand.CommandText))
            {
                DbParameter[] parameters = parameterCache.GetParameters(Connection.ConnectionString, dbCommand.CommandText);
                dbCommand.Parameters.AddRange(parameters);
            }
            else
            {
                string connectionString = Connection.ConnectionString;
                dbCommand.Connection = Connection;
                Connection.Open();
                SqlCommandBuilder.DeriveParameters(dbCommand as SqlCommand);
                parameterCache.AddParameters(connectionString, dbCommand.CommandText, dbCommand.Parameters);
            }
        }

        internal object GetParameter(string name)
        {
            return objCommand.Parameters[name].Value;
        }
    }
}