Update and Delete in GridView

In this article, we will look at how to update or delete a record in a database table using a GridView control.

The GridView control is a tabular databound control but sometimes it is used like a spreadsheet control. It came with ASP.NET 2.0 and is an improved version of the DataGrid control. It displays a set of data items in an HTML table. We can use this to display data from a database and edit, sort and select the data.

Database Table Structure

We are using an "EMPLOYEE" table so the "EMPLOYEE" table design is:



Design of GridView

We design a GridView with which we can perform update and delete operations. Here we can update Name, Emp_Code and Emp_Age fields depending on the Id field of the "EMPLOYEE" table.

<asp:GridView ID="gvEmployee" runat="server"
            AutoGenerateColumns="False"
            DataKeyNames="Id"
            onrowcancelingedit="gvEmployee_RowCancelingEdit"
            onrowediting="gvEmployee_RowEditing"
            onrowdeleting="gvEmployee_RowDeleting"
            onrowupdating="gvEmployee_RowUpdating"
            GridLines="None"
            AllowPaging="true"
            CssClass="mGrid"
            PagerStyle-CssClass="pgr"
            AlternatingRowStyle-CssClass="alt"
            Width=40%>
            <Columns>
                <asp:TemplateField HeaderText="S.No." ItemStyle-HorizontalAlign="Center">
                    <ItemTemplate>
                        <%#Container.DataItemIndex+1%>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Employee Name">
                    <ItemTemplate>
                        <%#Eval("Name")%>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtempname" runat="server" Text='<%#Eval("Name") %>'></asp:TextBox>
                    </EditItemTemplate>
                </asp:TemplateField>
             <asp:TemplateField HeaderText="Employee Code">
                <ItemTemplate>
                    <%#Eval("Emp_Code")%>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox ID="txtempcode" runat="server" Text='<%#Eval("Emp_Code") %>'></asp:TextBox>
                </EditItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Employee Age">
                <ItemTemplate>
                    <%#Eval("Emp_Age")%>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox ID="txtempage" runat="server" Text='<%#Eval("Emp_Age") %>'></asp:TextBox>
                </EditItemTemplate>
            </asp:TemplateField>
            <asp:CommandField ShowEditButton="true" ButtonType ="Image" EditImageUrl="Image/edit.png" UpdateImageUrl="Image/accept.png"
            CancelImageUrl="Image/cancel.png" HeaderText="Edit" />
            <asp:CommandField ShowDeleteButton="true" ButtonType="Image" DeleteImageUrl="Image/delete.png" HeaderText="Delete" />
    </Columns>
</asp:GridView>

The following bulleted list describes each field of the GridView used in the design of it:

• AutoGenerateColumns: This property accepts only a Boolean value. The default value is true. When we set it to False, the BoundField objects are not created automatically; we will create the fields manually. When we set it to True then all table fields are retrieved by the SQL query or Stored Procedure and also bound the with GridView and we get all field columns in the GridView with their records as well as manual records.

• DataKeyNames: This property associates a value with each row in the GridView. Here we can assign the name of a single database column or more than one column. When we assign more than one database column then they are specified separated by commas. It represents datakey values. By this we uniquely identifiy records in GridView rows. Here we are using the "Id" field of the EMPLOYEE table; that field does not display in the GridView but this field value is used to access the table by row and can perform updates/deletes depending on this field value because it is the EMPLOYEE table's primary key field. Mostly it is a primary key field or unique field.

• <Columns>: It is a collection of columns that the GridView contains. By this we can control what GridView columns are displayed, which means we can change the order of the appearance of the columns.

• <asp:TemplateField>: Is used to add ASP.Net controls in GridView rows. One TemplateField represents one column in the GridView. It customizes the appearance of the UI in the GridView. It is useful when we are using a GridView to edit a database record.

• <ItemTemplate>: It specifies the contents that are displayed in a TemplateField object. It displays a layout for the TemplateField.

• <EditItemTemplate>: It also specifies contents that are displayed in a TemplateField but in EditMode.

• Eval: It is a method that is used to display data. In this method we pass a database field name as an argument and that database field is displayed by this. This method is used when we will edit a row record otherwise we can use the Bind method.

• <asp:CommandField>: It is a special field that displays command buttons to perform selecting, editing, inserting and deleting. Here we define the image type button so it can display an icon of the operation instead of their name.

Create SQL Connection

We need a connection with a Microsoft SQL Server database so we can access data and perform an operation on it. We define a connection in the web.config file and give a name to the connection string.

<connectionStrings>
    <add name="connStr" connectionString="Data Source= sandeepss-PC;database=Development;user=sa;password=knowdev"/>
</connectionStrings>

Now we access this connection string on the .cs page so we can open a connection with the relation database. We need to use two namespaces. The first one accesses the connection string and the other is for the SQL connection.

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

Now we define a method for opening a connection with the relation database and returns a SQL connection object depending on the connection string that is passed as a parameter of the SqlConnection class constructor.

private SqlConnection GetConnection()
{
    string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
    SqlConnection con = new SqlConnection(connStr);
    con.Open();
    return con;
}

Populate Employee Details

Create a Stored Procedure to retrieve all employee details from the "EMPLOYEE" table:

CREATE PROCEDURE _sp_GetEmployeeDetail
AS
BEGIN
     SET NOCOUNT ON;
     SELECT Id,NAME, Emp_Code,Emp_Age FROM EMPLOYEE
END
GO

We create a method to get all employee details and bind the retrieved data with the employee grid. So first we need to use a namespace, as in:

using System.Data;

Now create a method for get employee details

private void GetEmployeeDetail()
{
    SqlConnection con = GetConnection();
    DataTable dt = new DataTable();
    SqlCommand cmd = new SqlCommand("_sp_GetEmployeeDetail", con);
    cmd.CommandType = CommandType.StoredProcedure;
    if (con.State == ConnectionState.Closed)
    {
        con.Open();
        IDataReader dr = cmd.ExecuteReader();
        dt.Load(dr);
    }
    gvEmployee.DataSource = dt;
    gvEmployee.DataBind();
    con.Close();
}

To populate the GridView we need to call this method on page load.

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        GetEmployeeDetail();
    }
}

Delete a Record

We can delete a record from the database table by clicking on the delete button that is in the Delete column of the GridView. We first of all create a Stored Procedure that gets the Id of an employee as a parameter from the GridView on whichever row the user clicked.

CREATE PROCEDURE _sp_DeleteEmployee
(
     @Id int
)
AS
BEGIN
     DELETE FROM EMPLOYEE WHERE Id=@Id
END
GO

Now we use the RowDeleting Event of GridView. In this event, first we get the datakey value that is the id of an employee of whichever row the user clicked and that id is passed as a parameter to the Stored Procedure. After that the GridView is rebound with new data.

protected void gvEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    int id = Convert.ToInt32(gvEmployee.DataKeys[e.RowIndex].Value.ToString());
    SqlConnection con = GetConnection();
    SqlCommand cmd = new SqlCommand("_sp_DeleteEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Id", id);
    if (con.State == ConnectionState.Closed)
    {
        con.Open();
    }
    cmd.ExecuteNonQuery();
    GetEmployeeDetail();
}

Update a Record

To update a record in the GridView we need to perform the following three operations:

1. Row move in edit mode.

2. Cancel edit operation and get original state without any update

3. Update record and get updated state of GridView.

According to the above, in these three operations we use three events of the GridView for each operation. Point numbers 2 and 3 are performed after point 1 is performed.

1. Row move in edit mode

We have a GridView with records. We can't update a record until that record doesn't exist in the input type field (TextBox). So first we need to move to an updateable row record in the TextBox. To do that we need a RowEditing event of the GridView.

protected void gvEmployee_RowEditing(object sender, GridViewEditEventArgs e)
{
    gvEmployee.EditIndex = e.NewEditIndex;
    GetEmployeeDetail();
}

2. Cancel the edit operation and get the original state without any update.

When the row is in edit mode and we don't want to update a record then we want to move to the original state of the GridView so we use the RowCancelingEdit event of the GridView.

protected void gvEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    gvEmployee.EditIndex = -1;
    GetEmployeeDetail();
}

The EditIndex property of a GridView determines the index of a row in edit mode. EditIndex=-1 means that no row is in edit mode.

3. Update record and get updated state of the GridView.

When a GridView row is in edit mode then we can update the row's record in the relational database table. So we need to create a Stored Procedure on which we pass all parameters that are updateable along with the primary key value, in other words the row data's key value that is being updated.

CREATE PROCEDURE _sp_UpdateEmployee
(
     @Id int,
     @Name nvarchar(30),
     @Emp_Code varchar(10),
     @Emp_Age int
)
AS
BEGIN
     UPDATE EMPLOYEE SET Name=@Name,
     Emp_Code=@Emp_Code,
     Emp_Age=@Emp_Age WHERE Id=@Id
END
GO

We use the RowUpdating event of GridView to update the record in the database table. We get the Id value using the row index and another TextBox value is also retrieved. All updated values are passed to the Stored Procedure.

protected void gvEmployee_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    int id = Convert.ToInt32(gvEmployee.DataKeys[e.RowIndex].Value.ToString());
    string name = ((TextBox)gvEmployee.Rows[e.RowIndex].FindControl("txtempname")).Text;
    string emp_code = ((TextBox)gvEmployee.Rows[e.RowIndex].FindControl("txtempcode")).Text;
    int emp_age = Convert.ToInt32(((TextBox)gvEmployee.Rows[e.RowIndex].FindControl("txtempage")).Text);
    SqlConnection con = GetConnection();
    if (con.State == ConnectionState.Closed)
    {
        con.Open();
    }
    SqlCommand cmd = new SqlCommand("_sp_UpdateEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Id ", id);
    cmd.Parameters.AddWithValue("@Name ", name);
    cmd.Parameters.AddWithValue("@Emp_Code ", emp_code);
    cmd.Parameters.AddWithValue("@Emp_Age ", emp_age);
    cmd.ExecuteNonQuery();
    con.Close();
    gvEmployee.EditIndex = -1;// no row in edit mode
    GetEmployeeDetail();
}