Introduction
To design software effectively we prefer to take a multilayer architecture. That approach has
been adopted by the industry to write efficient code. Change management becomes very easy in this style of coding. Let’s discuss the approach briefly.
A three-layer process model helps us to work efficiently initially. After some point of time if any change comes in the business logic that can be done easily by making the particular change on the particular layer. For example, the change in business logic layer will not affect the other two layers namely Data Access and UI.
How the three layers are connected?
Dataflow Diagram of Three Layer Architecture
Implementation of DAL
Before implementing DAL, you first need to know what DAL or Data Access Layer
is and what it contains.
In Data Access Layer, data may be any database (Oracle, SQL Server, Access, PostgreSQL, MySQL, etc.) or any file. That means, where you store the data. And this layer is
the one and only connectivity between your application and that data.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace DAL
{
class Database
{
internal string UserName { get; set; }
internal string Password { get; set; }
internal string ServerName { get; set; }
internal string DatabaseName { get; set; }
internal string ConnectionString
{
get { return "Data Source=" + ServerName + ";Initial Catalog=" +
DatabaseName + ";Persist Security Info=True;User ID=" +
UserName + ";Password=" + Password; }
}
public bool InsertUpdateDelete(string sql, Dictionary parameters, bool isProcedure)
{
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
{
if (isProcedure)
sqlCommand.CommandType =
System.Data.CommandType.StoredProcedure;
else sqlCommand.CommandType = System.Data.CommandType.Text;
foreach (KeyValuePair parameter in parameters)
sqlCommand.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
if (sqlCommand.ExecuteNonQuery() > 0) return true;
else return false;
}
}
}
public DataTable Select(string sql, bool isProcedure, Dictionary parameters = null)
{
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
{
if (isProcedure) sqlCommand.CommandType = CommandType.StoredProcedure;
else sqlCommand.CommandType = CommandType.Text;
if (parameters != null)
foreach (KeyValuePair parameter in parameters)
sqlCommand.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
using (DataTable dt = new DataTable())
{
sqlDataAdapter.Fill(dt);
return dt;
}
}
}
}
}
}
}
Implementation of BLL
Before implementing BLL, you first need to know what BLL or Business Logic Layer
is and what it contains.
Roughly speaking, you can think a 'name of the table' as a 'name of the class', 'name of the columns' as a 'name of the data members or properties' and the DDL and DML operations as methods of that class.
Let's implement what I've said:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;
namespace BLL
{
class EmployeeBLL
{
public long EmployeeId { get; set; }
public string EmployeeName { get; set; }
public string EmployeeAddress { get; set; }
public double Salary { get; set; }
public DateTime DOJ { get; set; }
public bool InsertEmployee()
{
Database db = InitializeDatabase();
string sqlQuery = "INSERT INTO [Employee] ";
sqlQuery += "(EmployeeName, EmployeeAddress, Salary, DOJ) ";
sqlQuery += "VALUES ";
sqlQuery += "(@EmployeeName, @EmployeeAddress, @Salary, @DOJ)";
Dictionary parameters = new Dictionary();
parameters.Add("@EmployeeName", EmployeeName);
parameters.Add("@EmployeeAddress", EmployeeAddress);
parameters.Add("@Salary", Salary);
parameters.Add("@DOJ", DOJ);
return db.InsertUpdateDelete(sqlQuery, parameters, false);
}
public bool UpdateEmployee()
{
Database db = InitializeDatabase();
string sqlQuery = "UPDATE [Employee] SET ";
sqlQuery += "EmployeeName=@EmployeeName, EmployeeAddress=@EmployeeAddress, Salary=@Salary, DOJ=@DOJ ";
sqlQuery += "WHERE EmployeeId=@EmployeeId";
Dictionary parameters = new Dictionary();
parameters.Add("@EmployeeId", EmployeeId);
parameters.Add("@EmployeeName", EmployeeName);
parameters.Add("@EmployeeAddress", EmployeeAddress);
parameters.Add("@Salary", Salary);
parameters.Add("@DOJ", DOJ);
return db.InsertUpdateDelete(sqlQuery, parameters, false);
}
public bool DeleteEmployee()
{
Database db = InitializeDatabase();
string sqlQuery = "DELETE [Employee] WHERE EmployeeId=@EmployeeId";
Dictionary parameters = new Dictionary();
parameters.Add("@EmployeeId", EmployeeId);
return db.InsertUpdateDelete(sqlQuery, parameters, false);
}
public DataTable SelectEmployee()
{
Database db = InitializeDatabase();
string sqlQuery = "SELECT EmployeeId AS Id, EmployeeName AS Name, " +
"EmployeeAddress AS Address, Salary, DOJ AS 'Joining Date' FROM Employee";
return db.Select(sqlQuery, false);
}
public DataRow SelectEmployeeById()
{
Database db = InitializeDatabase();
string sqlQuery = "SELECT EmployeeName AS Name, EmployeeAddress " +
"AS Address, Salary, DOJ AS 'Joining Date' ";
sqlQuery += "FROM Employee WHERE EmployeeId=@EmployeeId";
Dictionary parameters = new Dictionary();
parameters.Add("@EmployeeId", EmployeeId);
return db.Select(sqlQuery, false, parameters).AsEnumerable().First();
}
private Database InitializeDatabase()
{
Database db = new Database();
db.UserName = "sa";
db.Password = "server2008";
db.ServerName = "(local)";
db.DatabaseName = "DebopamDB";
return db;
}
}
}
Implementation of UI Layer
UI Layer is the User Interface Layer. This layer contains Web Forms and its controls for Web
based applications. And Windows Forms and its controls for Desktop based applications.
I've implemented a Desktop based application demo. In the next version I'll show you how you can implement
a Web based application using the same DAL and BLL that I've implemented here.
The Main form
Behind the Main Form:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using BLL;
namespace ThreeLayerArchitectureDemo
{
public partial class FormManageEmployee : Form
{
EmployeeBLL employee = new EmployeeBLL();
public FormManageEmployee()
{
InitializeComponent();
}
private void FormManageEmployee_Load(object sender, EventArgs e)
{
FillGridView();
}
private void btnInsert_Click(object sender, EventArgs e)
{
using (FormEditUpdateEmployee frmEditUpdateEmp = new FormEditUpdateEmployee())
{
frmEditUpdateEmp.EmployeeId = 0;
if (frmEditUpdateEmp.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
employee = frmEditUpdateEmp.ToEmployee();
if (employee.InsertEmployee())
{
FillGridView();
MessageBox.Show("Successfully Inserted", "Success",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
MessageBox.Show("Error during Inserting", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else return;
}
}
private void FillGridView()
{
grdViewEmployee.DataSource = employee.SelectEmployee();
}
private void btnEdit_Click(object sender, EventArgs e)
{
using (FormEditUpdateEmployee frmEditUpdateEmp = new FormEditUpdateEmployee())
{
frmEditUpdateEmp.EmployeeId = long.Parse(grdViewEmployee.CurrentRow.Cells["Id"].Value.ToString());
if (frmEditUpdateEmp.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
employee = frmEditUpdateEmp.ToEmployee();
if (employee.UpdateEmployee())
{
FillGridView();
MessageBox.Show("Successfully Updated",
"Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
MessageBox.Show("Error during Updating",
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
return;
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
employee.EmployeeId = long.Parse(grdViewEmployee.CurrentRow.Cells["Id"].Value.ToString());
employee.EmployeeName = grdViewEmployee.CurrentRow.Cells["Name"].Value.ToString();
if (MessageBox.Show("Delete " + employee.EmployeeName + "?",
"Delete Employee?", MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
if (employee.DeleteEmployee())
{
FillGridView();
MessageBox.Show("Successfully Deleted",
"Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
MessageBox.Show("Error during Deleting",
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
else
return;
}
}
}
The Insert/Edit Form
Behind the Insert/Edit form:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using BLL;
namespace ThreeLayerArchitectureDemo
{
public partial class FormEditUpdateEmployee : Form
{
EmployeeBLL employee = new EmployeeBLL();
public long EmployeeId
{
get { return long.Parse(this.txtName.Tag.ToString()); }
set
{
if (value == 0)
{
this.txtName.Text = null;
this.txtName.Tag = 0;
this.txtAddress.Text = null;
this.txtSalary.Text = null;
this.dateDOJ.Value = DateTime.Today.Date;
}
else
{
employee.EmployeeId = value;
DataRow dr = employee.SelectEmployeeById();
this.txtName.Text = dr["Name"].ToString();
this.txtName.Tag = value;
this.txtAddress.Text = dr["Address"].ToString();
this.txtSalary.Text = dr["Salary"].ToString();
this.dateDOJ.Value = DateTime.Parse(dr["Joining Date"].ToString());
}
}
}
internal EmployeeBLL ToEmployee()
{
employee.EmployeeId = long.Parse(this.txtName.Tag.ToString());
employee.EmployeeName = this.txtName.Text;
employee.EmployeeAddress = this.txtAddress.Text;
employee.Salary = double.Parse(this.txtSalary.Text);
employee.DOJ = this.dateDOJ.Value;
return employee;
}
public FormEditUpdateEmployee()
{
InitializeComponent();
}
private void btnSave_Click(object sender, EventArgs e)
{
this.DialogResult = System.Windows.Forms.DialogResult.OK;
this.Close();
}
private void btnCancel_Click(object sender, EventArgs e)
{
this.DialogResult = System.Windows.Forms.DialogResult.Cancel;
this.Close();
}
}
}
Advantages of Using Three Layer Architecture
- Scalability: The application servers can be deployed on many machines, the database no longer requires a connection from every client.
- Reusability: You can reuse the Business Logic Layer (BLL) i.e. middle layer with different user interfaces like ASP.NET Web Form, ASP.NET Web Service, .NET Window Form etc. You can reuse the Database Access Layer (DAL) i.e. top layer with different project.
- Improve Data Integrity: The BLL i.e. middle layer can ensure that only valid data is allowed to be inserted, updated or deleted in the database.
- Improve Security: Since the client does not have direct access to the database. BLL is more generally secure since it is placed on a more secure central server.
- Reduce Distribution: Changes to BLL/DLL only need to be updated on application server & do not have to be disturbed to all clients.
- Hidden Database Structure: Since the actual structure of the database is hidden from the user.
- Improve Availability
Disadvantages of Using Three Layer Architecture
- Increase developer’s complexity and effort
- In general 3-Layer architecture is more complex to build compared to 2-Layer
architecture
- Point of communication is doubled
Points of Interest
When you implement this code you'll also learn to use
Dictionary
,
using
, and
internal
in C#.
History