Introduction
- This is comprehensive article on the GridView events.
- Operations/Events covered are: Update, delete, Insert, Paging, Sorting, RowDataBound.
- It includes the working code and explanations.
- This does not use the codeless method of the SqlDataSource.
- Uses the DataSet model.
- After any update/delete/insert the rows shall remain sorted in the selected order.
- The result of the Update/ Delete / Insert is displayed.
- Initially the rows shall be sorted in the default order.
- Paging is done through normal default way, it can be made efficient by caching the dataset and using it, not going to database on each paging operation, may be in the next article I will put it in. Otherwise this will become bit complex.
- Code in the article uses the oops approach and the relevant part of the code can be moved to the business layer and the data layer appropriately to ease the maintenance, enhance the extensibility and scalability.
BackGround
There are many articles on the GridView and it’s working, however they have been on the particular functionality in focus. In this article I have tried to put together most of the operations on the Grid View those are needed on regular basis.
Using the Code
- Components:
- Database: It uses two tables: Product and category.
- Product is referencing the category table.
- The Grid – the aspx file.
- The code behind
- Class file.
Steps
- Set/select the database.
- Write the database operation methods in the class file. We call these methods from the code behind.
- There are various events fired from the GridView we capture them in the Code Behind and use the methods defined in class file accordingly.
Note
ProductId is not displayed to the user, however we are using it to capture Insert/ Update and Delete internally. As it is a key we may not like to get it touched by the user. - While adding the records product ID is automatically generated. There may be various methods to generate it. I have used one of the simplest ways to produce it for demonstration.
Code
The ASPX: main constituents of the ASPX code.
<asp:GridView ID="grid1" runat="server" AutoGenerateColumns="false"
DataKeyNames="ProductId,Category"
OnRowDataBound="grid1_OnRowDataBound"
OnRowEditing="grid1_OnRowEditing"
OnRowUpdating="grid1_OnRowUpdating"
OnRowDeleting="grid1_OnRowDeleting"
OnRowCommand="grid1_OnRowCommand"
ShowFooter="true"
OnRowCancelingEdit="grid1_OnRowCancelingEdit"
AllowSorting="true"
OnSorting ="grid1_OnSorting"
AllowPaging="true"
PageSize="2"
OnPageIndexChanging="grid1_OnPageIndexChanging"
EmptyDataText="No records Selected."
>
<Columns>
<asp:TemplateField HeaderText="Product Name" SortExpression="name">
<ItemTemplate>
<asp:Label ID="label1" runat="server" Text='<%#Bind("name") %>'>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtName" runat="server" Text='<%#Eval("name") %>'>
</asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtNameInput" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Product No">
<ItemTemplate>
<asp:Label ID="label2" runat="server"
Text='<%#Bind("ProductNumber") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate >
<asp:TextBox ID="txtProductNo" runat="server"
text='<%# Bind("ProductNumber") %>' ></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtProductNumberInput" runat="server">
</asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Color" SortExpression="Color">
<ItemTemplate>
<asp:Label ID="label3" runat="server" Text='<%#Eval("Color") %>'>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtColor" runat="server"
Text='<%#Bind("Color") %>'>'</asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtColorInput" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Category" SortExpression="Category">
<ItemTemplate>
<asp:Label ID="label4" runat="server"
Text='<%#Eval("Category") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID ="ddlCategory" runat="server"
DataTextField="cat_id" DataValueField="cat_id">
</asp:DropDownList>
</EditItemTemplate>
<FooterTemplate >
<asp:DropDownList ID ="ddlCategoryInput" runat="server"
DataTextField="cat_id" DataValueField="cat_id">
</asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Cost" SortExpression="Cost">
<ItemTemplate>
<asp:Label ID="label7" runat="server" Text='<%#Eval("Cost") %>'>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCost" runat="server"
Text='<%#Bind("Cost") %>'>'</asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCostInput" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit" ShowHeader="false">
<EditItemTemplate>
<asp:LinkButton ID="linkButton1" runat="server"
CausesValidation="true"
Text="Update" CommandName="update"></asp:LinkButton>
<asp:LinkButton ID="linkButton2" runat="server"
CausesValidation="false"
Text="Cancel" CommandName="Cancel"></asp:LinkButton>
</EditItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="linkButton2" runat="server"
CausesValidation="false"
Text="Add Record" CommandName="Add_Rec"></asp:LinkButton>
</FooterTemplate>
<ItemTemplate>
<asp:LinkButton ID="linkButton1" runat="server"
CausesValidation="false"
Text="Edit" CommandName="Edit"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Delete" ShowDeleteButton="true"
ShowHeader="true" />
</Columns>
</asp:GridView>
<br />
<br />
<asp:Label ID="result" runat="server" ForeColor="Blue"></asp:Label>
The Class File
Don’t forget to add: using System.Data.SqlClient
public class Product
{
public Product()
{
}
public static DataTable FetchProducts()
{
string StrCon = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
SqlConnection Conn = new SqlConnection(StrCon);
string SqlSelect =
"select ProductId ,Name ,ProductNumber ,Color ,Category , Cost from products ";
SqlCommand cmd = new SqlCommand(SqlSelect,Conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds,"table1");
return ds.Tables[0];
}
public static DataTable FetchCategory()
{
string StrCon = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
SqlConnection Conn = new SqlConnection(StrCon);
string SqlSelect = "select Cat_Id, Cat_Desc from Category ";
SqlCommand Cmd = new SqlCommand(SqlSelect, Conn);
SqlDataAdapter da = new SqlDataAdapter(Cmd);
DataSet ds = new DataSet();
da.Fill(ds, "table1");
return ds.Tables[0];
}
public static int update(string ProductId_p, string Name_p, string ProductNumber_p,
string Color_p, string Category_p, string Cost_p )
{
string StrCon = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
SqlConnection Con = new SqlConnection(StrCon);
string SqlUpd =
"update products set Name=@Name, ProductNumber=@ProductNumber ," +
"Color=@Color ,Category=@Category, Cost=@Cost where ProductId=@ProductId";
SqlCommand cmd = new SqlCommand(SqlUpd, Con);
cmd.Parameters.Add(new SqlParameter("@Name", Name_p));
cmd.Parameters.Add(new SqlParameter("@ProductNumber", ProductNumber_p));
cmd.Parameters.Add(new SqlParameter("@Color", Color_p));
cmd.Parameters.Add(new SqlParameter("@Category", Category_p));
cmd.Parameters.Add(new SqlParameter("@Cost", Cost_p));
cmd.Parameters.Add(new SqlParameter("@ProductId", ProductId_p));
Con.Open();
int upd = cmd.ExecuteNonQuery();
Con.Close();
return upd;
}
public static int insertRow(string Name_p, string ProductNumber_p, string Color_p,
string Category_p, string Cost_p)
{
string strcon = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
SqlConnection con = new SqlConnection(strcon);
string sqlselect = "select max(ProductId) from products";
SqlCommand cmd = new SqlCommand(sqlselect, con);
con.Open();
int maxID = (int)cmd.ExecuteScalar();
maxID++;
string sqlInsert = "insert into products values (@ProductId, @Name,
@ProductNumber, @Color, @Category, @Cost )";
cmd.CommandText = sqlInsert;
cmd.Connection= con;
cmd.Parameters.Add(new SqlParameter("@ProductId", maxID));
cmd.Parameters.Add(new SqlParameter("@Name", Name_p));
cmd.Parameters.Add(new SqlParameter("@ProductNumber", ProductNumber_p));
cmd.Parameters.Add(new SqlParameter("@Color", Color_p));
cmd.Parameters.Add(new SqlParameter("@Category", Category_p));
cmd.Parameters.Add(new SqlParameter("@Cost", Cost_p));
int rowInsert = cmd.ExecuteNonQuery();
con.Close();
return rowInsert;
}
public static int RowDelete(string ProductId_p)
{
string StrCon = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
SqlConnection Conn = new SqlConnection(StrCon);
string sqlDelete = "delete from products where ProductId = @ProductId";
SqlCommand cmd = new SqlCommand(sqlDelete, Conn);
cmd.Parameters.Add(new SqlParameter("@ProductId", ProductId_p));
Conn.Open();
int rowdel = cmd.ExecuteNonQuery();
Conn.Close();
return rowdel;
}
}
The Code Behind
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fill_grid();
}
}
public void grid1_OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
DropDownList ddlCategory = (DropDownList)e.Row.FindControl("ddlCategory");
if (ddlCategory != null)
{
ddlCategory.DataSource = Product.FetchCategory();
ddlCategory.DataBind();
ddlCategory.SelectedValue =
grid1.DataKeys[e.Row.RowIndex].Values[1].ToString();
}
}
if (e.Row.RowType == DataControlRowType.Footer)
{
DropDownList ddlCategoryInput = (DropDownList)e.Row.FindControl(
"ddlCategoryInput");
ddlCategoryInput.DataSource = Product.FetchCategory();
ddlCategoryInput.DataBind();
}
}
public void grid1_OnRowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtName = (TextBox)grid1.Rows[e.RowIndex].FindControl("txtName");
TextBox txtProductNo = (TextBox)grid1.Rows[e.RowIndex].FindControl("txtProductNo");
TextBox txtColor = (TextBox)grid1.Rows[e.RowIndex].FindControl("txtColor");
DropDownList ddlCategory = (DropDownList)grid1.Rows[e.RowIndex].FindControl(
"ddlCategory");
TextBox txtCost = (TextBox)grid1.Rows[e.RowIndex].FindControl("txtCost");
int upd= Product.update(grid1.DataKeys[e.RowIndex].Values[0].ToString(),
txtName.Text ,
txtProductNo.Text,
txtColor.Text,
ddlCategory.SelectedValue.ToString(),
txtCost.Text
);
grid1.EditIndex = -1;
fill_grid();
result.Text = upd.ToString() + " : Row updated";
}
protected void fill_grid()
{
DataTable dt = Product.FetchProducts();
if (dt.Rows.Count > 0)
{
DataView dv = new DataView(dt);
if (Session["sortexp"] != null)
{
string srtexpr = Session["sortexp"].ToString();
switch (srtexpr)
{
case "name":
dv.Sort = "name";
break;
case "color":
dv.Sort = "color";
break;
case "Category":
dv.Sort = "Category";
break;
case "Cost":
dv.Sort = "Cost";
break;
default:
grid1.DataSource = dv;
break;
}
}
grid1.DataSource = dv;
grid1.DataBind();
}
else
{
result.Text = " No rows Selected.";
}
}
public void grid1_OnRowEditing(object sender, GridViewEditEventArgs e)
{
grid1.EditIndex = e.NewEditIndex;
fill_grid();
}
public void grid1_OnRowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grid1.EditIndex= -1;
result.Text = " ";
fill_grid();
}
public void grid1_OnRowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Add_Rec")
{
TextBox txtNameInput = (TextBox)grid1.FooterRow.FindControl("txtNameInput");
TextBox txtProductNumberInput = (TextBox)grid1.FooterRow.FindControl(
"txtProductNumberInput");
TextBox txtColorInput = (TextBox)grid1.FooterRow.FindControl("txtColorInput");
DropDownList ddlCategoryInput = (DropDownList)grid1.FooterRow.FindControl(
"ddlCategoryInput");
TextBox txtCostInput = (TextBox)grid1.FooterRow.FindControl("txtCostInput");
int rowInsert
= Product.insertRow(
txtNameInput.Text,
txtProductNumberInput.Text,
txtColorInput.Text,
ddlCategoryInput.SelectedValue.ToString(),
txtCostInput.Text
);
fill_grid();
result.Text = rowInsert.ToString() + " :Row was Inserted Successfully";
}
}
public void grid1_OnRowDeleting(object sender, GridViewDeleteEventArgs e)
{
string product_Id = grid1.DataKeys[e.RowIndex].Values[0].ToString();
int rowdel = Product.RowDelete(product_Id);
result.Text = rowdel.ToString() + " : Row was Deleted.";
fill_grid();
}
public void grid1_OnSorting(object sender, GridViewSortEventArgs e)
{
string sortexp = e.SortExpression;
Session["sortexp"] = sortexp;
fill_grid();
}
public void grid1_OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
grid1.PageIndex = e.NewPageIndex;
fill_grid();
}
}
Event Description
OnRowDataBound event function grid1_OnRowDataBound() is called:
This event is called when the data is bound to the row. Dropdown lists are attached to the data row. If it’s the data control row then the selected option is set equal to the value in the database.
Here the value for the DataKeyNameCollection is used. The second value, the one we set on the DataKeyNames property of the Gridview.
For footer row the dropdown list attached.
OnRowUpdating event function grid1_OnRowUpdating() is called:
This event allows the capture of the update event and data update. Here the row index of the updating row is captured. Then the controls in that row and the values in them is retrieved and pass to update method in the class file.
Note: The Key value for the database is extracted from the DataKeysCollection.
OnRowEditing event function grid1_OnRowEditing() is called:
This event is fired as soon as user clicked the edit button, then the edit index for that row is set that is equal to the row index of the updating row. This value is returned by the event arguments.
OnRowCancelingEdit event function grid1_OnRowCancelingEdit() is called:
This event is fired when the user cancels the row update. Here the selection of the row for editing is removed.
OnRowCommand event function grid1_OnRowCommand() is called:
This event is fired when the user clicks the ‘Add Record’ button, the command fired is ‘Add_Rec’ as it is defiend in Grid, see the footer row. The name is captured from the event arguments. Ihe control in the footer row are located and their valus is extracted and sent for insert function.
OnRowDeleting event function grid1_OnRowDeleting() is called:
This function is called when the record is deleted, the key for the record is fetched from the datakey collection and passed to the delete function.
OnSorting event function grid1_OnSorting() is called:
This event is fired when the sort button is clicked by the user. The sort expression is retrieved from the event arguments and put in the session so that they are retrieved in the fill_grid() method and used for sorting.
OnPageIndexChanging event function grid1_OnPageIndexChanging() is called:
This function is called when user clicks different page number than current page number. The grid’s page index is set to this new page number. While loading grid automatically displayes the requested page.
Whenever user clicks the different page number the whole set of data is retrieved from the database and loaded into the grid. Suppose the complete requested data is 1000 records and the grid page size is 10 records, then only 10 records will be displayed to the user while rest of the records are of no use as because if user clicks the different page number again, whole 1000 records shall be fetched again. This process is not very efficient. However, it is the default working of the paging operation. There are many ways to improve this process which may require another big article which will overshadow the purpose of this article so simple default operation is used here.
Page_Load() this is the first significant event for the user after which he is able to see the page ready for his action. Here we are calling the fill_grid() method.
Fill_Grid(): A small trick is implemented in this function, common operation of getting the DataTable from the database is implemented. Besides that we are checking the sort order of the grid if it is present in the Session directory. If it is not then no sorting order is used, however if the sort order is present then it is used. If new sorting order is selected then that is saved in the session, (check the grid1_OnSorting() function) and is retrieved and used while filling the grid every time. This way the sort order is preserved for the session, and will be the selected sort order even after insert/update/delete operations.
Also in case there is no data returned then grid will not be displayed in this scenario we can use the lable to tell the user about it. However, it is the easiest way out but some logic can be added to allow user to enter data if appropriate, there are many ways to do it those may be business need dpendent and specific to each requirements and are not covered in this article.
Methods used in the class file:
I am using the functions to be static so that the instantiation of the class is not required, we can call the function by the the class_name.function_name() convention.
FetchProducts(): It simply retrives the data from the dabase into the datatable and retunrs this datatable to caller. FetchCategory(): It simply returns the whole category file data into the datatable and returns this datatable to the caller. Update(): It updates the record, it gets all the field values from the calling function as parameters. These values are inserted into the command with the help of SqlParameters. ExecuteNonQuery() is used and the number of affacted rows are returned to the calling function. InsertRow(): It adds a new record in database. It gets all the field values from the calling function as parameters.These values are inserted into the command with the help of SqlParameters. ExecuteNonQuery() is used and the number of affacted rows are returned to the calling function. RowDelete(): It deletes the desired row from the database. The key value is received through the parameters. With the help of SqlParameters and the ExecuteNonQuery() the record is deleted and affacted rows are returned to the calling function.
Interest
- Sorting order is maintained while doing operations – add/ update/ delete.
- The paging in the GridView is interesting to work upon to increase the process efficiency.
History
No updates so far
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
