May 03

Generic Data Access Layer in C# using DbProviderFactories

Generic Data Access Layer in C# using DbProviderFactories

1.   Introduction

In my previouspost we discussed about the implementation of generic data access layer using factory pattern. We created common interface functions and overridden into derived classes to access provider specific ADO.NET objects. The problem with that implementation is the code complexity and number of classes.  But that approach was necessary to understand the DBProviderFactory approach.       
The DbProviderFactories class inside System.Data.Common namespace used to create instance of provider specific ADO.NET objects. It returns the DbProviderFactory instance which will be used to create Connection, Command, DataAdapter and DataParameter objects of ADO.NET. In this post we will create a wrapper class on top of this DbProviderFactory instance to abstract data access instances like IDbConnection , IDbCommand , DbDataAdapter and DbParameter

2.   Overview

The DbProviderFactory contains set of methods for creating instances of a provider’s implementation             of the data source classes.

The DbProviderFactories class provides static methods for creating a DbProviderFactory instance. The instance then returns a correct strongly typed object based on provider information and the connection string supplied at run time.
 
In order to create a provider factory, you must supply a connection string as well as the provider name
 
<connectionStrings>
       <add name=DBConnection connectionString=Data Source=USERSQLSQLEXPRESS;Initial Catalog=Demo;Integrated Security=True;Timeout=90; providerName=System.Data.SqlClient />
</connectionStrings>

3.   Practical Implementation

Step 1: Create configuration settings class to get ConnectionString and ProviderName

This class used to get properties like connection string and provider name from the connection string mentioned in App/Web.config file
using System;
using System.Configuration;
namespace DataAccessHandler
{
    internal static class ConfigurationSettings
    {
        #regionProperties
        public static string DefaultConnection
        {
            get
            {
                return ConfigurationManager.ConnectionStrings[“DefaultConnection”].ToString();
            }
        }
        public static string ProviderName
        {
            get
            {
                return ConfigurationManager.ConnectionStrings[DefaultConnection].ProviderName;
            }
        }
        public static string ConnectionString
        {
            get
            {
                try
                {
                    return ConfigurationManager.ConnectionStrings[DefaultConnection].ConnectionString;
                }
                catch (Exception)
                {
                    throw new Exception(string.Format(“Connection string ‘{0}’ not found.”, DefaultConnection));
                }
            }
        }
        #endregion
        #regionStatic Methods
        public static string GetConnectionString(string connectionName)
        {
            try
            {
                return ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
            }
            catch (Exception)
            {
                throw new Exception(string.Format(“Connection string ‘{0}’ not found.”, connectionName));
            }
        }
        public static string GetProviderName(string connectionName)
        {
            try
            {
                return ConfigurationManager.ConnectionStrings[connectionName].ProviderName;
            }
            catch (Exception)
            {
                throw new Exception(string.Format(“Connection string ‘{0}’ not found.”, connectionName));
            }
        }
        #endregion
    }
}

Step 2: Create ProviderManager class to create instance of provider using DbProviderFactories

This class used to create instance of provider using DbProviderFactories. The GetFactoryfunction of DbProviderFactories returns the DbProviderFactory for provider given.
using System.Data.Common;
namespace DataAccessHandler
{
    public class ProviderManager
    {
        #regionProperty
        public string ProviderName { get; set; }
        public DbProviderFactory Factory
        {
            get
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
                return factory;
            }
        }
        #endregion
        #regionConstructor
        public ProviderManager()
        {
            ProviderName = ConfigurationSettings.GetProviderName(ConfigurationSettings.DefaultConnection);
        }
        public ProviderManager(string providerName)
        {
            ProviderName = providerName;
        }
        #endregion
    }
}

Step 3: Create DatabaseHelper class to create instance of ADO.NET objects using DbProviderFactory

This class used to create instance of Connection, Command, DataAdapter and Parameter objects that access the relational databases.
using System;
using System.Data;
using System.Data.Common;
namespace DataAccessHandler
{
    public class DatabaseHelper
    {
        #regionProperties
        public ProviderManager ProviderManager { get; set; }
        public string ConnectionString { get; set; }
        #endregion
        #regionConstructors
        public DatabaseHelper()
        {
            ConnectionString = ConfigurationSettings.ConnectionString;
            ProviderManager = new ProviderManager();
        }
        public DatabaseHelper(string connectionName)
        {
            ConnectionString = ConfigurationSettings.GetConnectionString(connectionName);
            ProviderManager = new ProviderManager(ConfigurationSettings.GetProviderName(connectionName));
        }
        public DatabaseHelper(string connectionString, string providerName)
        {
            ConnectionString = connectionString;
            ProviderManager = new ProviderManager(providerName);
        }
        #endregion
        #regionDatabase objects
        public notepad GetConnection()
        {
            try
            {
                var connection = ProviderManager.Factory.CreateConnection();
                connection.ConnectionString = ConnectionString;
                connection.Open();
               
                return connection;
            }
            catch (Exception)
            {
                throw new Exception(“Error occured while creating connection. Please check connection string and provider name.”);
            }
        }
        public void CloseConnection(IDbConnection connection)
        {
            connection.Close();
        }
        public IDbCommand GetCommand(string commandText, IDbConnection connection, CommandType commandType)
        {
            try
            {
                IDbCommand command = ProviderManager.Factory.CreateCommand();
                command.CommandText = commandText;
                command.Connection = connection;
                command.CommandType = commandType;
                return command;
            }
            catch (Exception)
            {
                throw new Exception(“Invalid parameter ‘commandText’.”);
            }
        }
       
        public DbDataAdapter GetDataAdapter(IDbCommand command)
        {
            DbDataAdapter adapter = ProviderManager.Factory.CreateDataAdapter();
            adapter.SelectCommand = (DbCommand)command;
            adapter.InsertCommand = (DbCommand)command;
            adapter.UpdateCommand = (DbCommand)command;
            adapter.DeleteCommand = (DbCommand)command;
            return adapter;
        }
        public DbParameter GetParameter(string name, object value, DbType dbType)
        {
            try
            {
                DbParameter dbParam = ProviderManager.Factory.CreateParameter();
                dbParam.ParameterName = name;
                dbParam.Value = value;
                dbParam.Direction = ParameterDirection.Input;
                dbParam.DbType = dbType;
                return dbParam;
            }
            catch (Exception)
            {
                throw new Exception(“Invalid parameter or type.”);
            }
        }
        public DbParameter GetParameter(string name, object value, DbType dbType, ParameterDirection parameterDirection)
        {
            try
            {
                DbParameter dbParam = ProviderManager.Factory.CreateParameter();
                dbParam.ParameterName = name;
                dbParam.Value = value;
                dbParam.Direction = parameterDirection;
                dbParam.DbType = dbType;
                return dbParam;
            }
            catch (Exception)
            {
                throw new Exception(“Invalid parameter or type.”);
            }
        }
       
        public DbParameter GetParameter(string name, object value, DbType dbType, int size, ParameterDirection parameterDirection)
        {
            try
            {
                DbParameter dbParam = ProviderManager.Factory.CreateParameter();
                dbParam.ParameterName = name;
                dbParam.Value = value;
                dbParam.Size = size;
                dbParam.Direction = parameterDirection;
                dbParam.DbType = dbType;
                return dbParam;
            }
            catch (Exception)
            {
                throw new Exception(“Invalid parameter or type.”);
            }
        }
       
        #endregion
    }
}

Step 4: Create wrapper class to access database specific functions

This class used create the CRUD functions to return ADO.NET objects like DataTable, DataSet, DataReader and Scalar values. These functions internally call helper functions to create Database connection, Commands and DataAdapters. This wrapper class contains different overloads to support more functionality and database transactions as well.
I used this class same as my previouspost with smaller code changes.
using System;
using System.Data;
namespace DataAccessHandler
{
    public class DBManager
    {
        private DatabaseHelper database;
        public DBManager(string connectionStringName)
        {
            database = new DatabaseHelper(connectionStringName);
        }
        public IDbConnection GetDatabasecOnnection()
        {
            return database.GetConnection();
        }
       
        public void CloseConnection(IDbConnection connection)
        {
            database.CloseConnection(connection);
        }
        public IDbDataParameter CreateParameter(string name, object value, DbType dbType)
        {
            return database.GetParameter(name, value, dbType, ParameterDirection.Input);
        }
        public IDbDataParameter CreateParameter(string name, int size, object value, DbType dbType)
        {
            return database.GetParameter(name, value, dbType, size, ParameterDirection.Input);
        }
        public IDbDataParameter CreateParameter(string name, int size, object value, DbType dbType, ParameterDirection direction)
        {
            return database.GetParameter(name, value, dbType, size, direction);
        }
        public DataTable GetDataTable(string commandText, CommandType commandType, IDbDataParameter[] parameters = null)
        {
            using (var connection = database.GetConnection())
            {
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    var dataset = new DataSet();
                    var dataAdaper = database.GetDataAdapter(command);
                    dataAdaper.Fill(dataset);
                    return dataset.Tables[0];
                }
            }
        }
        public DataSet GetDataSet(string commandText, CommandType commandType, IDbDataParameter[] parameters = null)
        {
            using (var connection = database.GetConnection())
            {
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    var dataset = new DataSet();
                    var dataAdaper = database.GetDataAdapter(command);
                    dataAdaper.Fill(dataset);
                    return dataset;
                }
            }
        }
        public IDataReader GetDataReader(string commandText, CommandType commandType, IDbDataParameter[] parameters, out IDbConnection connection)
        {
            IDataReader reader = null;
            connection = database.GetConnection();
           
            var command = database.GetCommand(commandText, connection, commandType);
            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            reader = command.ExecuteReader();
           
            return reader;
        }
        public void Delete(string commandText, CommandType commandType, IDbDataParameter[] parameters = null)
        {
            using (var connection = database.GetConnection())
            {
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    command.ExecuteNonQuery();
                }
            }
        }
        public void Insert(string commandText, CommandType commandType, IDbDataParameter[] parameters)
        {
            using (var connection = database.GetConnection())
            {
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    command.ExecuteNonQuery();
                }
            }
        }
        public int Insert(string commandText, CommandType commandType, IDbDataParameter[] parameters, out int lastId)
        {
            lastId = 0;
            using (var connection = database.GetConnection())
            {
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    object newId = command.ExecuteScalar();
                    lastId = Convert.ToInt32(newId);
                }
            }
            return lastId;
        }
        public long Insert(string commandText, CommandType commandType, IDbDataParameter[] parameters, out long lastId)
        {
            lastId = 0;
            using (var connection = database.GetConnection())
            {
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    object newId = command.ExecuteScalar();
                    lastId = Convert.ToInt64(newId);
                }
            }
            return lastId;
        }
        public void InsertWithTransaction(string commandText, CommandType commandType, IDbDataParameter[] parameters)
        {
            IDbTransaction transactionScope = null;
            using (var connection = database.GetConnection())
            {
                transactionScope = connection.BeginTransaction();
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                        transactionScope.Commit();
                    }
                    catch (Exception)
                    {
                        transactionScope.Rollback();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        public void InsertWithTransaction(string commandText, CommandType commandType, IsolationLevel isolationLevel, IDbDataParameter[] parameters)
        {
            IDbTransaction transactionScope = null;
            using (var connection = database.GetConnection())
            {
                transactionScope = connection.BeginTransaction(isolationLevel);
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                        transactionScope.Commit();
                    }
                    catch (Exception)
                    {
                        transactionScope.Rollback();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        public void Update(string commandText, CommandType commandType, IDbDataParameter[] parameters)
        {
            using (var connection = database.GetConnection())
            {
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    command.ExecuteNonQuery();
                }
            }
        }
        public void UpdateWithTransaction(string commandText, CommandType commandType, IDbDataParameter[] parameters)
        {
            IDbTransaction transactionScope = null;
            using (var connection = database.GetConnection())
            {
                transactionScope = connection.BeginTransaction();
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                        transactionScope.Commit();
                    }
                    catch (Exception)
                    {
                        transactionScope.Rollback();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        public void UpdateWithTransaction(string commandText, CommandType commandType, IsolationLevel isolationLevel, IDbDataParameter[] parameters)
        {
            IDbTransaction transactionScope = null;
            using (var connection = database.GetConnection())
            {
                transactionScope = connection.BeginTransaction(isolationLevel);
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                        transactionScope.Commit();
                    }
                    catch (Exception)
                    {
                        transactionScope.Rollback();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        public object GetScalarValue(string commandText, CommandType commandType, IDbDataParameter[] parameters= null)
        {
            using (var connection = database.GetConnection())
            {
                using (var command = database.GetCommand(commandText, connection, commandType))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    returncommand.ExecuteScalar();
                }
            }
        }
    }
}
Step 5: Test the wrapper class using console application
First create the class specific to User table in database. We will use this class to Insert and Update and Delete functionalities.
I used this class same as my previouspost as it is.
using System;
namespace DataAccessHandler.ConsoleApp
{
    public class User
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime Dob { get; set; }
        public bool IsActive { get; set; }
    }
}
using System;
using System.Data;
using System.Collections.Generic;
namespace DataAccessHandler.ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(“================ Using Microsoft Provider Factory =================nnn”);
            UsingProviderFactory();
            Console.ReadKey();
        }
        private static void UsingProviderFactory()
        {
            var dbManager = new DBManager(“DBConnection”);
            var user = new User
            {
                FirstName = “First”,
                LastName = “Last”,
                Dob = DateTime.Now.AddDays(-3000),
                IsActive = true
            };
            var parameters = new List<IDbDataParameter>();
            parameters.Add(dbManager.CreateParameter(“@FirstName”, 50, user.FirstName, DbType.String));
            parameters.Add(dbManager.CreateParameter(“@LastName”, user.LastName, DbType.String));
            parameters.Add(dbManager.CreateParameter(“@Dob”, user.Dob, DbType.DateTime));
            parameters.Add(dbManager.CreateParameter(“@IsActive”, 50, user.IsActive, DbType.Boolean));
            //INSERT
            int lastId = 0;
            dbManager.Insert(“DAH_User_Insert”, CommandType.StoredProcedure, parameters.ToArray(), out lastId);
            Console.WriteLine(“nINSERTED ID: “ + lastId);
            //DATATABLE
            var dataTable = dbManager.GetDataTable(“DAH_User_GetAll”, CommandType.StoredProcedure);
            Console.WriteLine(“nTOTAL ROWS IN TABLE: “ + dataTable.Rows.Count);
            //DATAREADER
            IDbConnection connection = null;
            var dataReader = dbManager.GetDataReader(“DAH_User_GetAll”, CommandType.StoredProcedure, null, out connection);
            try
            {
                user = new User();
                while (dataReader.Read())
                {
                    user.FirstName = dataReader[“FirstName”].ToString();
                    user.LastName = dataReader[“LastName”].ToString();
                }
                Console.WriteLine(string.Format(“nDATA READER VALUES FirstName: {0} LastName: {1}”, user.FirstName, user.LastName));
            }
            catch (Exception)
            {
            }
            finally
            {
                dataReader.Close();
                dbManager.CloseConnection(connection);
            }
            //SCALAR
            object scalar = dbManager.GetScalarValue(“DAH_User_Scalar”, CommandType.StoredProcedure);
            Console.WriteLine(“nSCALAR VALUE: “ + scalar.ToString());
            //UPDATE
            user = new User
            {
                Id = lastId,
                FirstName = “First1”,
                LastName = “Last1”,
                Dob = DateTime.Now.AddDays(-5000)
            };
            parameters = new List<IDbDataParameter>();
            parameters.Add(dbManager.CreateParameter(“@Id”, user.Id, DbType.Int32));
            parameters.Add(dbManager.CreateParameter(“@FirstName”, 50, user.FirstName, DbType.String));
            parameters.Add(dbManager.CreateParameter(“@LastName”, user.LastName, DbType.String));
            parameters.Add(dbManager.CreateParameter(“@Dob”, user.Dob, DbType.DateTime));
            dbManager.Update(“DAH_User_Update”, CommandType.StoredProcedure, parameters.ToArray());
            //DATATABLE
            dataTable = dbManager.GetDataTable(“DAH_User_GetAll”, CommandType.StoredProcedure);
            Console.WriteLine(string.Format(“nUPADTED VALUES FirstName: {0} LastName: {1}”, dataTable.Rows[0][“FirstName”].ToString(), dataTable.Rows[0][“LastName”].ToString()));
            //DELETE
            parameters = new List<IDbDataParameter>();
            parameters.Add(dbManager.CreateParameter(“@Id”, user.Id, DbType.Int32));
            dbManager.Delete(“DAH_User_Delete”, CommandType.StoredProcedure, parameters.ToArray());
            Console.WriteLine(“nDELETED RECORD FOR ID: “ + user.Id);
            //DATATABLE
            dataTable = dbManager.GetDataTable(“DAH_User_GetAll”, CommandType.StoredProcedure);
            Console.WriteLine(“nTOTAL ROWS IN TABLE: “ + dataTable.Rows.Count);
        }
    }
}
Step 6: Output of Console Application

 

 

4.   Summary

In this article I have explained how to create generic data access layer in C# using DbProviderFactories. In this post we created separate class for ConfigurationSettings to set connection string and provider name, ProviderManager used to create instance of specific provider and DatabaseHelper used to create ADO.NET database access objects. This implementation approach has also saved too many efforts to create concrete database classes and thus reduced the complexity as well.
In my next post I will show you how SQLHelper class will be useful to those who are generally work with SQL Server database. The SQLHelperclass would be helpful for your live assignments. hope you enjoyed this article.

5.   Download Code

Click hereto download the source code of this article.

 

Leave a Reply