May 02

Generic Data Access Layer in C# using Factory Pattern

Generic Data Access Layer in C# using Factory Pattern

1.   Introduction

A data access layer is an important part of any software application. Typically, a large enterprise(N-Tier) application will have one or more databases to store the data. On the top of these databases the Data Access Layer(DAL) is created. A business layer which contains business logic and classes represents the business domain used to communicate with data access layer. A service layer is used to expose the business layer to the client and finally some user interface like AP.NET Web, Windows Form or Windows service.

 

This post is about how you can develop a generic data access layer (DAL) to access different types of database with full CRUD (Create, Read, Update and Delete) support using different type of ADO.NET objects.

2.   Overview

 
ADO.Net Provides consistent access to databases like Microsoft SQL Server, Oracle as well as data sources exposed through OLE DB and XML. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update data. The ADO.NET classes are found in System.Data.dll.
The ADO.NET components have been designed to factor data access from data manipulation. There are two central components of ADO.NET that accomplish this: the DataSet, and the .NET Framework data provider, which is a set of components including the Connection, Command, DataReader, and DataAdapter objects.
 
The ADO.NET DataSet is the core component of the disconnected architecture of ADO.NET. The DataSet is explicitly designed for data access independent of any data source. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint, and relation information about the data in the DataTable objects.
The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. The DataReader provides a high-performance stream of data from the data source. Finally, the DataAdapter provides the bridge between the DataSet object and the data source. The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data, and reconcile changes made to the data in the DataSet back to the data source.
Database Objects:
1.    ConnectionEstablishes a connection to a specific data source
2.    Comman – Executes a command against a data source
3.    DataReader – Reads a forward-only, read-only stream of data from a data source.
4.    DataAdapter – Populates a data set and resolves updates with the data source
Abstracting Data Access:
ADO.NET is designed around a set of generic interfaces that abstract the underlying data processing functionality. You can use these interfaces directly to abstract your data access layer so that you can minimize the impact of changing the type of data source that you use. Abstracting data access is extremely helpful when you are designing systems where your customer chooses the database server.
The core interfaces provided by ADO.NET are found in the System.Data namespace
 
·         IDbConnection – This is an interface for managing database connections.
·         IDbCommand – This is an interface for running SQL commands.
·         IDbTransaction This is an interface for managing transactions.
·         IDataReader This is an interface for reading data returned by a command.
·         IDataAdapter This is an interface for channelling data to and from datasets.
 
       The following table shows the provider used to access different types of databases in ADO.NET
 
Database
Parameter
SQL Server
Oracle
OLE DB
ODBC
Data Provider
SqlClient
OracleClient
OleDb
Odbc
Namespace
System.Data.SqlClient
System.Data.OracleClient
System.Data.OleDb
System.Data.Odbc
Connection
SqlConnection
OracleConnection
OleDbConnection
OdbcConnection
Command
SqlCommand
OracleCommand
OleDbCommand
OdbcCommand
Data Adapter
SqlDataAdapter
OracleDataAdapter
OleDbDataAdapter
OdbcDataAdaper
Data Parameter
SqlParameter
OracleParameter

3.   Practical Implementation

 
In this example I have created interface IDatabaseHandler which contains basic functions required to assess data using ADO.NET objects like IDbConnection, IDbCommand, IDataAdapter and IDbDataParameter We need to implement these interfaces in child classes.

    Step 1: Create generic interface IDatabaseHandler

using System.Data;
namespace DataAccessHandler
{
    public interface IDatabaseHandler
    {
        IDbConnection CreateConnection();
        void CloseConnection(IDbConnectionconnection);
        IDbCommand CreateCommand(string commandText, CommandType commandType, IDbConnection connection);
        IDataAdapter CreateAdapter(IDbCommand command);
        IDbDataParameter CreateParameter(IDbCommand command);
    }
}
     Step 2: Implement IDatabaseHandler interface for different type of data access
 
1.       SQL Data Access
 
using System.Data;
using System.Data.SqlClient;
namespace DataAccessHandler
{
    public class SqlDataAccess : IDatabaseHandler
    {
        private string ConnectionString { get; set; }
        public SqlDataAccess(string connectionString)
        {
            ConnectionString = connectionString;
        }
        public IDbConnection CreateConnection()
        {
            return new SqlConnection(ConnectionString);
        }
        public void CloseConnection(IDbConnection connection)
        {
            var sqlConnection = (SqlConnection)connection;
            sqlConnection.Close();
            sqlConnection.Dispose();
        }
        public IDbCommand CreateCommand(string commandText, CommandType commandType, IDbConnection connection)
        {
            return new SqlCommand
            {
                CommandText = commandText,
                Connection = (SqlConnection)connection,
                CommandType = commandType
            };
        }
        public IDataAdapter CreateAdapter(IDbCommand command)
        {
            return new SqlDataAdapter((SqlCommand)command);
        }
        public IDbDataParameter CreateParameter(IDbCommand command)
        {
            SqlCommand SQLcommand = (SqlCommand)command;
            return SQLcommand.CreateParameter();
        }
    }
}
2.       Oracle Data Access
 
using System.Data;
using System.Data.OracleClient;
namespace DataAccessHandler
{
    public class OracleDataAccess : IDatabaseHandler
    {
        private string ConnectionString { get; set; }
        public OracleDataAccess(string connectionString)
        {
            ConnectionString = connectionString;
        }
        public IDbConnection CreateConnection()
        {
            return new OracleConnection(ConnectionString);
        }
        public void CloseConnection(IDbConnection connection)
        {
            var oracleConnection = (OracleConnection)connection;
            oracleConnection.Close();
            oracleConnection.Dispose();
        }
        public IDbCommand CreateCommand(string commandText, CommandType commandType, IDbConnection connection)
        {
            return new OracleCommand
            {
                CommandText = commandText,
                Connection = (OracleConnection)connection,
                CommandType = commandType
            };
        }
        public IDataAdapter CreateAdapter(IDbCommand command)
        {
            return new OracleDataAdapter((OracleCommand)command);
        }
        public IDbDataParameter CreateParameter(IDbCommand command)
        {
            OracleCommand SQLcommand = (OracleCommand)command;
            return SQLcommand.CreateParameter();
        }
    }
}
3.       OLEDB Data Access
 
using System.Data;
using System.Data.OleDb;
namespace DataAccessHandler
{
    public class OledbDataAccess : IDatabaseHandler
    {
        private string ConnectionString { get; set; }
        public OledbDataAccess(string connectionString)
        {
            ConnectionString = connectionString;
        }
        public IDbConnection CreateConnection()
        {
            return new OleDbConnection(ConnectionString);
        }
        public void CloseConnection(IDbConnectionconnection)
        {
            var oleDbConnection = (OleDbConnection)connection;
            oleDbConnection.Close();
            oleDbConnection.Dispose();
        }
        public IDbCommand CreateCommand(string commandText, CommandType commandType, IDbConnection connection)
        {
            return new OleDbCommand
            {
                CommandText = commandText,
                Connection = (OleDbConnection)connection,
                CommandType = commandType
            };
        }
        public IDataAdapter CreateAdapter(IDbCommand command)
        {
            return new OleDbDataAdapter((OleDbCommand)command);
        }
        public IDbDataParameter CreateParameter(IDbCommand command)
        {
            OleDbCommand SQLcommand = (OleDbCommand)command;
            return SQLcommand.CreateParameter();
        }
    }
}
4.       ODBC Data Access
 
using System.Data;
using System.Data.Odbc;
namespace DataAccessHandler
{
    public class OdbcDataAccess : IDatabaseHandler
    {
        private string ConnectionString { get; set; }
        public OdbcDataAccess(string connectionString)
        {
            ConnectionString = connectionString;
        }
        public IDbConnection CreateConnection()
        {
            return new OdbcConnection(ConnectionString);
        }
        public void CloseConnection(IDbConnection connection)
        {
            var odbcConnection = (OdbcConnection)connection;
            odbcConnection.Close();
            odbcConnection.Dispose();
        }
        public IDbCommand CreateCommand(string commandText, CommandType commandType, IDbConnection connection)
        {
            return new OdbcCommand
            {
                CommandText = commandText,
                Connection = (OdbcConnection)connection,
                CommandType = commandType
            };
        }
        public IDataAdapter CreateAdapter(IDbCommand command)
        {
            return new OdbcDataAdapter((OdbcCommand)command);
        }
        public IDbDataParameter CreateParameter(IDbCommand command)
        {
            OdbcCommand SQLcommand = (OdbcCommand)command;
            return SQLcommand.CreateParameter();
        }
    }
}
Step 3: Create separate class for parameter creation
 
I have created separate class for parameter creation. This would be useful while creation of parameters collection and these collections would be passes to CRUD methods. You can change this as per your need.
 
Here I have created different type of overloaded methods to create parameters and set their values.
 
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
namespace DataAccessHandler
{
    public class DataParameterManager
    {
        public static IDbDataParameter CreateParameter(string providerName, string name, object value, DbType dbType, ParameterDirection direction = ParameterDirection.Input)
        {
            IDbDataParameter parameter = null;
            switch (providerName.ToLower())
            {
                case “system.data.sqlclient”:
                    returnCreateSqlParameter(name, value, dbType, direction);
                case “system.data.oracleclient”:
                    returnCreateOracleParameter(name, value, dbType, direction);
                case “system.data.oleDb”:
                    break;
                case “system.data.odbc”:
                    break;
            }
            return parameter;
        }
        public static IDbDataParameterCreateParameter(string providerName, string name, int size, object value, DbType dbType, ParameterDirection direction = ParameterDirection.Input)
        {
            IDbDataParameter parameter = null;
            switch (providerName.ToLower())
            {
                case “system.data.sqlclient”:
                    returnCreateSqlParameter(name, size, value, dbType, direction);
                case “system.data.oracleclient”:
                    return CreateOracleParameter(name, size, value, dbType, direction);
                case “system.data.oleDb”:
                    break;
                case “system.data.odbc”:
                    break;
            }
            return parameter;
        }
        private static IDbDataParameter CreateSqlParameter(string name, object value, DbType dbType, ParameterDirection direction)
        {
            return new SqlParameter
            {
                DbType = dbType,
                ParameterName = name,
                Direction = direction,
                Value = value
            };
        }
      
        private static IDbDataParameter CreateSqlParameter(string name, int size, object value, DbType dbType, ParameterDirection direction)
        {
            return new SqlParameter
            {
                DbType = dbType,
                Size = size,
                ParameterName = name,
                Direction = direction,
                Value = value
            };
        }
        private static IDbDataParameter CreateOracleParameter(string name, object value, DbType dbType, ParameterDirection direction)
        {
            return new OracleParameter
            {
                DbType = dbType,
                ParameterName = name,
                Direction = direction,
                Value = value
            };
        }
        private static IDbDataParameter CreateOracleParameter(string name, int size, object value, DbType dbType, ParameterDirection direction)
        {
            return new OracleParameter
            {
                DbType = dbType,
                Size = size,
                ParameterName = name,
                Direction = direction,
                Value = value
            };
        }
    }
}
Step 4: Create factory pattern class to create instance of specific database
 
This class used to create instance of specific database using provider name (mentioned in hardcoded strings). This provider name is mentioned in the web/app.config files and this is must to mention provider details in config file
 
using System.Configuration;
namespace DataAccessHandler
{
    public class DatabaseHandlerFactory
    {
        private ConnectionStringSettings connectionStringSettings;
        public DatabaseHandlerFactory(string connectionStringName)
        {
            connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];
        }
        public IDatabaseHandler CreateDatabase()
        {
            IDatabaseHandler database = null;
            switch (connectionStringSettings.ProviderName.ToLower())
            {
                case “system.data.sqlclient”:
                    database = new SqlDataAccess(connectionStringSettings.ConnectionString);
                        break;
                case “system.data.oracleclient”:
                        database = new OracleDataAccess(connectionStringSettings.ConnectionString);
                        break;
                case “system.data.oleDb”:
                        database = new OledbDataAccess(connectionStringSettings.ConnectionString);
                        break;
                case “system.data.odbc”:
                        database = new OdbcDataAccess(connectionStringSettings.ConnectionString);
                        break;
            }
            return database;
        }
        public string GetProviderName()
        {
            return connectionStringSettings.ProviderName;
        }
    }
}
Step 5: Create wrapper class to access database specific functions
 
In this class the constructor of wrapper class creates will create instance of DatabaseHandlerFactory class and will set the ConnectionStringSettings property which contains the database connection string and name of provider. Later call the CreateDatabase() function of DatabaseHandlerFactoryclass to create database specific instance.
Now, create the CRUD functions to return ADO.NET objects like DataTable, DataSet, DataReader and Scalar values. These functions internally call base functions to create Database connection, Commands and DataAdapters. This wrapper class contains different overloads to support more functionality and database transactions as well.
using System;
using System.Data;
namespace DataAccessHandler
{
    public class DBManager
    {
        private DatabaseHandlerFactory dbFactory;
        private IDatabaseHandler database;
        private string providerName;
        public DBManager(string connectionStringName)
        {
            dbFactory = new DatabaseHandlerFactory(connectionStringName);
            database = dbFactory.CreateDatabase();
            providerName = dbFactory.GetProviderName();
        }
        public IDbConnection GetDatabasecOnnection()
        {
            return database.CreateConnection();
        }
       
        public void CloseConnection(IDbConnectionconnection)
        {
            database.CloseConnection(connection);
        }
        public IDbDataParameter CreateParameter(string name, object value, DbType dbType)
        {
            return DataParameterManager.CreateParameter(providerName, name, value, dbType, ParameterDirection.Input);
        }
        public IDbDataParameter CreateParameter(string name, int size, object value, DbType dbType)
        {
            return DataParameterManager.CreateParameter(providerName, name, size, value, dbType, ParameterDirection.Input);
        }
        public IDbDataParameter CreateParameter(string name, int size, object value, DbType dbType, ParameterDirection direction)
        {
            return DataParameterManager.CreateParameter(providerName, name, size, value, dbType, direction);
        }
        public DataTable GetDataTable(string commandText, CommandType commandType, IDbDataParameter[] parameters = null)
        {
            using (var connection = database.CreateConnection())
            {
                connection.Open();
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    var dataset = new DataSet();
                    var dataAdaper = database.CreateAdapter(command);
                    dataAdaper.Fill(dataset);
                    return dataset.Tables[0];
                }
            }
        }
        public DataSet GetDataSet(string commandText, CommandType commandType, IDbDataParameter[] parameters = null)
        {
            using (var connection = database.CreateConnection())
            {
                connection.Open();
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    var dataset = new DataSet();
                    var dataAdaper = database.CreateAdapter(command);
                    dataAdaper.Fill(dataset);
                    return dataset;
                }
            }
        }
        public IDataReader GetDataReader(string commandText, CommandType commandType, IDbDataParameter[] parameters, out IDbConnection connection)
        {
            IDataReader reader = null;
            connection = database.CreateConnection();
            connection.Open();
            var command = database.CreateCommand(commandText, commandType, connection);
            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.CreateConnection())
            {
                connection.Open();
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    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.CreateConnection())
            {
                connection.Open();
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    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.CreateConnection())
            {
                connection.Open();
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    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.CreateConnection())
            {
                connection.Open();
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    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.CreateConnection())
            {
                connection.Open();
                transactionScope = connection.BeginTransaction();
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    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.CreateConnection())
            {
                connection.Open();
                transactionScope = connection.BeginTransaction(isolationLevel);
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    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.CreateConnection())
            {
                connection.Open();
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    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.CreateConnection())
            {
                connection.Open();
                transactionScope = connection.BeginTransaction();
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    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.CreateConnection())
            {
                connection.Open();
                transactionScope = connection.BeginTransaction(isolationLevel);
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    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.CreateConnection())
            {
                connection.Open();
                using (var command = database.CreateCommand(commandText, commandType, connection))
                {
                    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.
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; }
    }
}
Now, create the program to test wrapper class. Here we will create instance of DBManager class by passing connection string name only.
using System;
using System.Data;
using System.Collections.Generic;
namespace DataAccessHandler.ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(“================ Using Database Factory =================nnn”);
            UsingDatabaseFactory();
            Console.ReadKey();
        }
        private static void UsingDatabaseFactory()
        {
            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 factory pattern. In this post we created separate classes for each type of database and hence it increased the complexity as well. Microsoft has also provided the DbProviderFactory class in System.Data.Common namespace. This class is uses to create instance for specific provider and this instance is used to create functions like CreateParameter, CreateDataAdapter, CreateCommand and CreateConnection. In my next post I will show you how DBManager class will use DbProviderFactoryclass to create instances of different databases. hope you enjoyed this article. If you like this article don’t forget to put your valuable comments.

5.   Download Code

Click hereto download the source code of this article.

 

Leave a Reply