Saturday, August 18, 2012


Introduction

Framework is essential to develop robust software in an organized way. Well organized software framework makes application easy to develop, enhance and modify with less effort. Framework nature can be of different types depending on business need.

Prerequisite

  • You need to have AdventureWorks sample database on your MS-SQL Server.

Instruction

  • Run SQL from SQL Script folder to AdventureWorks sample database on your MS-SQL Server.

Background

In this demonstration I will create a software application framework which will be used to interact with application through a single point and all of the functionalities will be accessible hierarchically. In robust applications single point access is used for simplicity and reduces complexity. Why hierarchical? I have the bellow underlying database.

 Figure 1

There are six tables in the underlying database. I need to provide four methods for each table. The first one for Add data to database, the second one is Update data to database, the third one is for Get All data from the underlying database table and the last and fourth one is GeById from database. So, four methods for each table and the number of tables is six. So the equation is: 6*4 =24. So the total number of methods for database operation for the above underlying database is 24. Now, if I want to access the entire 24 methods trough a single point then bellow figure will be appeared.


 Figure 2

Entire 24 methods in the above list. If the underlying database has 100 tables and if you have to provide 4 methods for each through a single then the equation is 100*4=400. So, 400 methods will be in the list. The number of methods will be more for more robust database. That big number of methods will not be easy to handle for developers. If we can access that number of methods hierarchically then we can reduce the length of the list like the bellow figure:

 Figure 3

Let’s Get Started

Create a blank using Visual Studio 2010 where C# is the language and named it Interact. Add a class library project into this newly created solution. Name the project Interact.Common. This project is for all common classes, managers and helpers for the framework. Now create a mapping class for underlying database tables which is called mapping class or entity class in the Interect.Common project. Before this you need to create a BaseEntity class which is the base class for all entity classes. This base class contains some of the properties.

namespace Interact.Common
{    
    public abstract class BaseEntity
    {
        public virtual int Id
        {
            get;
            set;
        }

        public int CreatorId
        {
            get;
            set;
        }

        public int UpdatorId
        {
            get;
            set;
        }

        public virtual bool IsNew
        {            
            get
            {
                return (Id <= 0);
            }
        }

        public DateTime CreateDate
        {
            get;
            set;
        }

        public DateTime UpdateDate
        {
            get;
            set;
        }

        protected BaseEntity()
        {
            CreateDate = UpdateDate = DateTime.Now;
        }        
    }
}



I have created an entity class for Department table. Below is the code for Department entity. Four properties for four fields in the class body and inherit this class from BaseEntity class. Here is the Department table.


Figure 4

And below is the mapping (entity) class code for the Department table.

 namespace Interact.Common
{
    public class Department:BaseEntity
    {
        public Int16 DepartmentID { get; set; }
        public String Name { get; set; }
        public String GroupName { get; set; }
        public DateTime ModifiedDate{ get; set; }
    }
}
Compile the Interact.Common project and make sure it error free. 
Create entity classes as you need them. You should name the entity class the 
same as the table name to maintain common standard that’s you need to careful 
when you design database. Now create the interface for each table. Each 
interface proves fours methods: add, get, get by id and update. You will create as you need them. Now adding a new project in the Interact solution and name it 
Interact.Interface. Get the Interact.Common project reference into this project. 


Figure 5
I have created Interface for Department table. Below is the code: 

using System;
using Interact.Common;
using System.Collections.Generic;

Here is the code for IDepartment interface. You should follow this naming concentration for all of your interfaces for common standard. In this case IDepartment. Prefix I for interface for each table name. 


namespace Interact.Interface
{
    public interface IDepartment
    {
        Department Add(Department department);
        Department Update(Department department);
        IList<Department> Get();
        Department GetById(Department department);
    }
}

using Interact.Common;

The Interact. Common namespace is for entity classes. Department Add(Department department); The Add method takes an argument of Department entity type and returns an object of the same type. This method is use for data insertion in the Department table. I will discuss more about it later in this article. Department Update(Department department); 


The Update also takes an argument of the Department entity type and returns an 
object of the same type. This method is use for record updating in the 
Department table. 
using System.Collections.Generic;

The above namespace is for I have used IList interface and List class in the below Get() method. 

IList<Department> Get();

The above method is for getting all the records from Department tables as List. 
Department GetById(Department department); Now about the GetById method. This method takes an argument of Department type and return same Department type. Now crating another interface IInteract in this project which will be used for create hierarchal way. In this interface create one read only property for each interface.Code bellow: 


namespace Interact.Interface
{
    public interface IInteract
    {
        //Human Resource
        IDepartment Department
        {
            get;
        }

        IEmployee Employee
        {
            get;
        }

        //Sales
        ICustomer Customer
        {
            get;
        }

        IProduct Product
        {
            get;
        }


        //Production
        IProductInventory ProductInventory
        {
            get;
        }

        ISalesPerson SalesPerson
        {
            get;
        }

    }
}

Compile the solution. 

Apart from this, I am discussing data access for the application framework. I 
used Microsoft Enterprise Library for data access. You know, it is very easy to use with less effort. It is a Microsoft pattern and Patrice also. First we need toconfigure it, which is very easy. I have uploaded MicrosoftEnterpriseLibrary.dll in source folder. In app.config/web.config it only needs a connection string to connect you to the database server. 

<connectionStrings>    <add name="InteractConnString" connectionString="Data Source=Mamun-PC\SQL2008    EXPR2;Initial Catalog=AdventureWorks;Integrated Security=True" providerName="    System.Data.SqlClient"/>
 </connectionStrings>
I have named it InteractConnString. 

Now adding data access project Interact.DataAccess in the Interact solution. First you need to add reference of Interact.Common and Microsoft.Practices.EnterpriseLibrary.Data.dll into this project. 



Figure 6
You need to have a base data access class in the project which will be the base class for all data access classes. Take a class and named it BaseDataAccess.cs. Add the bellow namespaces in the class.
using System;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

Here is the class body. 
namespace Interact.DataAccess
{
    public abstract class BaseDataAccess
    {
        private Database _db;

        #region Properties

        protected virtual Database Database
        {
            get
            {
                if (_db == null)
                {        
//your connection string name          
  _db = DatabaseFactory.CreateDatabase("InteractConnString");
                }

                return _db;
            }
        }

        #endregion

        #region Constructer & Destructer

        protected BaseDataAccess()
        {

        }

        ~BaseDataAccess()
        {
            GC.SuppressFinalize(this);
        }

        #endregion
    }
}

Now take a class for data access DepartmentDataAccess.cs for Department 
table which is responsible for data access. This class is derived from the 
BaseDataAccess class. You should maintain this naming convention (tablename+DataAccess) for all data access classes. 

using System;
using System.Data;
using Interact.Common;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Interact.DataAccess
{
    public class DepartmentDataAccess:BaseDataAccess
    {
    }
}
Now create all the four methods in the DepartmentDataAccess class which have defined in the IDepartment interface. 

public interface IDepartment
    {
        Department Add(Department department);
        Department Update(Department department);
        IList<Department> Get();
        Department GetById(Department department);
    }

Before creating the Add method, it needs to create store procedure for data insertion. To know more about store procedures and the SQL Framework you need read first SQL Framework. 


Here is the store procedure for data insertion [Department_Set]. 
Naming convention table name underscore Set(Tablename+ underscore+ Set). 

CREATE PROCEDURE [HumanResources].[Department_Set]
 --Input values
 @Name as varchar(50),
    @GroupName as varchar(50),    
 @ModifiedDate as DateTime 
ASSET NOCOUNT ONBEGIN
 --Constraint Variables For Readable Return Value  
 DECLARE @SUCCESSED int,
  @FAILED int   
 
 BEGIN TRY
  --Data insertion
  INSERT INTO [HumanResources].[Department]
           ([Name]
           ,[GroupName]
           ,[ModifiedDate])
  VALUES
           (@Name
           ,@GroupName
           ,@ModifiedDate)   
   
   SELECT TOP 1 * FROM [HumanResources].Department ORDER BY DepartmentID  DESC
 END TRY
 BEGIN CATCH -- Error Trapping Section
  DECLARE @ErrorMessage nvarchar(4000);
  DECLARE @ErrorSeverity int;
  DECLARE @ErrorState int;
  
  SELECT 
   @ErrorMessage = ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE();
  
  RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
  RETURN @FAILED  -- Readable Failed Return
 END CATCH

 RETURN @SUCCESSED -- Readable Success Return
ENDGO


So now the Add method. 

public Department Add(Department department)
        {
            Department departmentReturn = new Department();

            const String SP = "[HumanResources].[Department_Set]";

            using (DbCommand cmd = Database.GetStoredProcCommand(SP))
            {
                Database.AddInParameter(cmd, "@Name", DbType.String, department.Name);
                Database.AddInParameter(cmd, "@GroupName", DbType.String, department.GroupName);
                Database.AddInParameter(cmd, "@ModifiedDate", DbType.DateTime, department.CreateDate);

                using (IDataReader reader = Database.ExecuteReader(cmd))
                {
                    if (reader.Read())
                    {
                        Fill(departmentReturn, reader);
                    }                    
                }
            }

            return departmentReturn;
        }

The below line of statement in the Add method above sets store procedure to 
execute. HumanResources schema name. You may have dbo or others. 

const String SP = "[HumanResources].[Department_Set]";

The below statement returns the DBCommand object all through Microsoft.Practices.EnterpriseLibrary. Here Database is the property from BaseDataAccess class. 

using (DbCommand cmd = Database.GetStoredProcCommand(SP))

Now you need to set parameters for the command object. 

Database.AddInParameter(cmd, "@Name", DbType.String, department.Name);
                Database.AddInParameter(cmd, "@GroupName", DbType.String, department.GroupName);
                Database.AddInParameter(cmd, "@ModifiedDate", DbType.DateTime, department.CreateDate);

There are three input parameters for the [HumanResources].[Department_Set] store procedure. The Department column in the Department table is Identity that’s 
it doesn’t need to supply. 

The below line of the C# statement executes cmd through Microsoft.Practices.EnterpriseLibrary and returns a IDataReader type of object. 

using (IDataReader reader = Database.ExecuteReader(cmd))

In the [HumanResources].[Department_Set] the below SQL statement returns the lastinserted record to the framework. 

SELECT TOP 1 * FROM [HumanResources].Department ORDER BY DepartmentID  DESC

The reader object will not be null. And also the Add method returns a Department type of object. The return Department record needs to bind or map to the Department entity class which is in the Interact.Common project. The below Fill binds theDataReader to Department object. This is the helper method. 

private static void Fill(Department department, IDataReader reader)
        {
            const Int16 CONST_DEPARTMENT_ID=0;
            const Int16 CONST_NAME = 1;
            const Int16 CONST_GROUP_NAME = 2;
            const Int16 CONST_MODIFIED_DATE = 3;

            department.DepartmentID = reader.IsDBNull(CONST_DEPARTMENT_ID) ? Int16.MinValue : reader.GetInt16(CONST_DEPARTMENT_ID);
            department.Name = reader.IsDBNull(CONST_NAME) ? String.Empty : reader.GetString(CONST_NAME);
            department.GroupName = reader.IsDBNull(CONST_GROUP_NAME) ? String.Empty : reader.GetString(CONST_GROUP_NAME);
            department.ModifiedDate = reader.IsDBNull(CONST_MODIFIED_DATE) ? DateTime.MinValue : reader.GetDateTime(CONST_MODIFIED_DATE);
        }

The above method first declares the number of constants according to your database table. In this case the Department table. This is only for simplicity. The restof the statements in the method for to bind data to Department entity class fields after null check.

Now the Update Method. First create the store procedure for data updating. To know more about store procedure and SQL Framework you need read first SQL Framework.

CREATE PROCEDURE [HumanResources].[Department_Update]
 --Input values
 @Id as smallint,
 @Name as varchar(50),
    @GroupName as varchar(50),    
 @ModifiedDate as DateTime
 
ASSET NOCOUNT ONBEGIN
 --Constraint Variables For Readable Return Value  
 DECLARE @SUCCESSED int,
  @FAILED int     
 
 BEGIN TRY  
  
  --Data upddation
  UPDATE [HumanResources].[Department]
           SET [Name]=@Name
     ,GroupName=@GroupName           
     ,ModifiedDate=@ModifiedDate
     WHERE DepartmentID=@Id  
   
   SELECT * FROM [HumanResources].[Department] WHERE DepartmentID=@Id
 END TRY
 BEGIN CATCH -- Error Trapping Section
  DECLARE @ErrorMessage nvarchar(4000);
  DECLARE @ErrorSeverity int;
  DECLARE @ErrorState int;
  
  SELECT 
   @ErrorMessage = ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE();
  
  RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
  RETURN @FAILED  -- Readable Failed Return
 END CATCH

 RETURN @SUCCESSED -- Readable Success Return
END
GO

Here is the Update in the DepartmentDataAccess class. 

public Department Update(Department department)
        {
            Department departmentReturn = new Department();

            const String SP = "[HumanResources].[Department_Update]";

            using (DbCommand cmd = Database.GetStoredProcCommand(SP))
            {
                Database.AddInParameter(cmd, "@Id", DbType.Int16, department.DepartmentID);
                Database.AddInParameter(cmd, "@Name", DbType.String, department.Name);
                Database.AddInParameter(cmd, "@GroupName", DbType.String, department.GroupName);
                Database.AddInParameter(cmd, "@ModifiedDate", DbType.DateTime, department.UpdateDate);

                using (IDataReader reader = Database.ExecuteReader(cmd))
                {
                    if (reader.Read())
                    {
                        Fill(departmentReturn, reader);
                    }
                }
            }

            return departmentReturn;
        }


The method also return Department type after data updating which is the updating record. The [HumanResources].[Department_Get] stored procedure returns all the records from Department table to framework after that this makes it a collection ofdepartment list and send back to the user interface. Here is the store procedure.To know more about store procedure and SQL Framework you need read first SQL Framework.  

CREATE PROCEDURE [HumanResources].[Department_Get] 
ASBEGIN
 SET NOCOUNT ON
 --Variables
 DECLARE @SUCCESSED int,
  @FAILED int   
 
 BEGIN TRY
  --Get constraint value
  EXEC ReturnMessage @SUCCESSED OUTPUT, @FAILED OUTPUT   
  
  --Data retrieval 
  SELECT * FROM [HumanResources].[Department]    
   
 END TRY
 BEGIN CATCH
  DECLARE @ErrorMessage nvarchar(4000);
  DECLARE @ErrorSeverity int;
  DECLARE @ErrorState int;
  
  SELECT 
   @ErrorMessage = ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE();  
  
  RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
  RETURN @FAILED
 END CATCH

 RETURN @SUCCESSED
ENDGO
GO
Here is the GetAll method which corresponds to the Get method.
        public IList<Department> GetAll()
        {
            const string SP = "[HumanResources].[Department_Get]";

            using (DbCommand cmd = Database.GetStoredProcCommand(SP))
            {
                using (IDataReader reader = Database.ExecuteReader(cmd))
                {
                    IList<Department> list = new Listlt;Department>();

                    Fill(reader, list);

                    return list;
                }
            }
        }


And the last method is GetById. Here is the stored procedure. To learn more aboutstored procedures and the SQL Framework you need to read SQL Framework. 

CREATE PROCEDURE [HumanResources].[Department_GetById]
 --Input values
 @Id as smallint 
 
ASSET NOCOUNT ONBEGIN
 --Constraint Variables For Readable Return Value  
 DECLARE @SUCCESSED int,
  @FAILED int     
 
 BEGIN TRY   
   
 SELECT * FROM [HumanResources].[Department] WHERE DepartmentID=@Id
 
 END TRY
 BEGIN CATCH -- Error Trapping Section
  DECLARE @ErrorMessage nvarchar(4000);
  DECLARE @ErrorSeverity int;
  DECLARE @ErrorState int;
  
  SELECT 
   @ErrorMessage = ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE();
  
  RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
  RETURN @FAILED  -- Readable Failed Return
 END CATCH

 RETURN @SUCCESSED -- Readable Success Return
END

Here is the GetById method. 

public Department GetById(Department department)
        {
            Department departmentReturn = new Department();

            const string SP = "[HumanResources].[Department_GetById]";

            using (DbCommand cmd = Database.GetStoredProcCommand(SP))
            {
                Database.AddInParameter(cmd, "@Id", DbType.Int16, department.DepartmentID);

                using (IDataReader reader = Database.ExecuteReader(cmd))
                {
                    if (reader.Read())
                    {
                        Fill(departmentReturn, reader);                        
                    }                    
                }
            }
            return departmentReturn;
        }


Compile the solution. 

Figure 7
Add a class file and named it Implement.cs. Add the following namespace.
using System;
using Interact.Common;
using Interact.Interface;
using Interact.DataAccess;
using System.Collections.Generic;

Here is the Implement class which implements IDepartment interface and other interfaces. 

namespace Interact.Implement
{
    public class Implement : IDepartment    
   {
   }
}

Other interfaces as your requirement. 

public class Implement : IDepartment, IEmployee, ICustomer, IProduct, IProductInventory, ISalesPerson, IUtility

Now you have to provide the bodies of all four methods of IDepartment interface as for Implement class inherits IDepartment interface. Those method bodies actually call the Data Access layer and provide outcome to user interface. So you need create an instance for each data access class here. I have done this the bellow way.

 #region Properties

        DepartmentDataAccess _departmentDataAccess;
        private DepartmentDataAccess DepartmentDataAccess
        {
            get
            {
                if (_departmentDataAccess == null)
                {
                    _departmentDataAccess = new DepartmentDataAccess();
                }
                return _departmentDataAccess;
            }
        }

        #endregion
Now the body of Add method. 
 Department IDepartment.Add(Department department)
        {
            Department departmentReturn = new Department();

            departmentReturn = DepartmentDataAccess.Add(department);

            return departmentReturn;
        }

Update Method. 

      Department IDepartment.Update(Department department)
        {
            Department departmentReturn = new Department();

            departmentReturn = DepartmentDataAccess.Update(department);

            return departmentReturn;
        }

The Get method. 

        IList<department> IDepartment.Get()
        {
            IList<department> departmentList = new List<department>();

            departmentList = DepartmentDataAccess.GetAll();

            return departmentList;
        }
And Get By Id method. 
        Department IDepartment.GetById(Department department)
        {
            Department departmentReturn = new Department();

            departmentReturn = DepartmentDataAccess.GetById(department);

            return departmentReturn;
        }

Now creating another class MainImplement in this project for creating hierarchal way which implements IInteract interface from the Interact.Interface project. Create a public interface for each interface which returns a new instance of Implement class. For the IDepartment interface. 

       public IDepartment Department
        {
            get
            {
                return new Implement();
            }
        }

For all interfaces. 

namespace Interact.Implement
{
    public class MainImplement : IInteract
    {
        //Human Resource
        public IDepartment Department
        {
            get
            {
                return new Implement();
            }
        }


        public IEmployee Employee
        {
            get
            {
                return new Implement();
            }
        }

        //Sales
        public ICustomer Customer
        {
            get
            {
                return new Implement();
            }
        }

        public ISalesPerson SalesPerson
        {
            get
            {
                return new Implement();
            }
        }


        //Production
        public IProduct Product
        {
            get
            {
                return new Implement();
            }
        }

        public IProductInventory ProductInventory
        {
            get
            {
                return new Implement();
            }
        }
    }
}

Compile the solution. So the backend has been successfully created. Now the 
front-end. Take a website project Interact.Web.UI in the solution. Take a class, BaseWebForm, in the project which implements System.Web.UI.Page and which will beacting as a base form for all of the web forms. 

Get references from Interact.Common,Interact.Implement and Interact.Interface into this project. 

Figure 8

Adding namespace in BaseWebForm in base web from class. 

using System;
using Interact.Interface;
using Interact.Implement;

Creating a protected read only property which will provide IInteract type of object for all of its derived class which is the single point to access. 

IInteract _interact;

        protected IInteract Interact
        {
            get
            {
                return new MainImplement();
            }
        }

Now adding a DepartmentForm.aspx web form in this project which inherits from BaseWebForm. 

namespace Interact.Web.UI
{
    public partial class DepartmentForm : BaseWebForm
    {
    }
}

Now create user interface as you like. 

Figure 9
Here is the code for Get All button click event 

  protected void btnGetAll_Click(object sender, EventArgs e)
        {
            ClearFields();
            BindGrid();
        }

Here is the code for BindGrid(). 

private void BindGrid()
        {
            IList<Department> departmentList = new List<Department>();
            departmentList = Interact.Department.Get();

            gvDepartment.DataSource = departmentList;
            gvDepartment.DataBind();

            gvDepartment.Caption = String.Format("{0} Record(s) found.", departmentList.Count);
        }

Now run the application and click on the Get All button. And get the bellow outpu
t. 

Figure 10

Click on edit button in the grid.

Figure 11
Make the necessary change.
Figure 12 Click on Update button.
protected void btnUpdate_Click(object sender, EventArgs e)
        {
            Department department = new Department();

            department.DepartmentID =Int16.Parse(lblId.Text.Trim());
            department.Name = txtName.Text.Trim();
            department.GroupName = txtGroupName.Text.Trim();

            Department departmentResult = new Department();
            departmentResult = Interact.Department.Update(department);

            if (departmentResult != null)
            {
                lblMsg.Text = String.Format("Department {0} has been updated.", departmentResult.DepartmentID);
                ClearFields();
            }
            else
            {
                lblMsg.Text = "Error!";
            }
            BindGrid();
        }

Here is the output. 

Figure 13
Now Adding now record.
Figure 14 Click on Add button.
 protected void btnAdd_Click(object sender, EventArgs e)
        {
            Department department = new Department();

            department.Name = txtName.Text.Trim();
            department.GroupName = txtGroupName.Text.Trim();

            Department departmentResult = new Department();
            departmentResult = Interact.Department.Add(department);

            if (departmentResult != null)
            {
                lblMsg.Text = String.Format("Department {0} has been setup.", departmentResult.DepartmentID);
                ClearFields();
            }
            else
            {
                lblMsg.Text = "Error!";
            }
            BindGrid();
        }
Figure 15
WOW! The demonstration is done. Let’s try you. Was that demonstration 
helpful!?