Insert update delete in asp.net gridview datasource as sql server

In this lesson i explained about how to create grid view with following functionalities like Insert,update,delete..
I didn't do any validation for gridview fields in this code.But this code work perfectly according to the functionality.
I have did this with 3 layer architecture.

Conclusion

What are the change you are doing with gridview everything updated in DB.

I think my lesson helped you.Thanks for read it.

Create table in sql server with following command

create table Customer(
s_no int IDENTITY(1,1) primary key,
First_name varchar(20),
Last_name varchar(20),
Address varchar(20))
  
 
/* identity used to generate number automatically Syntax: IDENTITY( seed, increment)
After that include one row data with sql server, if not when run the code without data in table
it wont show anything in browser.
Add data with following code */
 
insert into Customer values('Gopala','Krishnan','India')
  

Look of grid view

Solution Explorer

Add the two class libraries with name of DAL and BLL like shown in figure and the class names also..

Dont forget to add reference of the BLL and DAL in WebApplication and DAL in BLL. shown in above fig.

Note: Important Dont forget to add DataKeyName in gridview properties. Shown in above fig.

Code of WebForm1.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>
<!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="GridView1" runat="server" BackColor="White" 
            BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" 
            DataKeyNames="s_no" onrowcancelingedit="GridView1_RowCancelingEdit" 
            onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" 
            onrowupdating="GridView1_RowUpdating"
            AutoGenerateColumns="False" ShowFooter="True" Width="553px">
            <Columns>
                <asp:BoundField DataField="s_no" HeaderText="s_no" />
                <asp:TemplateField HeaderText="First_name">
                    <EditItemTemplate>
                        <asp:TextBox ID="First_name" runat="server" Text='<%# Bind("First_name") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="Fname" runat="server"></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("First_name") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Last_name">
                    <EditItemTemplate>
                        <asp:TextBox ID="Last_name" runat="server" Text='<%# Bind("Last_name") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="Lname" runat="server"></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("Last_name") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Address">
                    <EditItemTemplate>
                        <asp:TextBox ID="Address" runat="server" Text='<%# Bind("Address") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="Address" runat="server"></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("Address") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField ShowHeader="False">
                    <EditItemTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" 
                            CommandName="Update" Text="Update"></asp:LinkButton>
                        &nbsp;<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" 
                            CommandName="Cancel" Text="Cancel"></asp:LinkButton>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:Button ID="ins" runat="server" Text="Insert" onclick="insertValue"/>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" 
                            CommandName="Edit" Text="Edit"></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:CommandField ShowDeleteButton="True" />
            </Columns>
            <FooterStyle BackColor="White" ForeColor="#000066" />
            <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
            <RowStyle ForeColor="#000066" />
            <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#007DBB" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#00547E" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Code of WebForm1.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BLL;
using System.Data;
using System.Data.SqlClient;
using DAL;
 
namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        dalView dalvi = new dalView();//object of dalView
        bllView BLLVIEW = new bllView();//object of blllView
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataSet rd = BLLVIEW.bllv();
                GridView1.DataSource = rd;
                GridView1.DataBind();
            }
        }
        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)//Row deleting function
        {
            int user_id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
            dalvi.dald(user_id);
            DataSet rd = BLLVIEW.bllv();
            GridView1.DataSource = rd;
            GridView1.DataBind();
        }
        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)//row editing function
        {
            GridView1.EditIndex = e.NewEditIndex;
            DataSet rd = BLLVIEW.bllv();
            GridView1.DataSource = rd;
            GridView1.DataBind();
        }
        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)//row cancel function
        {
            GridView1.EditIndex = -1;
            DataSet rd = BLLVIEW.bllv();
            GridView1.DataSource = rd;
            GridView1.DataBind();
        }
        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)//row updating function
        {
           int userid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
            TextBox First_name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("First_name");// First_name-you can find these name in WebForm1.aspx
            TextBox Last_name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("Last_name");
            TextBox Address = (TextBox)GridView1.Rows[e.RowIndex].FindControl("Address");
            string a = First_name.Text.ToString();
            string b = Last_name.Text.ToString();
            string c = Address.Text.ToString();
            GridView1.EditIndex = -1;
            dalvi.binu(a, b, c, userid);
            DataSet rd = BLLVIEW.bllv();
            GridView1.DataSource = rd;
            GridView1.DataBind();
        }
        protected void insertValue(object sender, EventArgs e)//row inserting function
        {
            TextBox fName = (TextBox)GridView1.FooterRow.FindControl("Fname");
            TextBox lName = (TextBox)GridView1.FooterRow.FindControl("Lname");
            TextBox Add = (TextBox)GridView1.FooterRow.FindControl("Address");
            string fn = fName.Text.ToString();
            string ln = lName.Text.ToString();
            string ad = Add.Text.ToString();
            GridView1.EditIndex = -1;
            dalvi.bini(fn, ln, ad);
            DataSet rd = BLLVIEW.bllv();
            GridView1.DataSource = rd;
            GridView1.DataBind();
        }
    }
}

Code of bllView.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAL;
 
namespace BLL
{
    public class bllView
    {
        dalView DALVIEW = new dalView();
        //object for DAL just to connect with dal here.if u dont want bll you can directly connect webfom with dal.
        public DataSet bllv()
        {
            return DALVIEW.bind();
        }
    }
}

Code of dalView.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
namespace DAL
{
   public class dalView
   {
       SqlConnection conn = new SqlConnection(@"data source=LAKSHANYA\SQLEXPRESS;Initial Catalog=master;Integrated security=true");
       public void dald(int a)//This function used to delete the content
       {
           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand("DELETE FROM Customer WHERE s_no=" + a, conn);
               cmd.ExecuteNonQuery();
            }
           catch (Exception e)
           {
               throw e;
           }
           finally
           {
               conn.Close();
           }
       }
       public DataSet bind()//this function used to get the data from the DB to bind into the gidview
       {
           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand("SELECT * FROM Customer", conn);
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               DataSet ds = new DataSet();
               da.Fill(ds);
               return ds;
           }
           catch (Exception e)
           {
               throw e;
           }
           finally
           {
               conn.Close();
           }
       }
       public void binu(string a,string b,string c, int d)//This function used to update the Db
        {
           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand("update Customer set First_name='" + a + "',Last_name='" + b + "',Address='"+c+"' where s_no=" + d, conn);
               cmd.ExecuteNonQuery();
           }
           catch (Exception e)
           {
               throw e;
           }
           finally
           {
               conn.Close();
           }
       }
       public void bini(string a, string b, string c)//This function used to insert the new value into Db
        {
           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand("insert into Customer(First_name,Last_name,Address) values ('" + a + "','" + b + "','" + c + "')", conn);
               cmd.ExecuteNonQuery();
           }
           catch (Exception e)
           {
               throw e;
           }
           finally
           {
               conn.Close();
           }
       }
   }
}