Saturday 20 April 2013

Insert, Edit, Update, Delete in GridView using Two tier architecture


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

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();
        }  
      
    }
}



 

1 comment: