There are many articles on 3 tier architecture on the internet however still I felt there is a need of more refined and well explained article. So here is this, in this article we are going to look into almost all aspects of designing a 3 tier architecture website in ASP.NET.
Introduction
Writing all code into the code file of ASP.NET or presentation layer of desktop or any other application makes an application less maintainable, scalable and extendible. Apart from this there are many other disadvantages of keeping all types of code into the same file.
N-tier application is the solution of this problem where separation of concern methodology is followed where a single tier is responsible for a single type of job. N-tier means more than one tier of application where the code of different types of jobs are written into different tiers (or projects).
This article will not explain the advantages or disadvantages of 3 tier architecture as the possibility of you are here and reading this article you already know the advantages.
In this article, we will learn how to create 3-tier architecture application (that is very popular) in ASP.NET web form. Even if this is designed in ASP.NET Web Form website, the same approach can be followed for any other type of application. The only thing changes is presentation layer; in web application or website, the presentation layer is ASP.NET Web Form; in Desktop application, the presentation layer if Windows Form and so on.
Objective
The objective of this article is to create a CRUD application using 3 tier architecture in ASP.NET Web Form with C# of a Person database table whose structure is same as displayed in picture below.
3 tier architecture application solution structure
A normal 3 tier architecture application has following tiers
- Presentation Layer - ASP.NET Web Form project that contains .aspx, .ascx and other types of files that is helpful to create a User Interface (UI) of the application or website or any other type of application.
- Business Access Layer (BAL) - This tier contains business logic, secondary validations, calculations and call to Data Access Layer methods if needed. If there is no business logic and any other types of validations to be performed, it simply calls Data Access Layer on need basis.
- Data Access Layer (DAL) - This tier contains necessary code to connect to the database and perform database activities (such as insert, update, delete, load records) and returns result to the Business Access Layer.
So here is the typical solution structure of a 3 tier architecture application solution.
Designing 3 tier architecture solution
For this demo, we have created a website and we are going to take that as an example and learn the complete process of creating 3 tier architecture solution.
So open Visual Studio and go to File > New Web Site > select Visual C# from left panel under Templates and select ASP.NET Empty Web Site template. Click OK and your solution explorer would look something similar to below picture.
Adding Business Access Layer (BAL) and Data Access Layer (DAL) tiers
To add other layers like Business Access Layer (BAL) and Data Access Layer (DAL), follow below step as displayed in the this picture.
Right click your Solution and go to Add > New Project ... In the Add New Project dialogue box, select Windows from left panel and Class Library from the Template, write the Name and Location of the Project to create and click OK. Ideally, Web Project, BAL and DAL all should be into the same parent folder.
After adding BAL and DAL, the project structure looks like below picture. Where 3-tier is our Presentation Layer and BAL is Business Access Layer and DAL is Data Access Layer projects.
By default when we add a Class Library, we get Class1.cs file so we have to rename that so that it looks more meaningful like it is done in this demo solution.
Lets create with Data Access Layer (DAL) first
Our DAL for Person database table looks like below where we have following methods to perform respective operation against Person database table.
- Insert - Insert record into Person database table
- Update - Update record into Person database table
- Delete - Delete record from Person database table
- LoadAll - Load all records from the Person database table
- Load - Load a single record from Person database table
Below is the code snippet of PersonDAL.cs file from Data Access Layer (DAL).
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
namespace DAL
{
public class PersonDAL
{
static string _connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
/// <summary>
/// Insert record into the database
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="age"></param>
/// <returns></returns>
public int Insert(string firstName, string lastName, int age)
{
var returnValue = 0;
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand dCmd = new SqlCommand("InsertPerson", conn))
{
dCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] prms = new SqlParameter[3];
prms[0] = new SqlParameter("@FirstName", SqlDbType.VarChar, 50);
prms[0].Value = firstName;
prms[1] = new SqlParameter("@LastName", SqlDbType.VarChar, 50);
prms[1].Value = lastName;
prms[2] = new SqlParameter("@Age", SqlDbType.Int);
prms[2].Value = age;
dCmd.Parameters.AddRange(prms);
conn.Open();
returnValue = dCmd.ExecuteNonQuery();
conn.Close();
}
}
return returnValue;
}
/// <summary>
/// Update record into the database
/// </summary>
/// <param name="autoId"></param>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="age"></param>
/// <returns></returns>
public int Update(int autoId, string firstName, string lastName, int age)
{
var returnValue = 0;
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand dCmd = new SqlCommand("UpdatePerson", conn))
{
dCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] prms = new SqlParameter[4];
prms[0] = new SqlParameter("@FirstName", SqlDbType.VarChar, 50);
prms[0].Value = firstName;
prms[1] = new SqlParameter("@LastName", SqlDbType.VarChar, 50);
prms[1].Value = lastName;
prms[2] = new SqlParameter("@Age", SqlDbType.Int);
prms[2].Value = age;
prms[3] = new SqlParameter("@AutoId", SqlDbType.Int);
prms[3].Value = autoId;
dCmd.Parameters.AddRange(prms);
conn.Open();
returnValue = dCmd.ExecuteNonQuery();
conn.Close();
}
}
return returnValue;
}
/// <summary>
/// Delete record from the database tbale
/// </summary>
/// <param name="autoId"></param>
/// <returns></returns>
public int Delete(int autoId)
{
var returnValue = 0;
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand dCmd = new SqlCommand("DeletePerson", conn))
{
dCmd.CommandType = CommandType.StoredProcedure;
SqlParameter prm = new SqlParameter("@AutoId", SqlDbType.Int);
prm.Value = autoId;
dCmd.Parameters.Add(prm);
conn.Open();
returnValue = dCmd.ExecuteNonQuery();
conn.Close();
}
}
return returnValue;
}
/// <summary>
/// Load all records from database table
/// </summary>
/// <returns></returns>
public DataTable LoadAll()
{
DataTable table = new DataTable();
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlDataAdapter ad = new SqlDataAdapter("LoadAll", conn))
{
ad.SelectCommand.CommandType = CommandType.StoredProcedure;
ad.Fill(table);
}
}
return table;
}
/// <summary>
/// Load a single records from database table
/// </summary>
/// <returns></returns>
public DataTable Load(int autoId)
{
DataTable table = new DataTable();
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlDataAdapter ad = new SqlDataAdapter("LoadPerson", conn))
{
ad.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter prm = new SqlParameter("@AutoId", SqlDbType.Int);
prm.Value = autoId;
ad.SelectCommand.Parameters.Add(prm);
ad.Fill(table);
}
}
return table;
}
}
}
PersonDAL.cs file
First we have a static variable for the database connection string that we have stored in the web.config file (of Presentation Layer, in this case ASP.NET Web Form). In order to get it we need to reference System.Configuration namespace. To do that Right click the DAL project and select Add Reference ... and select the namespace as displayed in the picture below.
Methods in PersonDAL.cs file
Insert
It accepts 3 parameters and uses SqlConnection, SqlCommand and other SqlParameter objects and execute InsertPerson stored procedure to insert record into the database. It also returns number of records affected by the Stored Procedure.
InsertPerson stored procedure
CREATE PROCEDURE [dbo].[InsertPerson]
@FirstName varchar(50),
@LastName varchar(50),
@Age int
AS
BEGIN
SET NOCOUNT OFF; -- Keep it off to returns no. of records affected
INSERT INTO [dbo].[Person]
([FirstName]
,[LastName]
,[Age])
VALUES
(@FirstName,
@LastName,
@Age)
ENDUpdate
It accepts four parameters and executes UpdatePerosn stored procedure the way we have done in Insert method to update record into the database.
UpdatePerson stored procedure
CREATE PROCEDURE [dbo].[UpdatePerson]
@AutoId int,
@FirstName varchar(50),
@LastName varchar(50),
@Age int
AS
BEGIN
SET NOCOUNT OFF; -- Keep it off to returns no. of records affected
UPDATE [dbo].[Person]
SET [FirstName] = @FirstName
,[LastName] = @LastName
,[Age] = @Age
WHERE AutoId = @AutoId
END
Delete
It accepts autoId as parameter and executes DeletePerson stored procedure to delete record from the database.
DeletePerson stored procedure
CREATE PROCEDURE [dbo].[DeletePerson]
@AutoId int
AS
BEGIN
SET NOCOUNT OFF; -- Keep it off to returns no. of records affected
DELETE Person WHERE AutoId = @AutoId
END
LoadAll
It simply executes LoadAll stored procedure and returns all records from the Person database table.
CREATE PROCEDURE [dbo].[LoadAll]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT AutoId, FirstName, LastName, Age FROM Person ORDER BY AutoId DESC
END
Load
It accepts autoId parameter and executes LoadPerson stored procedure to return a single record from the Person database table.
ALTER PROCEDURE [dbo].[LoadPerson]
-- Add the parameters for the stored procedure here
@AutoId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT AutoId, FirstName, LastName, Age FROM Person WHERE AutoId = @AutoId
END
Creating Business Access Layer (BAL)
As explained above, BAL is responsible for business logic, secondary validation, calculations and Data Access if any. In our case we have no business logic or calculations so this will simply call the respective Data Access Layer methods and returns the data to Presentation layer.
In order to call the Data Access Layer methods, we will need to add the DAL project reference to this project so do that as shown in the picture below by right clicking the BAL project and selecting Add Reference ... .
Below is the code snippet for PersonBAL.cs file from BAL.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DAL;
namespace BAL
{
public class PersonBAL
{
/// <summary>
/// Insert record into the database
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="age"></param>
/// <returns></returns>
public int Insert(string firstName, string lastName, int age)
{
// your businees logic, calculations or validations may go here
return new PersonDAL().Insert(firstName, lastName, age);
}
/// <summary>
/// Update record into the database
/// </summary>
/// <param name="autoId"></param>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="age"></param>
/// <returns></returns>
public int Update(int autoId, string firstName, string lastName, int age)
{
// your businees logic, calculations or validations may go here
return new PersonDAL().Update(autoId, firstName, lastName, age);
}
/// <summary>
/// Delete record from the database tbale
/// </summary>
/// <param name="autoId"></param>
/// <returns></returns>
public int Delete(int autoId)
{
// your businees logic, calculations or validations may go here
return new PersonDAL().Delete(autoId);
}
/// <summary>
/// Load all records from database table
/// </summary>
/// <returns></returns>
public DataTable LoadAll()
{
// your businees logic, calculations or validations may go here
return new PersonDAL().LoadAll();
}
/// <summary>
/// Load a single records from database table
/// </summary>
/// <returns></returns>
public DataTable Load(int autoId)
{
// your businees logic, calculations or validations may go here
return new PersonDAL().Load(autoId);
}
}
}
This has again the same method names as in the PersonDAL.cs file
- Insert - Calls Insert method of the Data Access Layer
- Update - Calls Update method of the Data Access Layer
- Delete - Calls Delete method of the Data Access Layer
- LoadAll - Calls LoadAll method of the Data Access Layer
- Load - Calls Load method of the Data Access Layer
The inside code of these methods are just calling respective methods of DAL so separate explanations of each method might not make much sense so it has been skipped here.
Creating Presentation Layer
As explained above, presentation layer is primarily responsible for giving a nice clean user interface to the end user to interact with the application and to perform necessary functionality. It also contains primary validation of the data to ensure that only valid data is going to BAL.
To avoid complexity of this demo project, validation logic and validation controls have been avoided.
Inserting a record - Insert.aspx
The code to create a form to insert a record into the Person database table is below.
<form id="form1" runat="server">
<h1>Insert Record</h1>
<div>
<p>First Name: <asp:TextBox ID="txtFirstName" runat="server" /></p>
<p>Last Name: <asp:TextBox ID="txtLastName" runat="server" /></p>
<p>Age: <asp:TextBox ID="txtAge" runat="server" /></p>
<asp:Button ID="btnSave" runat="server" OnClick="btnSave_Click" Text="Save" />
</div>
<p><asp:Label ID="lblMessage" runat="server" EnableViewState="false" /></p>
</form>Clicking on the Save buttons fires btnSave_Click method of the code file of Insert.aspx and below is the code for that.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BAL;
public partial class Insert : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSave_Click(object sender, EventArgs e)
{
var result = 0;
try
{
// Validate your data, if any
int age = 0;
int.TryParse(txtAge.Text, out age);
result = new PersonBAL().Insert(txtFirstName.Text.Trim(), txtLastName.Text.Trim(), age);
if (result > 0)
{
lblMessage.Text = "Record inserted successfully !";
}
else
{
lblMessage.Text = "No record afftected.";
}
}
catch (Exception ee)
{
lblMessage.Text = "Error occured. " + ee.Message;
}
}
}In above code snippet, first we are converting age textbox value to the integer value and calling the
Insert method of BAL. Based on what value the method is returning, we are writing message to the Label control. In this case if a record is inserted 1 will be returned.
As we are calling BAL method inside the try catch block so if any error occurs in either BAL or even in the DAL it will be caught and displayed into the Label.
Listing records from the database - default.aspx
In this page, we will list records into
GridView and to facilitate users to see complete details and update a record we will have two more columns added named
Details and
Update. Clicking on these column links sends user to Details.aspx and Update.aspx page respectively.
<asp:Label ID="lblMessage" runat="server" EnableViewState="false" />
<asp:GridView ID="GridView1" runat="server" EnableViewState="false">
<Columns>
<asp:TemplateField HeaderText="Details">
<ItemTemplate>
<a href="details.aspx?autoid=<%# Eval("AutoId") %>">Details</a>
</span>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Update">
<ItemTemplate>
<a href="update.aspx?autoid=<%# Eval("AutoId") %>">Update</a>
</span>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>The code file of default.aspx file looks like below
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BAL;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadRecords();
}
}
private void LoadRecords()
{
try
{
DataTable table = new PersonBAL().LoadAll();
GridView1.DataSource = table;
GridView1.DataBind();
}
catch (Exception ee)
{
lblMessage.Text = ee.Message;
}
}
}In the above code snippet, we are calling
LoadRecords() method if the request is not Post back. In
LoadRecords() method, we are calling
LoadAll() method of BAL and returning
DataTable and then the same is being set as DataSource of the GridView that lists records from the
DataTable in tabular structure on the page.
Seeing details of the record - Details.aspx
This uses
DetailsView control and displays record details in tabular format. This also has a
Delete button to delete the record in case user want it.
Delete button is wrapped into
span element that helps us to get a confirmation from end user before calling
btnDelete_Click method of the code file.
<asp:Label ID="lblMessage" runat="server" EnableViewState="false" />
<asp:DetailsView ID="DetailsView1" runat="server" EnableViewState="false"></asp:DetailsView>
<p>
<span onclick="return confirm('Are you sure to delete?')">
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" />
</span>
</p>The code snippet of Details.aspx code file.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BAL;
public partial class Details : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack && !string.IsNullOrWhiteSpace(Request.QueryString["autoid"]))
{
ViewState["AutoId"] = Request.QueryString["autoid"];
var autoId = int.Parse(Request.QueryString["autoid"]);
ShowDtails(autoId);
}
}
private void ShowDtails(int autoId)
{
try
{
DetailsView1.DataSource = new PersonBAL().Load(autoId);
DetailsView1.DataBind();
}
catch (Exception ee)
{
lblMessage.Text = ee.Message;
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
if (ViewState["AutoId"] == null)
{
return;
}
var result = 0;
try
{
var autoId = int.Parse(ViewState["AutoId"].ToString());
result = new PersonBAL().Delete(autoId);
if (result > 0)
{
lblMessage.Text = "Record deleted successfully !";
btnDelete.Visible = false;
}
else
{
lblMessage.Text = "No record affected.";
}
}
catch (Exception ee)
{
lblMessage.Text = ee.Message;
}
}
}Page_Load method
It checks if the request is not post back and there is a querystring coming in to this page then we are setting the value of querystring to the ViewState["AutoId"] that will be used to delete this record later on. Then we are calling the ShowDetails method by passing autoId as parameter.
ShowDetails method
This method calls the Load method of the BAL and bind the data into DetailsView control.
btnDelete_Click method
It checks for ViewState["AutoId"] and if it is null (means not a valid request to delete the record), it simply breaks the method by calling return. If not, it calls Delete method of the BAL and write proper message.
Updating record - update.aspx
This page receives autoid as parameter and populate the record into the form for editing. The code for this form is below.
<form id="form1" runat="server">
<h1>Update Record</h1>
<div>
<p>First Name: <asp:TextBox ID="txtFirstName" runat="server" /></p>
<p>Last Name: <asp:TextBox ID="txtLastName" runat="server" /></p>
<p>Age: <asp:TextBox ID="txtAge" runat="server" /></p>
<asp:Button ID="btnSave" runat="server" OnClick="btnSave_Click" Text="Update" />
</div>
<p><asp:Label ID="lblMessage" runat="server" EnableViewState="false" /></p>
</form>
Clicking on Update button execute btnSave_Click method of the code file.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BAL;
public partial class Update : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack && !string.IsNullOrWhiteSpace(Request.QueryString["autoid"]))
{
ViewState["AutoId"] = Request.QueryString["autoid"];
var autoId = int.Parse(Request.QueryString["autoid"]);
FillInPersonDetails(autoId);
}
else if (!IsPostBack)
{
Response.Redirect("~/default.aspx", true);
}
}
private void FillInPersonDetails(int autoId)
{
try
{
DataTable table = new PersonBAL().Load(autoId);
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
txtAge.Text = row["Age"].ToString();
txtFirstName.Text = row["FirstName"].ToString();
txtLastName.Text = row["LastName"].ToString();
}
}
catch (Exception ee)
{
lblMessage.Text = ee.Message;
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (ViewState["AutoId"] == null)
{
return;
}
var result = 0;
try
{
// Validate your data, if any
int age = 0;
int.TryParse(txtAge.Text, out age);
var autoId = int.Parse(ViewState["AutoId"].ToString());
result = new PersonBAL().Update(autoId, txtFirstName.Text.Trim(), txtLastName.Text.Trim(), age);
if (result > 0)
{
lblMessage.Text = "Record updated successfully !";
}
else
{
lblMessage.Text = "No record afftected.";
}
}
catch (Exception ee)
{
lblMessage.Text = "Error occured. " + ee.Message;
}
}
}
Page_Load method
If the request is not post back and coming with
autoid querystring then sets the value of
autoid to the
ViewState["AutoId"] and call
FillInPersonDetails method else if it is not post back then redirects the user to default.aspx page (It happens when end user directly comes to the update.aspx page).
FillInPersonDetails method
This method calls Load method of the BAL by passing autoId parameter and sets the data to texboxes for editing.
btnSave_Click method
This method checks for the ViewState["AutoId"] and if its null then simply returns from the method else calls the Update method of the BAL by passing respective parameters and writes proper message based on what is returned from this method.
Final structure of this solution looks like below
Other Point of Interest - Error Handling
It is clear from the code snippet of BAL and DAL that we have not used try catch block there as it is actually not required. We can simply wrap the BAL method calls into try catch block in the presentation layer as it is done in above code snippets and display or log the error into Catch block. Wrapping code in try catch block in BAL and DAL is overhead to the application and unnecessary creates performance issue and confusion.
Please feel free to download and use the demo application created for this article.
Conclusion
Apart from many other advantages of
3 tier architecture that you might have already come across, it is very
easy and useful for developer as well as application owner as it helps to
understand the code easily and works best in big team size where each team
member is responsible for doing a separate module and it also helps in easily
maintaining, extending the application.
Thanks for reading. Hope you liked
this article, do comment and vote for this article and share to your friends
and colleague.