Insert,Update,Delete In GridView using asp.net

02:03

Here I am Explaining Insert, Update, Delete In GridView using Asp.Net

 DataBase Table Creation:


 CREATE TABLE [dbo].[Employee1](
[Emp_Id] [int] NOT NULL,
[Emp_Name] [varchar](50) NOT NULL,
[Emp_Salary] [int] NOT NULL


 1.Create New Website in VS and Add Web Form To Website.
2.In source write the below code.
 <body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="grid" runat="server" AutoGenerateColumns="false" ShowFooter="true"
            OnRowEditing="grid_RowEditing" onrowcancelingedit="grid_RowCancelingEdit" 
            onrowupdating="grid_RowUpdating" onrowcommand="grid_RowCommand" 
            onrowdeleting="grid_RowDeleting">
            <Columns>
                <asp:TemplateField HeaderText="">
                    <ItemTemplate>
                        <asp:LinkButton ID="btnedit" runat="server" CommandName="Edit" Text="Edit"></asp:LinkButton>
                        <asp:LinkButton ID="btndelete" runat="server" CommandName="Delete" Text="Delete"></asp:LinkButton>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:LinkButton ID="btnupdate" runat="server" CommandName="Update" Text="Update"></asp:LinkButton>
                        <asp:LinkButton ID="btncancel" runat="server" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:LinkButton ID="btninsert" runat="server" CommandName="Insert" Text="Insert"></asp:LinkButton>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Employee Id">
                    <ItemTemplate>
                        <asp:Label ID="lbleid" runat="server" Text="<%#Bind('Emp_Id') %>"></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:Label ID="lbleid1" runat="server" Text="<%#Bind('Emp_Id') %>"></asp:Label>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txxfeid" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Emplyee Name">
                    <ItemTemplate>
                        <asp:Label ID="lblename" runat="server" Text="<%#Bind('Emp_Name') %>"></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtename" runat="server" Text="<%#Bind('Emp_Name') %>"></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtfename" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Emplyee Salary">
                    <ItemTemplate>
                        <asp:Label ID="lblesalary" runat="server" Text="<%#Bind('Emp_Salary') %>"></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtesalary" runat="server" Text="<%#Bind('Emp_Salary') %>"></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtfesalary" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <br />
        <asp:Label ID="lblmsg" runat="server" Text=""></asp:Label>
    </div>
    </form>
</body>

 3.In .cs file add the below name spaces and code.

using System.Data;
using System.Data.SqlClient;

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }
    public void BindData()
    {
        SqlConnection con = new SqlConnection("server=ADMINISTRATOR00;database=Acadamics;uid=ACMCPRJ;pwd=projects");
        SqlDataAdapter da = new SqlDataAdapter("select * from Employee1",con);
        SqlCommandBuilder cmb = new SqlCommandBuilder(da);
        DataTable table = new DataTable();
        da.Fill(table);
        grid.DataSource = table;
        grid.DataBind();
    }
    protected void grid_RowEditing(object sender, GridViewEditEventArgs e)
    {
        grid.EditIndex = e.NewEditIndex;
        BindData();
    }
    protected void grid_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        grid.EditIndex = -1;
        BindData();
    }
    protected void grid_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        Label lblempid = grid.Rows[e.RowIndex].FindControl("lbleid1") as Label;
        TextBox txtename = grid.Rows[e.RowIndex].FindControl("txtename") as TextBox;
        TextBox txtesal = grid.Rows[e.RowIndex].FindControl("txtesalary") as TextBox;
        int empid = Convert.ToInt32(lblempid.Text);
        string empname = txtename.Text;
        int empsal = Convert.ToInt32(txtesal.Text);
        SqlConnection con = new SqlConnection("server=ADMINISTRATOR00;database=Acadamics;uid=ACMCPRJ;pwd=projects");
        SqlCommand cmd = new SqlCommand("update Employee1 set Emp_Name='" + empname + "',Emp_Salary=" + empsal + " where Emp_Id=" + empid + "", con);
        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();
        string msg = "";
        if (i != 0)
        {
            msg = "Updation Successfully..";
        }
        else
        {
            msg = "Updation Failed..";
        }
        lblmsg.Text = msg.ToString();
        grid.EditIndex = -1;
        BindData();
    }
    protected void grid_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Label lblempid = grid.Rows[e.RowIndex].FindControl("lbleid") as Label;
        int empid = Convert.ToInt32(lblempid.Text);
        SqlConnection con = new SqlConnection("server=ADMINISTRATOR00;database=Acadamics;uid=ACMCPRJ;pwd=projects");
        SqlCommand cmd = new SqlCommand("delete Employee1 where Emp_Id=" + empid + "", con);
        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();
        string msg = "";
        if (i != 0)
        {
            msg = "Deletion Successfully..";
        }
        else
        {
            msg = "Deletion Failed..";
        }
        lblmsg.Text = msg.ToString();
        BindData();
    }
    protected void grid_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Insert")
        {
            TextBox txtempid = grid.FooterRow.FindControl("txxfeid") as TextBox;
            TextBox txtempname = grid.FooterRow.FindControl("txtfename") as TextBox;
            TextBox txtempsal = grid.FooterRow.FindControl("txtfesalary") as TextBox;
            int empid = Convert.ToInt32(txtempid.Text);
            string empname = txtempname.Text;
            int empsal = Convert.ToInt32(txtempsal.Text);
            SqlConnection con = new SqlConnection("server=ADMINISTRATOR00;database=Acadamics;uid=ACMCPRJ;pwd=projects");
            SqlCommand cmd = new SqlCommand("insert Employee1 values(@empid,@empname,@empsal)", con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@empid", empid);
            cmd.Parameters.AddWithValue("@empname", empname);
            cmd.Parameters.AddWithValue("@empsal", empsal);
            con.Open();
            int i = cmd.ExecuteNonQuery();
            con.Close();
            string msg = "";
            if (i != 0)
            {
                msg = "Insertion Successfully..";
            }
            else
            {
                msg = "Insertion Failed..";
            }
            lblmsg.Text = msg.ToString();
            BindData();
        }
    }
4. Run Website Or press F5.


Employee Id Emplyee Name Emplyee Salary
Edit Delete 1 Venu 150000
Edit Delete 2 Gopal 200000
Insert
Click on edit linkbutton=>Shows Like Below And Enter new empname and emp salary

Employee Id Emplyee Name Emplyee Salary
Update Cancel 1
Edit Delete 2 Gopal 200000
Insert
Click on Update Button=>Then changes are reflected

Employee Id Emplyee Name Emplyee Salary
Edit Delete 1 Krishna 200000
Edit Delete 2 Gopal 200000
Insert

Updation Successfully..

Enter New Empid,Empname,Empsalary then click on insert linkbutton.

Employee Id Emplyee Name Emplyee Salary
Edit Delete 1 Krishna 200000
Edit Delete 2 Gopal 200000
Insert
Then It will Show Like Below

Employee Id Emplyee Name Emplyee Salary
Edit Delete 1 Krishna 200000
Edit Delete 2 Gopal 200000
Edit Delete 3 Venu 250000
Insert

Insertion Successfully..
Then Click On Delete Link Button then it will show like below

Employee Id Emplyee Name Emplyee Salary
Edit Delete 1 Krishna 200000
Edit Delete 2 Gopal 200000
Insert

Deletion Successfully..