Insert/Update/Delete in GridView using asp.net C#

Insert/Update/Delete in GridView using asp.net C#

Step1:-
Create a employee table:



CREATE TABLE EMPLOYEE
(
 EMPID INT IDENTITY(100,1),
 FIRSTNAME VARCHAR(20),
 LASTNAME VARCHAR(20),
 ADDRESS VARCHAR(100),
 MOBILE VARCHAR(20)
)


NOW INSERT SOME VALUES IN EMPLOYEE TABLE:


INSERT INTO EMPLOYEE(FIRSTNAME,LASTNAME,ADDRESS,MOBILE)VALUES('SHIVAM','GUPTA','NEW DELHI','8826743157')


STEP2:- CREATE CONNECTION STRING IN WEB.CONFIG FILE.



<connectionStrings>
    <add name="mycon" connectionString="data source=SHIVAMGUPTA; initial catalog=operation; integrated security=true;" providerName="System.Data.SqlClient"/>
  </connectionStrings>


STEP3:- TAKE A GRIDVIEW IN YOUR .ASPX PAGE.



<asp:GridView ID="GridView1" runat="server" AutoGenerateDeleteButton="true" AutoGenerateColumns="false"
            AutoGenerateEditButton="True" 
            onpageindexchanging="GridView1_PageIndexChanging" 
            onrowcancelingedit="GridView1_RowCancelingEdit"  DataKeyNames="EmpID"
            onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" 
            onrowupdating="GridView1_RowUpdating">
          <Columns>
            <asp:BoundField DataField="EMPID" HeaderText="EmployeeID" />
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
            <asp:BoundField DataField="LastName" HeaderText="LastName" />
            <asp:BoundField DataField="Address" HeaderText="Address" />
            <asp:BoundField DataField="Mobile" HeaderText="Mobile" />
            
          </Columns>
        </asp:GridView>


        <asp:Label ID="lblMessage" runat="server"></asp:Label>






STEP4: NOW COME IN YOUR CODE BEHIND .ASPX.CS



public partial class _Default : System.Web.UI.Page
{
    SqlConnection con;
    protected void Page_Load(object sender, EventArgs e)
    {
        string conection;
        conection = System.Configuration.ConfigurationManager.ConnectionStrings["mycon"].ConnectionString.ToString();
         con = new SqlConnection(conection);
         if (!IsPostBack)
         {
             FillGrid();
         }


    }


    protected void FillGrid()
    {
      SqlCommand cmd = new SqlCommand("select * from employee", Con);
        con.Open();
        GridView1.DataSource = cmd.ExecuteReader();
        GridView1.DataBind();
        con.Close();
    }


    protected void GridView1_RowEditing(object sender,                           GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        FillGrid();
        
    }


    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int index = GridView1.EditIndex;
        GridViewRow row = GridView1.Rows[index];
        int Eid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);
        string FirstName = ((TextBox)row.Cells[2].Controls[0]).Text.ToString().Trim();
        string LastName = ((TextBox)row.Cells[3].Controls[0]).Text.ToString().Trim();
        string Address = ((TextBox)row.Cells[4].Controls[0]).Text.ToString().Trim();
        string Mobile = ((TextBox)row.Cells[5].Controls[0]).Text.ToString().Trim();




  string sql = "UPDATE EMPLOYEE SET FIRSTNAME='" + FirstName +      "',LastName='" + LastName + "',Address='" +Address + "',Mobile='" + Mobile + "' WHERE EMPID=" + Eid + "";


        SqlCommand cmd = new SqlCommand(sql, con);
        con.Open();
        int temp = cmd.ExecuteNonQuery();
        con.Close();
        if (temp == 1)
        {
            
            lblMessage.Text = "Record updated successfully";
        }
        GridView1.EditIndex = -1;
        FillGrid();
    }


    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {        
        int Eid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);       
        SqlCommand cmd = new SqlCommand("DELETE FROM EMPLOYEE WHERE EMPID=" + Eid + "", con);
        con.Open();
        int temp = cmd.ExecuteNonQuery();
        if (temp == 1)
        {
            lblMessage.Text = "Record deleted successfully";
        }
        con.Close();
        FillGrid();


    }


    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.EditIndex = e.NewPageIndex;
        FillGrid();
        
    }


    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        FillGrid();
    }
}