How to perform Edit, Update, and Delete operation in GridView?

To perform CRUD operation (create, read, update and delete) operation with GridView, we can follow this
approach.

Introduction

GridView control is a powerful data grid control that allows us to display the data in tabular format with facilities for sorting and pagination. It also allows us to manipulate the data.

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

 

This article is one of the series of articles I am writing for last couple of days on GridView in ASP.NET. To perform CRUD (create, read, update and delete) operation with GridView, we can follow the below approach.

 

ASPX PAGE

<asp:Label ID="lblMessage" runat="server" ForeColor="Green" EnableViewState="false" />

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"

AutoGenerateColumns="false" Width="100%" OnRowEditing="EditRow" OnRowCancelingEdit="CancelEditRow"

OnRowUpdating="UpdateRow" DataKeyNames="AutoId" OnRowDeleting="DeleteRow" AllowPaging="true"

PageSize="3" OnPageIndexChanging="ChangePage">






<Columns>






<asp:TemplateField HeaderText="Edit">






<ItemTemplate>






<asp:LinkButton ID="lnkEdit" runat="server" Text="Edit" CommandName="Edit" />


</ItemTemplate>

<EditItemTemplate>






<asp:LinkButton ID="lnkUpdate" runat="server" Text="Update" CommandName="Update" />

<asp:LinkButton ID="lnkCancel" runat="server" Text="Cancel" CommandName="Cancel" />


</EditItemTemplate>


</asp:TemplateField>

<asp:BoundField HeaderText="AutoId" DataField="AutoId" ReadOnly="true" />

<asp:TemplateField HeaderText="First Name">






<ItemTemplate>


<%# Eval("FirstNAme") %></ItemTemplate>
<EditItemTemplate>






<asp:TextBox ID="txtFirstName" runat="server" Text='<%# Eval("FirstName") %>'/>


</EditItemTemplate>


</asp:TemplateField>

<asp:TemplateField HeaderText="Last Name">






<ItemTemplate>


<%# Eval("LastName") %></ItemTemplate>
<EditItemTemplate>






<asp:TextBox ID="txtLastName" runat="server" Text='<%# Eval("LastName") %>'/>


</EditItemTemplate>


</asp:TemplateField>

<asp:TemplateField HeaderText="Age">






<ItemTemplate>


<%# Eval("Age") %></ItemTemplate>
<EditItemTemplate>






<asp:TextBox ID="txtAge" runat="server" Text='<%# Eval("Age") %>' Columns="3" />

<asp:RequiredFieldValidator ID="REw" runat="server" ControlToValidate="txtAge" Text="*" />


</EditItemTemplate>


</asp:TemplateField>

<asp:TemplateField HeaderText="Is Active?">






<ItemTemplate>


<%# Eval("Active").ToString().Equals("True") ? "Yes" : "No" %>
</ItemTemplate>

<HeaderStyle HorizontalAlign="Left" />

<EditItemTemplate>






<asp:DropDownList ID="dropActive" runat="server" SelectedValue='<%# Eval("Active") %>'>

<asp:ListItem Text="Yes" Value="True" />

<asp:ListItem Text="No" Value="False" />

</asp:DropDownList>


</EditItemTemplate>


</asp:TemplateField>

<asp:TemplateField HeaderText="Delete?">






<ItemTemplate>






<span onclick="return confirm('Are you sure to delete?')">

<asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" ForeColor="Red" CommandName="Delete" />

</span>


</ItemTemplate>


</asp:TemplateField>


</Columns>

<AlternatingRowStyle BackColor="White" />

<EditRowStyle BackColor="#efefef" />

<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />

<RowStyle BackColor="#EFF3FB" />

<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />

<SortedAscendingCellStyle BackColor="#F5F7FB" />

<SortedAscendingHeaderStyle BackColor="#6D95E1" />

<SortedDescendingCellStyle BackColor="#E9EBEF" />

<SortedDescendingHeaderStyle BackColor="#4870BE" />


</asp:GridView>

In the above code snippet, on the ASPX Page we have specified the TemplateField (TemplateField has ItemTemplate child tag that is used in the readonly listing and EditItemTemplate tag that is used when that column is shown in the Edit mode) of the GridView for all columns except the AutoId as it is our primary key and should not be edited. The EditItemTemplate has input controls that are used to hold the data in the edit mode. For the last column ("Is Active?"), we should have yes/no option to select so we have kept the DropDownList and specified its SelectedValue so that the value from database for that record is selected. Rest other column has simple textbox value so we have kept TextBoxes and specified their "Text" property to the value from the data source.
The first column of this GridView is the "Edit" column and the last column is the "Delete" column. Instead of using the AutoGenerated buttons, we have explicitely defined the Edit, Cancel and Delete buttons by specifying their CommandNameproperties to “Edit”, “Cancel” and “Delete” respectively.
In order to perform Edit, Cancel, Update and Delete operations, we need to handle OnRowEditing, OnRowCancelEdit,OnRowUpdating and OnRowDeleting events and we have devised “EditRow”, CancelEditRow”, “UpdateRow”, and “DeleteRow” server side methods respectively for those events.

DATABASE USED

we have used SQL Server 2008 R2 Express edition database and our database name is “DemoDatabase”. The table name is “PersonalDetail” as displayed below. Of course, the AutoId is the Identity Increment (auto crement) field.

Namespace to use

using System.Configuration; // to use the ConfigurationManager

using System.Data; // to use DataSet or DataTable

using System.Data.SqlClient; // to communicate with SQL Server database

using System.Text; // for StringBuilder

The connectionString in the web.config looks like below

<connectionStrings>


<add name="ConnStr" connectionString="Data Source=SUNITAPC\SQLEXPRESS;Initial Catalog=DemoDatabase;Persist Security Info=True;User ID=demo;Password=demo;"/>
</connectionStrings>

CODE BEHIND

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;



protected void Page_Load(object sender, EventArgs e)

{






if (!IsPostBack)

{


PopulateData();
}
}

protected void EditRow(object sender, GridViewEditEventArgs e)

{


GridView1.EditIndex = e.NewEditIndex;





this.PopulateData();


}

protected void CancelEditRow(object sender, GridViewCancelEditEventArgs e)

{


GridView1.EditIndex = -1;





this.PopulateData();


}

protected void DeleteRow(object sender, GridViewDeleteEventArgs e)

{






var autoID = GridView1.DataKeys[e.RowIndex].Value;

using (SqlConnection conn = new SqlConnection(_connStr))

{






string sql = "Delete from PersonalDetail" +

" where AutoId = @AutoId";

using (SqlCommand cmd = new SqlCommand(sql, conn))

{


cmd.Parameters.AddWithValue(



"@AutoId", autoID);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();
}
}

lblMessage.Text = "Record has been deleted successfully !";

lblMessage.ForeColor = System.Drawing.Color.Red;

this.PopulateData();


}

protected void ChangePage(object sender, GridViewPageEventArgs e)

{


GridView1.PageIndex = e.NewPageIndex;





this.PopulateData();


}

protected void UpdateRow(object sendedr, GridViewUpdateEventArgs e)

{






var autoID = GridView1.DataKeys[e.RowIndex].Value;

GridViewRow row = GridView1.Rows[e.RowIndex] as GridViewRow;

TextBox tFirstName = row.FindControl("txtFirstName") as TextBox;

TextBox tLastName = row.FindControl("txtLastName") as TextBox;

TextBox tAge = row.FindControl("txtAge") as TextBox;

DropDownList dropActive = row.FindControl("dropActive") as DropDownList;

using (SqlConnection conn = new SqlConnection(_connStr))

{






string sql = "Update PersonalDetail set FirstName = @FirstName,LastName=@LastName, Age= @Age, Active = @Active" + " where AutoId = @AutoId";

using (SqlCommand cmd = new SqlCommand(sql, conn))

{


cmd.Parameters.AddWithValue(



"@FirstName", tFirstName.Text.Trim());

cmd.Parameters.AddWithValue("@LastName", tLastName.Text.Trim());

cmd.Parameters.AddWithValue("@Age", tAge.Text.Trim());

cmd.Parameters.AddWithValue("@Active", dropActive.SelectedValue);

cmd.Parameters.AddWithValue("@AutoId", autoID);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();
}
}

lblMessage.Text = "Record updated successfully !";

GridView1.EditIndex = -1;

this.PopulateData();


}

private void PopulateData()

{






DataTable table = new DataTable();

using (SqlConnection conn = new SqlConnection(_connStr))

{






string sql = "Select * from PersonalDetail";

using (SqlCommand cmd = new SqlCommand(sql, conn))

{






using (SqlDataAdapter ad = new SqlDataAdapter(cmd))

{


ad.Fill(table);
}
}
}

GridView1.DataSource = table;

GridView1.DataBind();
}

EditRow method

As soon as the "Edit" link is clicked, this method executes and set the GridView’s "EditIndex" is set to the NewEditIndex of the event arguments and then re-populate the data, this brings that GridView row into the Edit view as displayed in the picture below.

CancelEditRow method

When "Cancel" button is clicked, this method executes and it sets the "EditIndex" property to -1 so that no record is set into the Edit mode and then re-populate the GridView. This brings the record being edited to normal mode.

UpdateRow method

In the UpdateRow method, first we have retrieved the DataKey (the autoid) value that is used to update the selected record. Then we have found the GridViewRow that is going to be edited using the GridView.Rows and passing the index of the current row being edited. Once we have the row, we can find all the TextBoxes and DropDownList we had into the EditTemplate.
Next we have used ADO.NET to execute the Udpate SQL statement and updated the reocord in database. Once the record is updated, we need to again set the EditIndex of the GridView to -1 so that no record is set in the Edit mode and then re-populate the data so that updated records are displayed in the GridView.

OUTPUT

Thanks for reading, hope you liked it.