Working with GridView without using Data Source Controls

There are many 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 tutorial 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:

BoundField	HeaderText	DataField	ReadOnly
Employee ID	Employee ID	EmployeeID	true
First Name	First Name	FirstName	false
Last Name	Last Name	LastName	false

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.

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

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

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.

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.

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

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();
}

The FileExists() method simply checks if the requested file exists in the WebForms table. Notice that the FileExists() method receives the virtual path as a parameter.

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.