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 projectInteract.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
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!?
- Related Article: SQL Framework
Thank You!