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

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

By : Joydip Kanjilal Jan 29, 2008

Page 2 / 3


Note that the DBManagerBase class contains the most common methods that are required. You can Open or Close a connection, Begin, Commit or Rollback transactions, etc. These methods would remain the same and are mandatory in this context even if you decide to have another version of the DBManager class with some more methods implemented it.

The DBManager class that extends the DBManagerBase abstract class contains a list of methods that can be used to execute stored procedures, queries and return DataSet instance or DataReader instances as well. You can opt for keeping your connection open after the ExecuteReader method is called so that you can use the live connection in the subsequent operations that you need to perform on your database. The methods names in the DBManager class relate to the operations that they are meant to perform. I feel not you will have any problems understanding what each of these methods are supposed to do.

Then, you have the AddParameter method that can be used to add parameters to your stored procedure so that at the time of invoking the procedure, you can pass the parameters along. The connection string that we need to use to connect to our database can be set using the ConnectionString public property. The connection string can typically be stored in your configuration file and the DBManager class can read the configuration file to retrieve the connection string.

The provider type can be set using the ProviderType enum. Fine, but, where will these values be set, i.e., how can we call the DBManager and from where? Confused? Hang on. Let us have a look at the DBManager class followed by how we can use this class to perform CRUD operations.

The following code listing depicts the DBManager 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;

namespace ApplicationFramework.DataAccessLayer
{
    public sealed class DBManager : DBManagerBase
    {
        public void OpenConnection()
        {
            connectionString = ConfigurationSettings.AppSettings["ConnectionString"].ToString();
            base.Open(connectionString);
        }

        public void OpenConnection(String connectionString)
        {
            base.Open(connectionString);
            base.IsOpen = true;
        }

        public void CloseConnection()
        {
            if (base.isOpen)
                base.Close();
            base.IsOpen = false;
        }

        public int AddParameter(string name, object value)
        {
            return databaseHelper.AddParameter(name, value);
        }

        public int AddParameter(string name, StoredProcedureParameterDirection parameterDirection)
        {
            return databaseHelper.AddParameter(name, parameterDirection);
        }

        public int AddParameter(string name, object value, StoredProcedureParameterDirection parameterDirection)
        {
            return databaseHelper.AddParameter(name, value, parameterDirection);
        }
        public int AddParameter(string name, StoredProcedureParameterDirection parameterDirection, int size, DbType dbType)
        {
            return databaseHelper.AddParameter(name, parameterDirection, size, dbType);
        }
        public int AddParameter(string name, object value, StoredProcedureParameterDirection parameterDirection, int size, DbType dbType)
        {
            return databaseHelper.AddParameter(name, value, parameterDirection, size, dbType);
        }
        public object GetParameter(string name)
        {
            return databaseHelper.GetParameter(name);
        }
        public DbDataReader ExecuteReader(string query)
        {
            this.dbDataReader = databaseHelper.ExecuteReader(query);
            return this.dbDataReader;
        }
        public DbDataReader ExecuteReader(string query, CommandType commandtype)
        {
            this.dbDataReader = databaseHelper.ExecuteReader(query, commandtype, DatabaseConnectionState.CloseOnExit);
            return this.dbDataReader;
        }
        public IDataReader ExecuteReader(string storedProcedureName, params object[] parameters)
        {
            this.dbDataReader = (DbDataReader)databaseHelper.ExecuteReader(storedProcedureName, parameters);
            return this.dbDataReader;
        }
        public DbDataReader ExecuteReader(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
        {
            this.dbDataReader = databaseHelper.ExecuteReader(query, commandtype, connectionstate);
            return this.dbDataReader;
        }
        public DbDataReader ExecuteReader(string query, DatabaseConnectionState connectionstate)
        {
            this.dbDataReader = databaseHelper.ExecuteReader(query, connectionstate);
            return this.dbDataReader;
        }
        public object ExecuteScalar(string query)
        {
            return databaseHelper.ExecuteScalar(query);
        }
        public object ExecuteScalar(string query, CommandType commandtype)
        {
            return databaseHelper.ExecuteScalar(query, commandtype);
        }
        public object ExecuteScalar(string query, DatabaseConnectionState connectionstate)
        {
            return databaseHelper.ExecuteScalar(query, connectionstate);
        }
        public object ExecuteScalar(string query, CommandType commandtype, DatabaseConnectionState connectionstate)
        {
            return databaseHelper.ExecuteScalar(query, commandtype, connectionstate);
        }
        public DataSet ExecuteDataSet(string query)
        {
            this.dataSet = databaseHelper.ExecuteDataSet(query);
            return this.dataSet;
        }
        public DataSet ExecuteDataSet(string query, CommandType commandtype)
        {
            this.dataSet = databaseHelper.ExecuteDataSet(query, commandtype);
            return this.dataSet;
        }
        public int ExecuteNonQuery(string query, CommandType commandtype)
        {
            return databaseHelper.ExecuteNonQuery(query, commandtype);
        }
        public int ExecuteNonQuery(string query, CommandType commandtype, DatabaseConnectionState databaseConnectionState)
        {
            return databaseHelper.ExecuteNonQuery(query, commandtype, databaseConnectionState);
        }
    }
}