Insert Edit update and delete from gridview in asp.net with c#

Insert Edit update and delete from grid-view

Notes: In this post i'm going to use the mane of DB and Table are

  1. Database name: empDB
  2. Table name: Employees

And going to use the connection string

static string _strCon = "Data Source=.;Integrated Security=true;Initial Catalog=empDB";

gridview in .aspx page code

        <div>
            <asp:Label ID="lblMsg" runat="server"></asp:Label>
        </div>
        <div>
            <asp:GridView ID="grdEmpDetails" DataKeyNames="UserId,UserName" runat="server"
                AutoGenerateColumns="false" CssClass="Gridview" HeaderStyle-BackColor="#61A6F8"
                ShowFooter="true" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White"
                OnRowCancelingEdit="grdEmpDetails_RowCancelingEdit"
                OnRowDeleting="grdEmpDetails_RowDeleting" OnRowEditing="grdEmpDetails_RowEditing"
                OnRowUpdating="grdEmpDetails_RowUpdating"
                OnRowCommand="grdEmpDetails_RowCommand">

                <Columns>
                    <asp:TemplateField HeaderText="Emp_Name">
                        <EditItemTemplate>
                            <asp:Label ID="lbl_editempname" runat="server" Text='<%#Eval("Emp_Name") %>' />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="lbl_itemempname" runat="server" Text='<%#Eval("Emp_Name") %>' />
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txt_ftrempnmae" runat="server" />
                            <asp:RequiredFieldValidator ID="rfv_empname" runat="server" ControlToValidate="txt_ftrempnmae" ValidationGroup="v1" />
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Emp_Dept">
                        <EditItemTemplate>
                            <asp:Label ID="lbl_editempDept" runat="server" Text='<%#Eval("Emp_Dept") %>' />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="lbl_itemempDept" runat="server" Text='<%#Eval("Emp_Dept") %>' />
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txt_ftrempDept" runat="server" />
                            <asp:RequiredFieldValidator ID="rfv_empDept" runat="server" ControlToValidate="txt_ftrempDept" ValidationGroup="v1" />
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField>
                        <EditItemTemplate>
                            <asp:ImageButton ID="img_update" CommandName="Update" runat="server" ImageUrl="~/Images/updateButton.jpg" Height="10px" Width="10px" />
                            <asp:ImageButton ID="img_cancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/CancelButton.jpg" Height="10px" Width="10px" />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:ImageButton ID="img_Edit" CommandName="Edit" runat="server" ImageUrl="~/Images/EditButton.jpg" Height="10px" Width="10px" />
                            <asp:ImageButton ID="img_Delete" CommandName="Delete" Text="Edit" runat="server" ImageUrl="~/Images/deleteButton.jpg" Height="10px" Width="10px" />
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:ImageButton ID="img_Add" runat="server" ImageUrl="~/Images/AddButton.jpg" CommandName="AddEmp" Width="10px" Height="10px" ValidationGroup="v1" />
                        </FooterTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </div>

gridview in .aspx.cs page code

public partial class _Default : System.Web.UI.Page
{
    /// <summary>
    /// page load
    /// </summary>
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GetEmployee();
            }
        }

    /// <summary>
    /// Get the connection between to DB
    /// </summary>
        static string _strCon = "Data Source=.;Integrated Security=true;Initial Catalog=empDB";
        SqlConnection con = new SqlConnection(_strCon);
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();

    /// <summary>
    /// Edit to the employee
    /// </summary>
        protected void grdEmpDetails_RowEditing(object sender, GridViewEditEventArgs e)
        {
            grdEmpDetails.EditIndex = e.NewEditIndex;
            GetEmployee();
        }

    /// <summary>
    /// update to employee
    /// </summary>
        protected void grdEmpDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            int empId = Convert.ToInt32(grdEmpDetails.DataKeys[e.RowIndex].Value.ToString());
            string Empname = grdEmpDetails.DataKeys[e.RowIndex].Values["Emp_Name"].ToString();

            TextBox txtDep = (TextBox)grdEmpDetails.Rows[e.RowIndex].FindControl("txt_ftrempDept");

            con.Open();
            cmd = new SqlCommand("update Employees set Emp_Dept='" + txtDep.Text + "' where emp_Id=" + empId, con);
            cmd.ExecuteNonQuery();
            con.Close();
            lblMsg.Text ="Updated successfully.";
            grdEmpDetails.EditIndex = -1;
            GetEmployee();
        }

    /// <summary>
    ///  cancel to edit mode
    /// </summary>
        protected void grdEmpDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            grdEmpDetails.EditIndex = -1;
            GetEmployee();
        }

    /// <summary>
    ///
    /// </summary>
        protected void grdEmpDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int empId = Convert.ToInt32(grdEmpDetails.DataKeys[e.RowIndex].Values["emp_Id"].ToString());
            string Empname = grdEmpDetails.DataKeys[e.RowIndex].Values["Emp_Name"].ToString();

            con.Open();
            cmd = new SqlCommand("delete from Employees where emp_Id=" + empId, con);
            int result = cmd.ExecuteNonQuery();
            con.Close();
            if (result >= 1)
            {
                GetEmployee();
                lblMsg.Text ="deleted successfully.";
            }
        }

        protected void grdEmpDetails_RowCommand(object sender, GridViewCommandEventArgs e)
        {
           if (e.CommandName.Equals("AddEmp"))
           {
               TextBox txtEmpname = (TextBox)grdEmpDetails.FooterRow.FindControl("txt_ftrempnmae");
               TextBox txtDep = (TextBox)grdEmpDetails.FooterRow.FindControl("txt_ftrempDept");
               con.Open();
               cmd = new SqlCommand("insert into Employees values('" + txtEmpname.Text + "','" + txtDep.Text + "')", con);
               int result= cmd.ExecuteNonQuery();
               con.Close();
                   if(result==1)
                   {
                       GetEmployee();
                       lblMsg.Text = "Inserted successfully.";
                   }
                   else
                   {
                       lblMsg.Text ="Not inserted.";
                   }              

           }          

        }

        /// <summary>
        /// Get the employees
        /// </summary>
        public void GetEmployee()
        {
            con.Open();
            cmd = new SqlCommand("Select Emp_Id,Emp_Name,Emp_Dept from Employees", con);
            da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            con.Close();
            if (ds.Tables[0].Rows.Count > 0)
            {
                grdEmpDetails.DataSource = ds;
                grdEmpDetails.DataBind();
            }
            else
            {
                ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
                grdEmpDetails.DataSource = ds;
                grdEmpDetails.DataBind();

                int count = grdEmpDetails.Rows[0].Cells.Count;
                grdEmpDetails.Rows[0].Cells.Clear();
                grdEmpDetails.Rows[0].Cells.Add(new TableCell());
                grdEmpDetails.Rows[0].Cells[0].ColumnSpan = count;
                grdEmpDetails.Rows[0].Cells[0].Text = "Records not Found";
            }
        }
    }