Insert, Update, Delete In GridView Using ASP.Net C#

CREATE PROCEDURE EmpEntry
(
  -- Variable declarations
  @Action VARCHAR(10), -- To perform operation such as Insert, Update, Delete, Select
  @id INT = NULL, -- ID to perform specific task
  @Fname VARCHAR(50) = NULL, -- For FirstName
  @MName VARCHAR(50) = NULL, -- For MName
  @Lname VARCHAR(50) = NULL -- For LastName
)
AS
BEGIN
  SET NOCOUNT ON;

  IF @Action = 'Insert' -- Used to insert records
  BEGIN
    INSERT INTO employee (FirstName, MName, LastName) VALUES (@Fname, @MName, @Lname)
  END
  ELSE IF @Action = 'Select' -- Used to Select records
  BEGIN
    SELECT * FROM employee
  END
  ELSE IF @Action = 'Update' -- Used to update records
  BEGIN
    UPDATE employee SET FirstName = @Fname, MName = @MName, LastName = @Lname WHERE id = @id
  END
  ELSE IF @Action = 'delete' -- Used to delete records
  BEGIN
    DELETE FROM employee WHERE id = @id
  END
END

<form id="form1" runat="server">
    <div>
        First Name <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        Middle Name <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        Last Name <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" Text="Save" OnClick="Button1_Click" />
    </div>
    <asp:HiddenField ID="HiddenField1" runat="server" />
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
</form>

<asp:GridView ID="GridView1" runat="server" DataKeyNames="id" OnRowEditing="Edit" OnRowCancelingEdit="canceledit" OnRowDeleting="delete" OnRowUpdating="Update">
</asp:GridView>

On the preceding GridView properties I have assigned the method name to be called for particular operations.

Method to Insert Data in Database

Right-click from the design page and view the code and then write the following code in the default. aspx.cs page to save the inserted records in the database.

protected void empsave(object sender, EventArgs e)  
{  
    connection();  
    query = "studentEntryView"; //Stored Procedure name  
    SqlCommand com = new SqlCommand(query, con); //creating  SqlCommand  object  
    com.CommandType = CommandType.StoredProcedure; //here we declaring command type as stored Procedure  

    // adding parameters to  SqlCommand below  
    com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString(); //for ing hidden value to preform insert operation  
    com.Parameters.AddWithValue("@FName", TextBox1.Text.ToString());      //first Name  
    com.Parameters.AddWithValue("@Mname ", TextBox2.Text.ToString());    //middle Name  
    com.Parameters.AddWithValue("@LName ", TextBox3.Text.ToString());    //Last Name  
    com.ExecuteNonQuery();   //executing the sqlcommand  
    Label1.Visible = true;  
    Label1.Text = "Records are Submitted Successfully";  
}

Now create the method to view the records in the GridView.

public void viewdata()
{
    connection();
    query = "studentEntryView";
    SqlCommand com = new SqlCommand(query, con);
    com.CommandType = CommandType.StoredProcedure;
    com.Parameters.AddWithValue("@Action", HiddenField2.Value).ToString();
    DataSet ds = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter(com);
    da.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();
}

The following is the method for the "OnRowEditing" Event.

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

The following is the method for the "OnRowCancelingEdit" Event.

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

The following is the method for the "OnRowDeleting" Event.

protected void delete(object sender, GridViewDeleteEventArgs e)
{
    connection();
    int id = int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
    HiddenField1.Value = "Delete";
    query = "EmpEntry";
    com = new SqlCommand(query, con);
    com.CommandType = CommandType.StoredProcedure;
    com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
    com.Parameters.AddWithValue("id", SqlDbType.Int).Value = id;
    com.ExecuteNonQuery();
    con.Close();
    gedata();
}

The following is the method for the "OnRowUpdating" Event.

protected void update(object sender, GridViewUpdateEventArgs e)
{
    connection();
    int id = int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
    HiddenField1.Value = "update";
    query = "EmpEntry";
    com = new SqlCommand(query, con);
    com.CommandType = CommandType.StoredProcedure;
    com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
    com.Parameters.AddWithValue("@FName", ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text.ToString());
    com.Parameters.AddWithValue("@MName", ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text.ToString());
    com.Parameters.AddWithValue("@LName", ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text.ToString());
    com.Parameters.AddWithValue("@id", SqlDbType.Int).Value = id;
    com.ExecuteNonQuery();
    con.Close();
    GridView1.EditIndex = -1;
    gedata();
}

A brief introduction to the code

In the sample code above I have used the two string queries for giving the Stored Procedure name and the constr for storing the connection from the web. config file and another thing is that I have used a hidden field by which I am ing the action values that are required to our Stored Procedure.

Now our application is ready to use, press F5 or other as you know, then enter some values to TextBox and press the "Save" button.

Now after clicking on the "Save" button, the hidden field value takes the value "Insert" and es it to the Stored Procedure as the action and because of this, the Stored Procedure will execute a particular type of block.

Now at page load, I have called the method, so after that the grid will fill as in.

Now click on the Edit button that calls the edit method as shown in the following grid.

If you click on the "Cancel" button then the edit cancel method will be called and the edit mode will be canceled. Now enter some values into the grid TextBox and click on an update button that calls the update method and then the records in the GridView will be updated.

Now click on the delete button that calls the delete method and deletes the records from the GridView

Note

• For detailed code please download the zip file attached above.
• Don't forget to update the Web. config file for your server location.

Summary

From all the examples above we see how to reduce the code required to perform these tasks. In the next article, I will explain how to Implement a 2-tier architecture which makes my code much simpler and reusable, I hope this article is useful for all students and beginners. If you have any suggestions related to this article then please contact me.