CRUD operation in Gridview using 3 tier in ASP.NET
In this article, we will learn how to create 3-tier architecture application (that is very popular) in ASP.NET web form.
Introduction
In this article, we will learn how to create 3-tier architecture application that is very popular in ASP.NET web form.
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 a 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 a result to the Business Access Layer.
So here is the typical solution structure of a 3 tier architecture application solution.
Step1: First of all we have to create a database.
In a database, we will create a table.
CREATE TABLE [dbo].[tbl_data](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[city] [varchar](50) NULL,
CONSTRAINT [PK_tbl_data] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOStep2: We will add a Grid View in our Productinfo.aspx page .
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Productinfo.aspx.cs" Inherits="_3_tier.Productinfo" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="true"
BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
CellPadding="3" DataKeyNames="id" onrowcancelingedit="GridView1_RowCancelingEdit"
onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" OnRowCommand="gvDetails_RowCommand"
onrowupdating="GridView1_RowUpdating" CellSpacing="2">
<Columns>
<asp:TemplateField HeaderText="Operations">
<EditItemTemplate>
<asp:Button ID="Button3" runat="server" CommandName="Update" Text="Update" />
<asp:Button ID="Button4" runat="server" CommandName="Cancel" Text="Cancel" />
</EditItemTemplate>
<ItemTemplate>
<asp:Button ID="Button1" runat="server" CommandName="Edit" Text="Edit" />
<asp:Button ID="Button2" runat="server" CommandName="Delete" Text="Delete" />
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="addnew" runat="server" CommandName="Add New" Text="Add New" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("name") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("name") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="addname" runat="server" ></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("city") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("city") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="addcity" runat="server" ></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FFF1D4" />
<SortedAscendingHeaderStyle BackColor="#B95C30" />
<SortedDescendingCellStyle BackColor="#F1E5CE" />
<SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
</div>
</form>
</body>
</html>Step3: Write the following events in Productinfo.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using BAL;
namespace _3tier
{
public partial class Productinfo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
refreshdata();
}
}
public void refreshdata()
{
DataTable dt = new DataTable();
bal balobj = new bal();
dt= balobj.BindBalGridView();
GridView1.DataSource = dt;
GridView1.DataBind();
Response.Write("bind data");
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int id = Convert.ToInt16(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());
bal balobj = new bal();
int i = balobj.DeleteBal(id);
if (i > 1)
{
Response.Write("iteme delete successfully");
}
refreshdata();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
refreshdata();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
refreshdata();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox name = GridView1.Rows[e.RowIndex].FindControl("TextBox1") as TextBox;
TextBox city = GridView1.Rows[e.RowIndex].FindControl("TextBox2") as TextBox;
int id = Convert.ToInt16(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());
bal balobj = new bal();
balobj.UpdateBal(name.Text, city.Text, id);
int i=
GridView1.EditIndex = -1;
refreshdata();
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("Add New"))
{
TextBox name = (TextBox)GridView1.FooterRow.FindControl("addname") ;
TextBox city = (TextBox)GridView1.FooterRow.FindControl("addcity");
//string Name = name.ToString();
//string City = city.ToString();
bal balobj = new bal();
int i = balobj.InsertBal(name.Text, city.Text);
refreshdata();
}
}
}
}Step4: Create a Business Access Layer
In bal.cs class write the following code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using DAL;
namespace BAL
{
public class bal
{
dal dalobj = new dal();
public DataTable BindBalGridView(){
return dalobj.BindDalGridView();
}
public int DeleteBal(int id)
{
return dalobj.DeleteDal(id);
}
public int UpdateBal(string name,string city,int id)
{
return dalobj.UpdateDal(name,city,id);
}
public int InsertBal(string Name,string city)
{
return dalobj.InsertDal(Name, city);
}
}
}
Step5: Create a Data Access layer
In dal.cs class write the following code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
public class dal
{
public DataTable BindDalGridView()
{
SqlConnection con = new SqlConnection(@"Data Source=ROHIT-HP;Initial Catalog=aa; User ID=sa;Password=sa1234; ");
SqlCommand cmd = new SqlCommand("select * from tbl_data", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
public int DeleteDal( int id)
{
SqlConnection con = new SqlConnection(@"Data Source=ROHIT-HP;Initial Catalog=aa; User ID=sa;Password=sa1234;");
con.Open();
SqlCommand cmd = new SqlCommand("delete from tbl_data where id =@id", con);
cmd.Parameters.AddWithValue("id", id);
int i = cmd.ExecuteNonQuery();
con.Close();
return i;
}
public int UpdateDal(string name,string city,int id)
{
SqlConnection con = new SqlConnection(@"Data Source=ROHIT-HP;Initial Catalog=aa; User ID=sa;Password=sa1234;");
con.Open();
SqlCommand cmd = new SqlCommand("Update tbl_data set name='" + name + "',city='" + city + "' where id='" + id + "' ", con);
int i = cmd.ExecuteNonQuery();
con.Close();
return i;
}
public int InsertDal(string name,string city)
{
SqlConnection con = new SqlConnection(@"Data Source=ROHIT-HP;Initial Catalog=aa; User ID=sa;Password=sa1234;");
con.Open();
SqlCommand cmd = new SqlCommand("insert into tbl_data(name,city) values('" + name + "','" + city + "')", con);
int i= cmd.ExecuteNonQuery();
con.Close();
return i;
}
}
}