In this article, I am going to explain how to manipulate data using GridView control. This article scope is limited to Updating and Deleting records using GridView and I am not using any readymade Data controls for that but manually writing all event methods. I will be using Sql objects directly into methods to keep the example simple and straight forward. In practical scenario you should use your existing architecture to populate and update the data.
In this article, I am going to take a simple database table that has following fields:
AutoID - Int, AutoIncrement, Primary Key
PageName - Varchar(50)
PageDescription - Varchar(500)
Active - Bit(1)
In this article, I am not going to explain the workings of Sql objects but focusing on the events and methods of the GridView that will help us in updating and deleting the records. Now let’s start with putting the GridView on .aspx page. Following is the code for the GridView.
GridView Code
<asp:Label ID="lblMessage" runat="Server" ForeColor="Red"></asp:Label>
<asp:GridView ID="GridView1" runat="Server" AutoGenerateColumns="False" BorderWidth="1"
DataKeyNames="AutoID" AutoGenerateEditButton="True" OnRowEditing="EditRecord"
OnRowCancelingEdit="CancelRecord" OnRowUpdating="UpdateRecord" CellPadding="4"
HeaderStyle-HorizontalAlign="left" OnRowDeleting="DeleteRecord" RowStyle-VerticalAlign="Top"
ForeColor="#333333" GridLines="None">
<Columns>
<asp:BoundField DataField="AutoID" HeaderText="AutoID" ReadOnly="True" />
<asp:TemplateField HeaderText="Page Name">
<ItemTemplate>
<%# Eval("PageName") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtPageName" runat="Server" Text='<%# Eval("PageName") %>' Columns="30"></asp:TextBox>
<asp:RequiredFieldValidator ID="req1" runat="Server" Text="*" ControlToValidate="txtPageName"></asp:RequiredFieldValidator>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Page Description">
<ItemTemplate>
<%# Eval("PageDescription") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtPageDesc" runat="Server" TextMode="MultiLine" Rows="10" Columns="50"
Text='<%# Eval("PageDescription") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="req2" runat="Server" Text="*" ControlToValidate="txtPageDesc"></asp:RequiredFieldValidator>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Active">
<ItemTemplate>
<%# Eval("Active") %>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="dropActive" runat="server" SelectedValue='<%# Eval("Active").ToString().ToLower().Equals("true") ? "True" : "False" %>'>
<asp:ListItem Text="Yes" Value="True"></asp:ListItem>
<asp:ListItem Text="No" Value="False"></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete?">
<ItemTemplate>
<span onclick="return confirm('Are you sure to Delete the record?')">
<asp:LinkButton ID="lnkB" runat="Server" Text="Delete" CommandName="Delete"></asp:LinkButton>
</span>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" VerticalAlign="Top" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
In the above code, I have kept
AutoGenerateColumns as false so that GridView will not render all the fields of the data source automatically. I have specified
DataKeyNames as AutoID, the primary key of the database table. Then I have several methods attached with following events
Get video of hundreds of ASP.NET Tips and Tricks -
http://www.itfunda.com/aspnet-how-to-tips-and-tricks/Show/50 .
Events -
Methods
OnRowEditing -
EditRecord
OnRowCancelEdit -
CancelRecord
OnRowUpdating -
UpdateRecord
OnRowDeleting -
DeleteRecord
As I set AutoGenerateColumns property to false so I am going to manually write the fields name that I need to appear in the way I want. So I have to specify how I want them to appear in the normal view and edit view. That’s why I have placed different templates like ItemTemplate (for normal view) and EditItemTemplate (for edit view). You can notice that I have kept RequiredFieldValidator too inside the EditItemTemplate as I want the textbox to be validated for empty data. In the same way you can put any kind of Validation controls inside EditItemTemplate to validate the form element while user will click Update link. For PageName and PageDescription field I want them to appear in the TextBox in edit view, so I have specified text property of TextBox as the field value in EditItemTemplate and for Active field, I want it to appear as the DropDownList so I have specified the SelectedValue property as the value of the Active field.
You may notice that the last column of the GridView is a Delete column that let user delete the record from database, so I have placed Delete link button inside the <span> tag and have specified onClick property as JavaScript confirm return value, so that when user clicks on it he/she sees a confirmation box whether he/she really wants to delete the record.
Till now our GridView is ready to be populated, now let’s write a method to populate the GridView.
Populating the GridView Control
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
}
}
/// <summary>
/// Bind data to the grid
/// </summary>
private void BindData()
{
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter dAd = new SqlDataAdapter("select * from mySampleTable", conn);
DataSet dSet = new DataSet();
try
{
dAd.Fill(dSet, "PagesData");
GridView1.DataSource = dSet.Tables["PagesData"].DefaultView;
GridView1.DataBind();
}
catch (Exception ee)
{
lblMessage.Text = ee.Message.ToString();
}
finally
{
dSet.Dispose();
dAd.Dispose();
conn.Close();
conn.Dispose();
}
}
To populate the GridView, I am calling
BindData() method from Page_Load event after checking IsPostBack property of the page, so the GridView will only be populated when there is no postback on the page (when the page loads for the first time). This will make sure that BindData() method will not fire when you are going to edit, update or delete the record.
In BindData() method, I am getting the data from database using Sql objects and specifying the DataSource property of the GridView to the PagesData table of the DataSet.
Editing GridView Control
/// <summary>
/// fires when edit link is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void EditRecord(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindData();
}
When Edit link of the GridView will be clicked then
OnRowEditing event will fire that will call
EditRecord method. In this method, I am specifying the EditIndex property of the GridView to the NewEditIndex of the GridView and then calling BindData() method again to bind the data. Please note that when you will not bind the data again, GridView will not change in edit mode.
Cancel Editing GridView Control
/// <summary>
/// fires when cancel link is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void CancelRecord(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindData();
}
When Cancel link is clicked on GridView in edit mode,
OnRowCancelingEdit event will fire that will call
CancelRecord method. In this method, I am specifying the EditIndex property of the GridView to -1. As there is no row at -1 position so GridView will be shown in normal mode.
Updating GridView Control
/// <summary>
/// fires when update link is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void UpdateRecord(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
int autoid = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
TextBox tPageName = (TextBox)row.FindControl("txtPageName");
TextBox tPageDesc = (TextBox)row.FindControl("txtPageDesc");
DropDownList dActive = (DropDownList)row.FindControl("dropActive");
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());
SqlCommand dCmd = new SqlCommand();
try
{
conn.Open();
dCmd.CommandText = "spUpdateData";
dCmd.CommandType = CommandType.StoredProcedure;
dCmd.Parameters.Add("@AutoID", SqlDbType.Int).Value = autoid;
dCmd.Parameters.Add("@PageName", SqlDbType.VarChar, 50).Value = tPageName.Text.Trim();
dCmd.Parameters.Add("@PageDescription", SqlDbType.VarChar, 500).Value = tPageDesc.Text.Trim();
dCmd.Parameters.Add("@Active", SqlDbType.Bit).Value = bool.Parse(dActive.SelectedValue);
dCmd.Connection = conn;
dCmd.ExecuteNonQuery();
lblMessage.Text = "Record Updated successfully.";
// Refresh the data
GridView1.EditIndex = -1;
BindData();
}
catch (SqlException ee)
{
lblMessage.Text = ee.Message;
}
finally
{
dCmd.Dispose();
conn.Close();
conn.Dispose();
}
}
When Update link of the GridView will be clicked (in edit mode),
OnRowUpdating event will fire that will call
UpdateRecord method. In this method, we have to get changed values of TextBox and DropDown list and also we need the primary key value for the record that is being edited. To get the Primary key, I have used DataKeys property of the GridView with the index of the row, being edited and stored into a integer variable after parsing. Now I have declared a GridViewRow variable named row and stored the current row being edited into it. With the help row variable, I am going to find all the controls placed under that row in edit mode using FindControl property. After finding the controls I am using them to pass their values to the parameter of the SqlCommand object that is ultimately updating the records into database. After that again, I am doing the same thing that I had done in CancelRecord method, I mean setting EditIndex property of the GridView to -1 and rebinding the data again by calling BindData() method.
Deleting records from GridView Control
/// <summary>
/// Delete records from database
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void DeleteRecord(object sender, GridViewDeleteEventArgs e)
{
string autoid = GridView1.DataKeys[e.RowIndex].Value.ToString();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());
SqlCommand dCmd = new SqlCommand();
try
{
conn.Open();
dCmd.CommandText = "spDeleteData";
dCmd.CommandType = CommandType.StoredProcedure;
dCmd.Parameters.Add("@AutoID", SqlDbType.Int).Value = Int32.Parse(autoid);
dCmd.Connection = conn;
dCmd.ExecuteNonQuery();
lblMessage.Text = "Record Deleted successfully.";
// Refresh the data
BindData();
}
catch (SqlException ee)
{
lblMessage.Text = ee.Message;
}
finally
{
dCmd.Dispose();
conn.Close();
conn.Dispose();
}
}
When Delete link of the GridView will be clicked, it will confirm the user whether they really want to Delete the record, if they will click OK, it will fire OnRowDeleting event of the GridView that will call
DeleteRecord method. In this method again, I am getting the primary key value using DataKeys property of the GridView and using it to delete the records from the database. After successfully deleting records, again I am calling BindData() method to rebind the GridView.
Hope this article helps to begin working with GridView control. If you have a feedback or suggestion, please do write to me.
Enjoy and Happy Coding !!!