In this example i used the 2 Tier Architecture for Inserting, Retrieving, Update and Delete Data from database in the Gridview. 2 tier architecture is secured, easily manageable,and highly understandable.
2-Tier architecture consists of
1) UI or Presentation Layer
2) Data Access Layer
1) UI or Presentation Layer
2) Data Access Layer
The presentation tier contains the UI (User
Interface) elements of the site, and includes all the logic that manages
the interaction between the visitor and the client’s business.
The data tier is responsible for storing the application’s data and sending it to the presentation tier when requested.
To implement this concept you need to follow the below steps
Step1 :
First you need to design a table in Sql Database to to save the records in
database and can also use stored procedures for inserting, retrieving, update and delete data from Database.
Step2:
Create a new Asp.net website in Visual Studio.Go to Solution Explorer and then right click on your website
add new project for DataAccess
layer name it as DAL and select a class as dalclass.cs Add New Item to the solution and select a Webform and name it as Presentation.aspx.
Step3:
Now open the Dalcls.cs page and write the following source code.
DalClass.Cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
public class dalclass
{
string connection = ConfigurationManager.AppSettings["config"];
public DataSet
bind()
{
SqlConnection cn = new
SqlConnection(connection);
SqlDataAdapter da = new
SqlDataAdapter("select
* from employee", cn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public int insert(string Ename, string
Esalary)
{
SqlConnection cn = new
SqlConnection(connection);
cn.Open();
SqlCommand cmd = new
SqlCommand("insert
into employee(Ename,Esalary) values('" + Ename + "','" + Esalary + "')", cn);
cmd.CommandType = CommandType.Text;
int res = cmd.ExecuteNonQuery();
return res;
cn.Close();
}
public int update(int Eno, string
Ename, string Esalary)
{
SqlConnection cn = new
SqlConnection(connection);
cn.Open();
SqlCommand cmd = new
SqlCommand("update
employee set Ename='" +
Ename + "',Esalary='" + Esalary + "' where Eno=" + Eno + "", cn);
cmd.CommandType = CommandType.Text;
int res = cmd.ExecuteNonQuery();
return res;
cn.Close();
}
public int delete(int Eno)
{
SqlConnection
cn = new SqlConnection(connection);
cn.Open();
SqlCommand cmd = new
SqlCommand("delete
from employee where Eno=" + Eno + "",
cn);
cmd.CommandType = CommandType.Text;
int res = cmd.ExecuteNonQuery();
return res;
cn.Close();
}
}
}
Step4:
Now open the Presentation.aspx page and write the following source code.
Presentation.aspx :
<%@ Page
Language="C#"
AutoEventWireup="true"
CodeBehind="sample.aspx.cs"
Inherits="sample100.sample"
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="grdEmployee"
runat="server"
AutoGenerateEditButton="true"
DataKeyNames="Eno"
AutoGenerateDeleteButton="true"
onrowediting="grdEmployee_RowEditing"
AutoGenerateColumns="false"
onrowupdating="grdEmployee_RowUpdating"
ShowFooter="true"
onrowcancelingedit="grdEmployee_RowCancelingEdit"
onrowdeleting="grdEmployee_RowDeleting">
<Columns>
<asp:TemplateField HeaderText="Eno">
<ItemTemplate>
<%#Eval("Eno")%>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="tt1"
Text='<%#Eval("Eno") %>'
runat="server"></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtno" runat="server">
</asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Ename">
<ItemTemplate>
<%#Eval("Ename")%>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="tt2"
Text='<%#Eval("Ename")%>'
runat="server"></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtname"
runat="server">
</asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Esalary">
<ItemTemplate>
<%#Eval("Esalary")%>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="tt3"
Text='<%#Eval("Esalary") %>'
runat="server"></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtsal" runat="server">
</asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<FooterTemplate>
<asp:Button ID="btnAdd" runat="server"
Text="Add"
onclick="btnAdd_Click"
/>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Step5:
Now open the Presentation.aspx.cs page and write the following source code.
Presentation.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DAL;
using System.Data;
namespace sample100
{
public partial class sample :
System.Web.UI.Page
{
dalclass dc = new dalclass();
protected void
Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind();
}
}
public void bind()
{
DataSet ds = dc.bind();
grdEmployee.DataSource = ds;
grdEmployee.DataBind();
}
protected void
grdEmployee_RowEditing(object sender, GridViewEditEventArgs e)
{
grdEmployee.EditIndex = e.NewEditIndex;
bind();
}
protected void
grdEmployee_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int Eno =Convert.ToInt32(grdEmployee.DataKeys[e.RowIndex].Value.ToString());
TextBox Name= (TextBox)(grdEmployee.Rows[e.RowIndex].FindControl("tt2"));
TextBox Salary = (TextBox)(grdEmployee.Rows[e.RowIndex].FindControl("tt3"));
string Ename = Name.Text;
string Esalary = Salary.Text;
int result = dc.update(Eno, Ename, Esalary);
grdEmployee.EditIndex=-1;
bind();
}
protected void
grdEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grdEmployee.EditIndex = -1;
bind();
}
protected void
grdEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int Eno = Convert.ToInt32(grdEmployee.DataKeys[e.RowIndex].Value.ToString());
int result = dc.delete(Eno);
bind();
}
protected void
btnAdd_Click(object sender, EventArgs e)
{
TextBox
Name=(TextBox)(grdEmployee.FooterRow.FindControl("txtname"));
TextBox Salary = (TextBox)(grdEmployee.FooterRow.FindControl("txtsal"));
string Ename = Name.Text;
string Esalary = Salary.Text;
dc.insert(Ename, Esalary);
bind();
}
}
}
