To perform CRUD operation (create, read, update and delete) operation with GridView, we can follow this
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.
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 CommandName
properties 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 ConfigurationManagerusing
System.Data; // to use DataSet or DataTableusing
System.Data.SqlClient; // to communicate with SQL Server databaseusing
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.
Keep reading my forth coming articles. To read my series of articles on ASP.NET,
click here.