Tuesday, December 31, 2013

Database Factory Pattern

Hi All,

Happy holidays to all of you and wish you a great new year ahead..
After one week of long break.. just thought of writing something..

I have written my fist blog on most favorite topic How to see who visited my Facebook page and second one is Tools-for-Microsoft.NET 2012.  After these I am thinking of writing something on advance topic.

Design Patterns !! Subject which is close to my heart (now a days).  Being a Techie .. close to my heart subject often changes form one to another :)

In Design Patterns - I have chosen Database Factory Pattern.  The reason behind choosing this topic is 90% developers need to connect to one or the other database.  In this blog I thought of writing each and every detail about Database Factory Pattern and off course providing you source code too...  :)

OK.. then lets Start..

What is Design Pattern - (As mentioned in Wikipedia )
In software engineering, a design pattern is a general reusable solution to a commonly occurring problem within a given context in software design.
In a layman language it's just a mold. You create it once and use it in many places with no or a minor change.

What is Database Factory Pattern - (I didn't find any industry standard definition, so I have just created my own for our blog )
Database factory pattern is a creation of reusable components for database transaction. 
There are 6 components always involved in case of any type of database transactions.



Now we will check how we will arrange these components in our Microsoft.NET 2012 solution.

I am taking here simple example of "Employee Management System", where we will perform two operations 
1. Add Employee
2. View Employees
I am creating one project which will do all the work related to database transactions, and name it as a "DataAccessLayer.csproj".   Create another project for our core business requirement "EmployeeManagementSystem.csproj".   

"DataAccessLayer.csproj" project is our Database Factrory and "EmployeeManagementSystem"(EMS) is going to use factory("DataAccessLayer.csproj") to do all of it's database transactions. We can have any number of projects like EMS (EmployeeManagementSystem) in our solution, which will be using same code provided in the Factory(DataAccessLayer.csproj"). 

EMS contains "ClsDBConnect.cs" which is the single class who talk to Database Factory (DataAccessLayer.csproj).


Now we will see each components in details 

1. AbstractDbComponents : This class will hold all abstract classes related to database. 



In this example, I have created abstract methods for opening connection, create procedure, command etc. 

Class name : AbsDbComponents.cs


2. Database Factory : This class is a factory class this will be use to create database connection and return database object to "EmployeeManagementSystem.csproj"

This class is going to call "DatabaseFactorySectionHandler.cs" internally to get configuration settings.

Class name : ClsDBFactory.cs



// Get Section details defined in config file. 
private static readonly ClsDBSectionHandler SectionHandler = (ClsDBSectionHandler)ConfigurationManager.GetSection("DatabaseFactoryConfiguration");


// Create Database 
public static AbsDbComponents CreateDatabase()
{
                // Find the class
                Type database = Type.GetType("DataAccessLayer.ClsDBSql");

                // Get it's constructor
                ConstructorInfo constructor = database.GetConstructor(new Type[] { });

                // Invoke it's constructor, which returns an instance.
                var createdObject = (AbsDbComponents)constructor.Invoke(null);

                // Initialize the connection string property for the database.
                createdObject.ConnectionString = SectionHandler.ConnectionString;

                // Pass back the instance as a Database

                return createdObject;
}

3. GlobalConnect: This is public class which will call Database Factory's CreateDatabase() method. In case of patterns, it is best programming practice to create one entry point for external classes, this is our entry point for EMS.



Class name : ClsDbGlobalConnect.cs

4. DatabaseFactorySectionHandlerIn case of patterns, it is best programming practice to create your own section in configuration file and section handler classes to invoke those files. I have created here ClsDBSectionHandler.cs class to write a code for section handler. 

Note : Special precaution need to be taken in case of writing your own configuration Section handling in C#. This is ideal strategy for writing database design pattern, If you find it very difficult to write your own section handling, "ConnectionString" tag can be use in Config files, to start with.. 

Class name : ClsDBSectionHandler.cs
Other related files : EmployeeManagementSystem\App.config

 public sealed class ClsDBSectionHandler : ConfigurationSection
    {
        [ConfigurationProperty("Name")]
        public string Name
        {
            get { return (string)base["Name"]; }
        }

        [ConfigurationProperty("ConnectionStringName")]
        public string ConnectionStringName
        {
            get { return (string)base["ConnectionStringName"]; }
        }

        public string ConnectionString
        {
            get
            {
                try
                {
                    return ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
                }
                catch (Exception excep)
                {
                    throw new Exception("Connection string " + ConnectionStringName + " was not found in web.config. " + excep.Message);
                }
            }
        }
    }

5. SQLDatabase : This class is derived from AbsDbComponents.cs, where we have defined all abstract methods required in EMS. 
public class ClsDBSql : AbsDbComponents
This will implement CreateOpenConnection(), CreateStoredProcCommand() and all other abstract methods. 

 public override IDbCommand CreateCommand(string commandText, IDbConnection connection)
        {
            var command = (SqlCommand)CreateCommand();

            command.CommandText = commandText;
            command.Connection = (SqlConnection)connection;
            command.CommandType = CommandType.Text;

            return command;

        }

Class name : ClsDBSql.cs
Same way we need to write a code to create connection, create parameter etc.

6. Data Consumer

This class is a part of Employee Management System and single point of contact to talk to database. 


        ///
        /// Insert Employee Information into the database
        ///
        /// Employee First Name
        /// Employee Last Name
        /// Employee ID
        public void InsertIntoEmployeeTable(string employeeFirstName, string employeeLastName, 
        string employeeId)
        {
            // Stored procedure's 'In' Parameters 
            var param1 = Database.CreateParameter("@employeeFirstName", employeeFirstName);
            var param2 = Database.CreateParameter("@employeeLastName", employeeLastName);
            var param3 = Database.CreateParameter("@employeeID", employeeId);
            try
            {
                // Stored Procedure name
                const string spName = "sp_InsertToEmployeeTable";

                // Call to ExecuteSP()
                ExecuteSP(spName, param1, param2, param3);
            }
            catch (Exception ex)
            {
                //Log Exception
                throw;
            }            

        }

Once you have created pattern you just need to write few lines of code to connect to database and do any kind of operation. 

In addition to this I have created  dynamic stroed procedure so that it will work from one to several parameter with the same code. 

 private void ExecuteSP(string spName, params IDataParameter[] paramList)
        {
            // Connection object
            using (IDbConnection connection = Database.CreateOpenConnection())
            {
                // Command object
                using (IDbCommand command = Database.CreateStoredProcCommand(spName,                         connection))
                {
                    // Add parameters to command object
                    foreach (IDataParameter t in paramList)
                        command.Parameters.Add(t);

                    // ExecuteNonQuery
                    command.ExecuteNonQuery();
                }
            }
        }

Class name : ClsDBConnect.cs

EmployeManagementSystem.csprj


We will create a form with name "frmEmployeeDetails.cs".  This is just User Interface created to show all these transactions at once place 

This is just a simple form with 3 fields First Name, Last Name and Employee ID, with 2 buttons Add Employee and Show Employee. 


When click on Add employee it is calling stored procedure "sp_InsertToEmployeeTable" and when clicked on view employee it is calling "sp_ReadFromEmployeeTable" stored procedure. 




When we finish doing coding, project structure should be like this 


Database Structure 

We will create "tblEmployee" table in database with the following fields





We will create 2 stroed procedure one to insert employee details and another to view employee list.

1. sp_InsertToEmployeeTable

USE [Employee]
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertToEmployeeTable]    Script Date: 12/31/2013 4:31:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_InsertToEmployeeTable] (@employeeFirstName NVARCHAR(45), @employeeLastName nvarchar(50), @employeeID nvarchar(50)) AS
BEGIN
BEGIN TRANSACTION;
BEGIN
  INSERT INTO dbo.tblEmployee VALUES (@employeeFirstName, @employeeLastName, @employeeID)
END
COMMIT;

END

1. sp_ReadFromEmployeeTable


USE [Employee]

GO

/****** Object:  StoredProcedure [dbo].[sp_ReadFromEmployeeTable]    Script Date: 12/31/2013 4:32:42 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_ReadFromEmployeeTable] 

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT * FROM [tblEmployee](NOLOCK)   
END

Combining all these blocks together creates sound proof Database Design Pattern and which will be use forever no matter which database you use, which queries you fire and how many parameters you need in your stored procedure. 

Hope this blog will help you to learn design pattern .. and help you in your coding.... 

Download Source Code - Click here for source code