Working with GridView without using Data Source Controls

Introduction

There are many articles and tutorials around that illustrate the use of GridView control along with the new Data Source Controls. When attached with a Data Source control GridView performs tasks such as paging, sorting and editing automatically i.e. without writing any code. However, at times you require to bind the GridView with a DataView or DataTable directly. Fortunately GridView can also be used without attaching with the Data Source Controls. This calls for some extra coding from developer's end. If you are familiar with DataGrid control of ASP.NET 1.x then you will find the coding similar. In this article we will see how paging, sorting and editing can be implemented in a GridView control that is bound with a DataView (or DataTable) directly.

Example - Employee listing

As an example we are going to use Employees table of the Northwind database. In order to develop a test web form create a new web site in VS.NET 2005. Drag and drop a GridView control on the web form. Add three BoundFields and a CommandField to the GridView with the help of Fields dialog (see below).

Set various properties of the BoundFields as shown in the following table:

Go in the code behind of the web form and add the following code in the Page_Load event:

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

Here, we call BindGrid() method if the web form is being served as a result of first request. The BindGrid() method is our own function that we will create later on. The method essentially binds the GridView with a DataView. The DataView supplies all the records from the Employees table.

Implementing Paging

In order to implement paging you need to set AllowPaging property of the GridView control to True. Then set PageSize property to 3. There are two events of GridView that are related to paging. They are:

• PageIndexChanging
• PageIndexChanged

The former event is raised before the current page changes and the later is raised after the page has changed. The former allows you to cancel the operation.

Write the following code in the PageIndexChanging event of the GridView.

protected void GridView1_PageIndexChanging
(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
BindGrid();
}

The PageIndexChanging event handler receives an event argument of type GridViewPageEventArgs. This argument provides the new page number that the user has selected via a property called NewPageIndex. We set PageIndex property of the GridView to this new page number and call the BindGrid() method again. This will display the corresponding records in the GridView. The following figure shows the GridView after implementing paging.

Implement Sorting

Implementing sorting requires a bit of more work. First of all, you need to set AllowSorting property of the GridView to True. Further, you need to set SortExpression property of the BoundFields to the name of the column on which you would like to sort the grid. Once you set these properties you will find that the column headers of all the columns for  which you specified SortExpression property appear as clickable link buttons. Clicking on the link buttons raise two events:

• Sorting
• Sorted

Just like paging events these events follow pre and post pattern. The Sorting event allows you to cancel the operation also.

The Sorting event handler receives an event argument of type GridViewSortEventArgs. The GridViewSortEventArgs class has three important properties. The SortExpression property supplies the sort expression that you specified earlier for the column being sorted. The SortDirection property specifies the direction of sorting i.e. ascending or descending. This property is not of much when you are sorting the grid on your own. However, when coupled with data source controls the GridView can toggle the sorting direction automatically for you and you can get/set the direction using this property. The Cancel property allows you to cancel the sorting operation.

Since we are not using data source controls we need to handle the toggling of the sort direction on our own. We do this by storing a ViewState variable. Add the following code in the Sorting event handler.

protected void GridView1_Sorting(object sender, 
GridViewSortEventArgs e)
{
ViewState["sortexpression"] = e.SortExpression;

if (ViewState["sortdirection"] == null)
{
ViewState["sortdirection"] = "asc";
}
else
{
if (ViewState["sortdirection"].ToString() == "asc")
{
ViewState["sortdirection"] = "desc";
}
else
{
ViewState["sortdirection"] = "asc";
}
}
BindGrid();
}

Here, we store the value of SortExpression property into a VeiwState variable called sortexpression. We need to access this variable from BindGrid() method so that our sorting will work correctly under all the circumstances. Similarly we set a ViewState variable called sortdirection. This variable stores the direction of sorting i.e. asc or desc. Note how we are toggling the direction. Finally, we call BindGrid() method which will bind sorted version of the DataView with the grid.

The following figure shows the GridView after implementing sorting.

Implement Editing

In order to edit records displayed in the GridView, we need to add a CommandField to the GridView. By default this field renders a link button titled Edit. Once we click on edit the GridView raises an event called RowEditing and the Edit link button changes to Update and Cancel link buttons. Clicking on Update link button raises RowUpdating event whereas clicking on the Cancel link button raises RowCancelingEdit event. We need to write our own code to update the new values back to the database in the RowUpdating event.

Add the following code in the RowEditing event handler:

protected void GridView1_RowEditing
(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindGrid();
}

The RowEditing event handler receives an event argument of type GridViewEditEventArgs. The GridViewEditEventArgs class has a property called NewEditIndex that tells us the row number being edited. We need to set the EditIndex property of the GridView to this new edit index. This will cause the GridView to enter in edit mode (see below).

Note that the EmployeeID column is not editable because we have marked it as ReadOnly.

Now add the following code in the RowCancelingEdit event handler.

protected void GridView1_RowCancelingEdit
(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindGrid();
}

Here, we set the EditIndex property of GridView to -1 indicating that we intend to cancel the edit operation.

Finally, we will write code for handling the RowUpdating event.

protected void GridView1_RowUpdating
(object sender, GridViewUpdateEventArgs e)
{
int empid;
string fname, lname;
empid = int.Parse(GridView1.Rows[e.RowIndex].
Cells[0].Text);
fname = ((TextBox)GridView1.Rows[e.RowIndex].
Cells[1].Controls[0]).Text;
lname = ((TextBox)GridView1.Rows[e.RowIndex].
Cells[2].Controls[0]).Text;

SqlConnection cnn = new SqlConnection(@"data source=
.\sqlexpress;initial catalog=northwind;integrated 
security=true");
cnn.Open();
SqlCommand cmd = new SqlCommand("update employees set 
firstname=@fname,lastname=@lname where employeeid=@empid",
 cnn);
cmd.Parameters.Add(new SqlParameter("@fname",fname));
cmd.Parameters.Add(new SqlParameter("@lname", lname));
cmd.Parameters.Add(new SqlParameter("@empid", empid));
cmd.ExecuteNonQuery();
cnn.Close();

GridView1.EditIndex = -1;
BindGrid();
}

Here, we first retrieve the new values entered in the textboxes. The RowUpdating event handler receives an event argument of type GridViewUpdateEventArgs. The GridViewUpdateEventArgs class has a property called RowIndex that tells us the row number being updated. We retrieve the new values using the Rows collection of the GridView. Note how the Cells and Controls collections are used. The Cells collection contains all the columns of the current row whereas Controls collection contains all the controls from a specific column. The Controls collection stores all the controls as generic Control type. Hence we need to type cast the controls to TextBox class. Then we create a connection, command and parameters and execute an UPDATE query against the database. Once the updating operation is over we set EditIndex property of the GridView to -1 and bind the grid again.

The BindGrid() method

Throughout our code we have used a method called BindGrid(). It's time to unveil the BindGrid() method now.

private void BindGrid()
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter
("select * from employees", @"data source=
.\sqlexpress;initial catalog=northwind;
integrated security=true");
da.Fill(ds,"employees");
DataView dv = ds.Tables[0].DefaultView;

if (ViewState["sortexpression"] != null)
{
dv.Sort = ViewState["sortexpression"].ToString() 
+ " " + ViewState["sortdirection"].ToString();
}

GridView1.DataSource=dv;
GridView1.DataBind();
}

Here, we fill a DataSet with all the records from Employees table. Then we create a DataView based on the employees DataTable. Then we sort the DataView using the Sort property. The column on which we wish to sort the DataView is obtained from the  sortexpression ViewState variable. While sorting we also make use of sortdirection ViewState variable. Recollect that we set these two ViewState variables in the Sorting event handler. Finally, we set the DataSource property of the GridView to the DataView and then call its DataBind() method.

Summary

GridView can be bound with a DataView (or DataTable) directly without using data source controls. We can implement the same paging, sorting and editing features. However, we need to handle various events ourselves and do some extra work so that all the features work as expected.