In this article, we are going to create 3 tier application in asp.net. We will take some form data and store data into database using stored procedure. Below is the step by step tutorial.
Step 1: Open visual studio, go to File->New->Project->Other Project Types->Visual Studio Solutions->Blank Solution as shown in below figure. Give the solution name as 3Tier and click on OK. I'm using visual studio 2010.
Now a blank solution will be created as show below.
Step 2.1: Now, we have to add two Class library projects, one for Data Access Layer and other for Business Logic layer. And one ASP.Net web application project. Right click on the solution go to Add->New Project select a class library project save it with the name DAL. Now a class library project will be add as show below in fig 1.
| fig. 1 | fig. 2 |
|---|
Step 2.2: Now, rename class1.cs to clsConnection.cs as shown above fig. 2 for implementing logic for database connection. Make sure to add System.Configuration dll in DAL. Now open clsConnection.cs class file write below code for database connection. Now add a method named as GetConnection which will return SqlConnection object. Here, you can fetch connection string from web.config file of ASP.NET web application or you can pass it directly.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;// add namespace
using System.Configuration;// add namespace
namespace DAL
{
public class clsConnection
{
public SqlConnection GetConnection()
{
SqlConnection con = null;
try
{
//string conString = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
string conString = @"data source=XXXX;database=XXXX;user id=XXXX;password=XXXX;";
con = new SqlConnection(conString);
}
catch (Exception ex)
{
}
return con;
}
}
}
Step 2.3: Now, add a one more class file named as clsStoredProc.cs in DAL which will be used for stored procedures are being used in application. We will be using only one stored procedure usp_Employee.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DAL
{
public class clsStoredProc
{
// set procedure name
public static String usp_Employee = "usp_Employee";
public static String usp_Procedure2 = "usp_Procedure2";
public static String usp_Procedure3 = "usp_Procedure3";
public static String usp_Procedure4 = "usp_Procedure4";
public static String usp_Proceduren = "usp_Procedure..n";
}
}
Step 3.1: Now, add one more class library project for Business Logic layer. Right click on the solution go to Add->New Project select a class library project save it with the name BLL. Now a class library project will be added. Rename class1.cs to clsEmployee.cs as shown in below fig.
Step 3.2: Add reference of DAL into BLL. Create a object of class clsConnection and create a bool method named as SaveEmployee to save data. Below is the complete code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;// add namespace
using System.Data.SqlClient;// add namespace
using System.Data;// add namespace
namespace BLL
{
public class clsEmployee
{
public string id { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public DateTime DOJ { get; set; }
clsConnection clsConn = new clsConnection();
public bool SaveEmployee()
{
SqlConnection con = null;
try
{
con = clsConn.GetConnection();
// clsStoredProc.usp_Employee to get procedure name
SqlCommand cmd = new SqlCommand(clsStoredProc.usp_Employee, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", Name);
cmd.Parameters.AddWithValue("@Gender", Gender);
cmd.Parameters.AddWithValue("@DOJ", DOJ);
cmd.Parameters.AddWithValue("@Action", "SaveData");
con.Open();
cmd.ExecuteNonQuery();
con.Close();
return true;
}
catch (Exception ex)
{
return false;
}
finally
{
con.Close();
}
}
}
}
Step 4.1: Now, Right click on the solution go to Add->New Project select a ASP.NET web application project project save it with the name ThreeTierApp as shown below. Now add a webform with name Employee.aspx. Add a reference of BLL into ThreeTierApp.
Step 4.2: Now, design webform which will take employee data. Below is the designer file code. Add a image calender.png in root directory for ImageButton control.
Page Language="C#" AutoEventWireup="true" CodeBehind="Employee.aspx.cs" Inherits="ThreeTierApp.Employee" %>
xmlns="http://www.w3.org/1999/xhtml">
runat="server">
Step 4.2: Below is the code-behind file code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BLL;// add namespace
namespace ThreeTierApp
{
public partial class Employee : System.Web.UI.Page
{
clsEmployee empObj = new clsEmployee();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void imgCal_Click(object sender, ImageClickEventArgs e)
{
Calendar1.Visible = !Calendar1.Visible;
}
protected void Calendar1_SelectionChanged(object sender, EventArgs e)
{
txtDOJ.Text = Calendar1.SelectedDate.ToShortDateString();
Calendar1.Visible = false;
}
protected void btnSumbit_Click(object sender, EventArgs e)
{
lblMessage.Text = string.Empty;
lblMessage.ForeColor = System.Drawing.Color.Red;
if (txtName.Text.Trim() == string.Empty)
{
empObj.Name = txtName.Text.Trim();
lblMessage.Text = "Enter employee name";
return;
}
if (ddlGender.SelectedIndex==0)
{
lblMessage.Text = "Select employee gender";
return;
}
if (txtDOJ.Text == string.Empty)
{
lblMessage.Text = "Select DOJ";
return;
}
empObj.Name = txtName.Text.Trim();
empObj.Gender = ddlGender.SelectedItem.Text;
empObj.DOJ = Convert.ToDateTime(txtDOJ.Text);
if (empObj.SaveEmployee())
{
lblMessage.Text = "Record saved successfully";
lblMessage.ForeColor = System.Drawing.Color.Green;
}
else
{
lblMessage.Text = "Saved failed";
}
}
}
}
Step 4.3: Below is the SQL script to create table and a stored procedure.
create table tblEmployee
(
Id int primary key identity,
Name varchar(50),
Gender varchar(10),
DOJ DateTime
)
go
create procedure usp_Employee
@Name varchar(50)=null,
@Gender varchar(10)=null,
@DOJ DateTime=null,
@Action varchar(20)=null
as
begin
if(@Action='SaveData')
begin
insert into tblEmployee values(@Name,@Gender,@DOJ)
end
end
