Implementing Sorting

that item into our local variable, dataSet. Regardless of how the DataSet is loaded, we bind it to our GridView as we did before.

If you save your work and load the Departments page in your browser, you should see that the page runs exactly as it did previously, except that now the database is accessed only once, the first time the page loads.

Implementing Sorting

To implement sorting functionality, we need to understand a few details of the inner workings of data binding.

Technically, you can’t bind a DataSet to a GridView control, because a DataSet can contain many tables, whereas the GridView control can only handle one set of rows and columns. However, by virtue of the fact that your DataSet has only contained a single DataTable, the GridView control has been smart enough to figure out that what you probably meant was the following:

Visual Basic

' Bind the grid to the DataSet

departmentsGrid.DataSource = dataSet.Tables("Departments") departmentsGrid.DataBind()

C#

// Bind the grid to the DataSet

departmentsGrid.DataSource = dataSet.Tables["Departments"]; departmentsGrid.DataBind();

However, this isn’t technically correct in the strictest sense, either. All of the GridView’s data binding is actually done through DataView objects. Thankfully, each DataTable has a DefaultView property, which the GridView will automatically use whenever you bind it to a DataTable. So, the following code listings have the same functionality as those we saw above:

Visual Basic

' Bind the grid to the DataView departmentsGrid.DataSource = _

dataSet.Tables("Departments").DefaultView departmentsGrid.DataBind()

C#

// Bind the grid to the DataView departmentsGrid.DataSource =

509

Chapter 12: Advanced Data Access

dataSet.Tables["Departments"].DefaultView;

departmentsGrid.DataBind();

DefaultView does not Apply when Binding to a DataSet

It’s interesting to note that if you bind directly to a DataSet that contains only one table, that table’s DefaultView will not be used; the GridView will generate a separate DataView itself.

DataViews represent a customized view of a DataSet for sorting, filtering, searching, editing, and navigation. When binding a GridView directly to a DataTable, the DefaultView property, which is a DataView object, is accessed automatically for us. However, if we want to enable sorting, we need to access the DataView and set its sorting parameters.

The first step to enabling sorting is to set the AllowSorting property to True. When we do that, the grid’s column headings become hyperlinks. Before we make those hyperlinks work, we need to handle the grid’s Sorting event, where we teach the grid what to do when those links are clicked.

Set the AllowSorting property of the GridView control in Departments.aspx to True, then use the designer to generate the handler for the GridView’s Sorting event. Then, complete the code as shown:

Visual Basic

File: Departments.aspx.vb (excerpt)

Protected Sub departmentsGrid_Sorting(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) _ Handles departmentsGrid.Sorting

'Retrieve the name of the clicked column (sort expression) Dim sortExpression As String = e.SortExpression

'Decide and save the new sort direction

If (sortExpression = gridSortExpression) Then

If gridSortDirection = SortDirection.Ascending Then gridSortDirection = SortDirection.Descending

Else

gridSortDirection = SortDirection.Ascending End If

Else

gridSortDirection = WebControls.SortDirection.Ascending End If

'Save the new sort expression gridSortExpression = sortExpression

'Rebind the grid to its data source BindGrid()

End Sub

510

Implementing Sorting

Private Property gridSortDirection() Get

' Initial state is Ascending

If (ViewState("GridSortDirection") Is Nothing) Then

	ViewState("GridSortDirection") = SortDirection.Ascending
End If
'	Return the state
Return ViewState("GridSortDirection")
End	Get
Set(ByVal value)
ViewState("GridSortDirection") = value
End	Set
End Property
Private Property gridSortExpression()
Get
'	Initial sort expression is DepartmentID
If (ViewState("GridSortExpression") Is Nothing) Then
	ViewState("GridSortExpression") = "DepartmentID"
End If
'	Return the sort expression
Return ViewState("GridSortExpression")
End	Get
Set(ByVal value)
ViewState("GridSortExpression") = value
End	Set
End Property
C#	File: Departments.aspx.cs (excerpt)

protected void departmentsGrid_Sorting(object sender, GridViewSortEventArgs e)

{

//Retrieve the name of the clicked column (sort expression) string sortExpression = e.SortExpression;

//Decide and save the new sort direction

if (sortExpression == gridSortExpression)

{

if(gridSortDirection == SortDirection.Ascending)

{

gridSortDirection = SortDirection.Descending;

}

else

{

gridSortDirection = SortDirection.Ascending;

}

}

else

511

Chapter 12: Advanced Data Access

{

gridSortDirection = SortDirection.Ascending;

}

//Save the new sort expression gridSortExpression = sortExpression;

//Rebind the grid to its data source BindGrid();

}

private SortDirection gridSortDirection

{

get

{

// Initial state is Ascending

if (ViewState["GridSortDirection"] == null)

{

ViewState["GridSortDirection"] = SortDirection.Ascending;

}

// Return the state

return (SortDirection) ViewState["GridSortDirection"];

}

set

{

ViewState["GridSortDirection"] = value;

}

}

private string gridSortExpression

{

get

{

//Initial sort expression is DepartmentID if (ViewState["GridSortExpression"] == null)

{

ViewState["GridSortExpression"] = "DepartmentID";

}

//Return the sort expression

return (string) ViewState["GridSortExpression"];

}

set

{

ViewState["GridSortExpression"] = value;

}

}

512

Implementing Sorting

Properties

We haven’t really discussed the task of defining your own properties since Chapter 4, so now might be a good time for a quick refresher. By now, you should be fairly comfortable with the idea that each of your web forms is its own class, and inherits a great deal of functionality from its parent class, Page. You’ve already dealt with quite a few of that class’s features, such as its Load event and its IsPostBack property.

You can define for your class properties that can be read-only, write-only, or are able to be read and written to. When you read data from a property, its Get code is executed. Most of the time, this code will be quite simple, but it can be as complex as you choose to make it. In the same way, when a value is written to a property, its Set code is executed, which can also be quite complex if you choose to make it so.

Finally, update the BindGrid method to apply the sorting:

Visual Basic File: Departments.aspx.vb (excerpt)

Private Sub BindGrid() ' Define data objects

Dim conn As SqlConnection Dim dataSet As New DataSet

Dim adapter As SqlDataAdapter

If ViewState("DepartmentsDataSet") Is Nothing Then

'Read the connection string from Web.config Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _ "Dorknozzle").ConnectionString

'Initialize connection

conn = New SqlConnection(connectionString) ' Create adapter

adapter = New SqlDataAdapter( _

"SELECT DepartmentID, Department FROM Departments", _ conn)

'Fill the DataSet adapter.Fill(dataSet, "Departments")

'Store the DataSet in view state ViewState("DepartmentsDataSet") = dataSet

Else

dataSet = ViewState("DepartmentsDataSet")

End If

'Prepare the sort expression using the gridSortDirection and

'gridSortExpression properties

Dim sortExpression As String

If gridSortDirection = SortDirection.Ascending Then

513

Chapter 12: Advanced Data Access

sortExpression = gridSortExpression & " ASC" Else

sortExpression = gridSortExpression & " DESC" End If

' Sort the data

	dataSet.Tables("Departments").DefaultView.Sort = sortExpression
	' Bind the grid to the DataSet
	departmentsGrid.DataSource = _
	dataSet.Tables("Departments").DefaultView
	departmentsGrid.DataBind()
End Sub
C#	File: Departments.aspx.cs (excerpt)

private void BindGrid()

{

// Define data objects SqlConnection conn;

DataSet dataSet = new DataSet(); SqlDataAdapter adapter; if(ViewState["DepartmentsDataSet"] == null)

{

//Read the connection string from Web.config string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

//Initialize connection

conn = new SqlConnection(connectionString); // Create adapter

adapter = new SqlDataAdapter(

"SELECT DepartmentID, Department FROM Departments", conn);

//Fill the DataSet adapter.Fill(dataSet, "Departments");

//Store the DataSet in view state ViewState["DepartmentsDataSet"] = dataSet;

}

else

{

dataSet = (DataSet)ViewState["DepartmentsDataSet"];

}

//Prepare the sort expression using the gridSortDirection and

//gridSortExpression properties

string sortExpression;

if(gridSortDirection == SortDirection.Ascending)

{

sortExpression = gridSortExpression + " ASC";

514

Implementing Sorting

}

else

{

sortExpression = gridSortExpression + " DESC";

}

// Sort the data

dataSet.Tables["Departments"].DefaultView.Sort = sortExpression;

// Bind the grid to the DataSet departmentsGrid.DataSource =

dataSet.Tables["Departments"].DefaultView;

departmentsGrid.DataBind();

}

Execute the project again, and test that sorting by column works as shown in Figure 12.26.

Figure 12.26. Sorting Dorknozzle’s departments

We’ve written a lot of code here! Let’s take a look at how it works.

In order to sort the data in the grid, all we need to do is set the Sort property of the view we’re displaying to ColumnNameSortOrder, where ColumnName is, of course, the name of the column we’re sorting, and SortOrder is either ASC (for

515

Chapter 12: Advanced Data Access

ascending) or DESC (for descending). So, if you were sorting the DepartmentID column, the Sort property would need to be set to DepartmentID ASC or Department DESC.

This property must be set before the data binding is performed, as is shown in the following code, which will sort the data by DepartmentID in descending numeric order:

Visual Basic

dataTable.DefaultView.Sort = "DepartmentID DESC" departmentsGrid.DataSource = dataTable.DefaultView departmentsGrid.DataBind()

C#

dataTable.DefaultView.Sort = "Department DESC"; departmentsGrid.DataSource = dataTable.DefaultView; departmentsGrid.DataBind();

It’s a pretty simple task to sort a DataView in code like this, but if we want to let users sort the data on the basis of any column, in any direction, things get a little bit more complicated. In this case, we need to remember the previous sort method between requests.

In order to be truly user-friendly, our grid should behave like this:

The first time a column header is clicked, the grid should sort the data in ascending order, based on that column.

When the same column header is clicked multiple times, the grid should alternate between sorting the data in that column in ascending and descending modes.

When a column heading is clicked, the grid’s Sorting event is fired. In our case, the Sorting event handler (which we’ll look at in a moment) saves the details of the sort column and direction in two properties:

gridSortExpression retains the name of the column on which we’re sorting the data (such as Department)

gridSortDirection can be either SortDirection.Ascending or SortDirection.Descending

We create a sorting expression using these properties in BindGrid:

516

Implementing Sorting

Visual Basic

File: Departments.aspx.vb (excerpt)

'Prepare the sort expression using the gridSortDirection and

'gridSortExpression properties

Dim sortExpression	As String
If gridSortDirection = SortDirection.Ascending Then
sortExpression =	gridSortExpression & " ASC"
Else
sortExpression =	gridSortExpression & " DESC"
End If
C#	File: Departments.aspx.cs (excerpt)

//Prepare the sort expression using the gridSortDirection and

//gridSortExpression properties

string sortExpression;

if(gridSortDirection == SortDirection.Ascending)

{

sortExpression = gridSortExpression + " ASC";

}

else

{

sortExpression = gridSortExpression + " DESC";

}

In order to implement the sorting functionality as explained above, we need to remember between client requests which column is being sorted, and whether it’s being sorted in ascending or descending order. That’s what the properties gridSortExpression and gridSortDirection do:

Visual Basic		File: Departments.aspx.vb (excerpt)
Private Property gridSortDirection()
Get
'	Initial state is Ascending
If (ViewState("GridSortDirection") Is Nothing) Then
	ViewState("GridSortDirection") = SortDirection.Ascending
End If
'	Return the state
Return ViewState("GridSortDirection")
End	Get
Set(ByVal value)
ViewState("GridSortDirection") = value
End	Set
End Property
Private Property gridSortExpression()
Get
'	Initial sort expression is DepartmentID

517

Chapter 12: Advanced Data Access

If (ViewState("GridSortExpression") Is Nothing) Then ViewState("GridSortExpression") = "DepartmentID"

End If

' Return the sort expression

Return ViewState("GridSortExpression")

End Get
Set(ByVal value)
ViewState("GridSortExpression") = value
End Set
End Property
C#	File: Departments.aspx.cs (excerpt)

private SortDirection gridSortDirection

{

get

{

// Initial state is Ascending

if (ViewState["GridSortDirection"] == null)

{

ViewState["GridSortDirection"] = SortDirection.Ascending;

}

// Return the state

return (SortDirection) ViewState["GridSortDirection"];

}

set

{

ViewState["GridSortDirection"] = value;

}

}

private string gridSortExpression

{

get

{

//Initial sort expression is DepartmentID if (ViewState["GridSortExpression"] == null)

{

ViewState["GridSortExpression"] = "DepartmentID";

}

//Return the sort expression

return (string) ViewState["GridSortExpression"];

}

set

{

ViewState["GridSortExpression"] = value;

}

}

518

Implementing Sorting

Here, we use the ViewState collection to store information about which column is being sorted, and the direction in which it’s being sorted.

When the Sorting event handler fires, we set the gridSortExpression and gridSortDirection properties. The method starts by retrieving the name of the clicked column:

Visual Basic File: Departments.aspx.vb (excerpt)

Protected Sub departmentsGrid_Sorting(ByVal sender As Object, _

ByVal e	As System.Web.UI.WebControls.GridViewSortEventArgs) _
Handles	departmentsGrid.Sorting
' Retrieve the name of the clicked column (sort expression)
Dim sortExpression As String = e.SortExpression
C#	File: Departments.aspx.cs (excerpt)

protected void departmentsGrid_Sorting(object sender, GridViewSortEventArgs e)

{

// Retrieve the name of the clicked column (sort expression) string sortExpression = e.SortExpression;

Next, we check whether the previously-clicked column is the same as the newlyclicked column. If it is, we need to toggle the sorting direction. Otherwise, we set the sort direction to ascending:

Visual Basic File: Departments.aspx.vb (excerpt)

' Decide and save the new sort direction

If (sortExpression = gridSortExpression) Then

If gridSortDirection = SortDirection.Ascending Then

gridSortDirection	= SortDirection.Descending
Else
gridSortDirection	= SortDirection.Ascending
End If
Else
gridSortDirection =	WebControls.SortDirection.Ascending
End If
C#	File: Departments.aspx.cs (excerpt)

// Decide and save the new sort direction if (sortExpression == gridSortExpression)

{

if(gridSortDirection == SortDirection.Ascending)

{

gridSortDirection = SortDirection.Descending;

}

519