Sorting Columns
The DataGrid offers both built-in and custom paging solutions. It also offers simple solutions for enabling column sorting in a rendered table. Using the built-in sorting features of the DataGrid , you easily can allow your Web site visitors to click on column headings to re- sort the DataGrid .
The sorting functionality requires you to handle the sorting of the data, but it handles the tracking of which column to sort by. Much like paging, sorting is enabled by setting the DataGrid.AllowSorting property to True . This tells the DataGrid that you want the column headings to be links that the user can click on to re-sort the DataGrid .
Once AllowSorting is set to True , you need to specify the OnSortCommand event handler. In the event handler, you'll capture the name of the column to sort by and re-create the data source sorted as necessary. Since you must re-create the data source on each post-back to the page, you can simply alter the parameters of the SQL statement that retrieves the data. Listing 6.10 shows an ASP.NET code behind and Web Form with sorting implemented.
Listing 6.10 Column Sorting with the DataGrid
[VB] 01: Imports System 02: Imports System.Web 03: Imports System.Web.UI 04: Imports System.Web.UI.WebControls 05: Imports System.Data 06: Imports System.Data.SqlClient 07: 08: Public Class Listing0610 : Inherits Page 09: 10: Protected myDataGrid As DataGrid 11: 12: Protected SqlStmt As String = "SELECT CompanyName, ContactName, ContactTitle, Phone, 
Fax FROM Customers" 13: 14: Sub Page_Load(Source As Object, E As EventArgs) 15: If Not Page.IsPostBack Then 16: BindData() 17: End If 18: End Sub 19: 20: Sub BindData() 21: Dim myConnection As SqlConnection = New SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=;") 22: Dim myCommand As SqlCommand = New SqlCommand(SqlStmt, myConnection) 23: Dim myReader As SqlDataReader = Nothing 24: 25: Try 26: myConnection.Open() 27: myReader = myCommand.ExecuteReader() 28: myDataGrid.DataSource = myReader 29: myDataGrid.DataBind() 30: 31: Finally 32: myConnection.Close() 33: End Try 34: 35: End Sub 36: 37: Sub SortCommand_OnClick(Source As Object, E As DataGridSortCommandEventArgs) 38: SqlStmt = SqlStmt & " ORDER BY " & E.SortExpression 39: BindData() 40: End Sub 41: 42: End Class [Code Behind C# - 06.10.cs] 01: using System; 02: using System.Web; 03: using System.Web.UI; 04: using System.Web.UI.WebControls; 05: using System.Data; 06: using System.Data.SqlClient; 07: 08: public class Listing0610 : Page{ 09: 10: protected DataGrid myDataGrid; 11: 12: protected string SqlStmt = "SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM Customers"; 13: 14: protected void Page_Load(Object sender, EventArgs e){ 15: if (!Page.IsPostBack){ 16: BindData(); 17: } 18: } 19: 20: protected void BindData(){ 21: SqlConnection myConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=;"); 22: SqlCommand myCommand = new SqlCommand(SqlStmt, myConnection); 23: SqlDataReader myReader = null; 24: 25: try{ 26: myConnection.Open(); 27: myReader = myCommand.ExecuteReader(); 28: myDataGrid.DataSource = myReader; 29: myDataGrid.DataBind(); 30: } 31: finally{ 32: myConnection.Close(); 33: } 34: 35: } 36: 37: protected void SortCommand_OnClick(Object sender, DataGridSortCommandEventArgs e){ 38: SqlStmt = SqlStmt + " ORDER BY " + e.SortExpression; 39: BindData(); 40: } 41: 42: } [Web Form VB] 01: <%@ Page Inherits="Listing0610" Src="06.10.vb" %> [Web Form C#] 01: <%@ Page Inherits="Listing0610" Src="06.10.cs" %> [Web Form VB & C#] 02: <html> 03: <head> 04: <style ref="stylesheet" type="text/css"> 05: .tableItem { font: x-small Verdana, Arial, sans-serif;} 06: .tableHeader { font: bold small Arial; color:#663300; background-color:#CCCC66;} 07: .alternatingItem { font: x-small Verdana, Arial, sans-serif; background-color: #FFFFCC;} 08: A { color:#663300} 09: A:hover { color:red} 10: .pageLinks { font: bold x-small Verdana, Arial, sans-serif;} 11: </style> 12: </head> 13: <body> 14: <form runat="server" method="post"> 15: <asp:DataGrid runat="server" id="myDataGrid" 16: Width="740" 17: Cellpadding="4" 18: Cellspacing="0" 19: Gridlines="Horizontal" 20: HorizontalAlign="Center" 21: HeaderStyle-CssClass="tableHeader" 22: ItemStyle-CssClass="tableItem" 23: AlternatingItemStyle-CssClass="alternatingItem" 24: AllowSorting="True" 25: OnSortCommand="SortCommand_OnClick" 26: /> 27: </form> 28: </body> 29: </html>
In Listing 6.10, you implement column sorting with the DataGrid . This requires a few code modifications from the previous examples. On line 12 of the code behind class, you create a page-level variable for the SQL statement ( SqlStmt ). This allows you to set the variable value in any method in the Web Form code behind. That way you can set an initial SQL statement on the first request of the page and modify it to sort by a column when its column heading is clicked.
On lines 37 “40, you create the SortCommand_OnClick() event handler. In this event handler, you add an ORDER BY clause to the SQL statement. The parameter to order the data by is set using the SortExpression property of the DataGridSortCommandEvenArgs ( e.SortExpression ). Each column in a DataGrid can specify a SortExpression , which is used to identify how to sort the results when a sort link is clicked. Since none of the columns have a specified SortExpression property, it defaults to the column name. Once you've modified the SQL statement with an ORDER BY clause, you call to the BindData() method to retrieve the data from the database and bind it to the DataGrid . The new page is rendered as a table sorted by the column that was clicked on. Figure 6.9 shows the rendered page from Listing 6.10.
Figure 6.9. The DataGrid implements column sorting by capturing the SortExpression and binding to the data source again.
One of the truly great things about the DataGrid 's advanced functionality is that none of the features, such as paging and sorting, are mutually exclusive. The advance features of the DataGrid can be used together. To implement both paging and sorting on a DataGrid , you need to make sure that the event handlers for both features are in place and the correct properties of the DataGrid are set. The only difference from what you've done so far is that the SQL statement needs to be maintained from one page request to the next . If you sort a DataGrid by a column and then navigate to the next page of data, you want to ensure that the sorting remains consistent.
To implement both paging and sorting, you'll write code similar to the examples in this section and the "Paging with the DataGrid" section earlier in this chapter. You'll need to add a hidden field for the SQL statement ORDER BY clause. A Label control with the Visible property set to False is ideal for this. Using a Label control ensures the value of the control (its state) is maintained by ViewState from one page request to the next. Additionally, when a Label control's Visible property is set to False , no HTML for that control is rendered to the client. This means your SQL statement ORDER BY clause is completely hidden from the client. Listing 6.11 shows a Web Form that implements both paging and sorting.
Listing 6.11 Using Paging and Sorting on a DataGrid
[VB] 01: Imports System 02: Imports System.Web 03: Imports System.Web.UI 04: Imports System.Web.UI.WebControls 05: Imports System.Data 06: Imports System.Data.SqlClient 07: 08: Public Class Listing0611 : Inherits Page 09: 10: Protected myDataGrid As DataGrid 11: Protected SqlStatement As Label 12: 13: Protected SqlStmt As String = "SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM Customers" 14: 15: Sub Page_Load(Source As Object, E As EventArgs) 16: If Not Page.IsPostBack Then 17: BindData() 18: End If 19: End Sub 20: 21: Sub BindData() 22: SqlStmt = SqlStmt + SqlStatement.Text 23: Dim myDataSet As DataSet = new DataSet() 24: Dim myDataAdapter As SqlDataAdapter = new SqlDataAdapter(SqlStmt, "server=localhost;database=Northwind;uid=sa;pwd=;") 25: myDataAdapter.Fill(myDataSet, "Customers") 26: 27: myDataGrid.DataSource = myDataSet.Tables("Customers") 28: myDataGrid.DataBind() 29: End Sub 30: 31: Sub SortCommand_OnClick(Source As Object, E As DataGridSortCommandEventArgs) 32: SqlStatement.Text = " ORDER BY " + e.SortExpression 33: BindData() 34: End Sub 35: 36: Sub PageIndexChanged_OnClick(Source As Object, E As DataGridPageChangedEventArgs) 37: myDataGrid.CurrentPageIndex = e.NewPageIndex 38: BindData() 39: End Sub 40: 41: End Class [Code Behind C# - 06.11.cs] 01: using System; 02: using System.Web; 03: using System.Web.UI; 04: using System.Web.UI.WebControls; 05: using System.Data; 06: using System.Data.SqlClient; 07: 08: public class Listing0611 : Page{ 09: 10: protected DataGrid myDataGrid; 11: protected Label SqlStatement; 12: 13: protected string SqlStmt = "SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM Customers"; 14: 15: protected void Page_Load(Object sender, EventArgs e){ 16: if (!Page.IsPostBack){ 17: BindData(); 18: } 19: } 20: 21: protected void BindData(){ 22: SqlStmt = SqlStmt + SqlStatement.Text; 23: DataSet myDataSet = new DataSet(); 24: SqlDataAdapter myDataAdapter = new SqlDataAdapter(SqlStmt, "server=localhost;database=Northwind;uid=sa;pwd=;"); 25: myDataAdapter.Fill(myDataSet, "Customers"); 26: 27: myDataGrid.DataSource = myDataSet.Tables["Customers"]; 28: myDataGrid.DataBind(); 29: } 30: 31: protected void SortCommand_OnClick(Object sender, DataGridSortCommandEventArgs e){ 32: SqlStatement.Text = " ORDER BY " + e.SortExpression; 33: BindData(); 34: } 35: 36: protected void PageIndexChanged_OnClick(Object sender, DataGridPageChangedEventArgs e){ 37: myDataGrid.CurrentPageIndex = e.NewPageIndex; 38: BindData(); 39: } 40: 41: } [Web Form VB] 01: <%@ Page Inherits="Listing0611" Src="06.11.vb" %> [Web Form VB] 01: <%@ Page Inherits="Listing0611" Src="06.11.cs" %> [Web Form VB & C#] 02: <html> 03: <head> 04: <style ref="stylesheet" type="text/css"> 05: .tableItem { font: x-small Verdana, Arial, sans-serif;} 06: .tableHeader { font: bold small Arial; color:#663300; background-color:#CCCC66;} 07: .alternatingItem { font: x-small Verdana, Arial, sans-serif; background-color: #FFFFCC;} 08: A { color:#663300} 09: A:hover { color:red} 10: .pageLinks { font: bold x-small Verdana, Arial, sans-serif;} 11: </style> 12: </head> 13: <body> 14: <form runat="server" method="post"> 15: <asp:Label id="SqlStatement" runat="server" Visible="False" /> 16: <asp:DataGrid runat="server" id="myDataGrid" 17: Width="740" 18: Cellpadding="4" 19: Cellspacing="0" 20: Gridlines="Horizontal" 21: HorizontalAlign="Center" 22: HeaderStyle-CssClass="tableHeader" 23: ItemStyle-CssClass="tableItem" 24: AlternatingItemStyle-CssClass="alternatingItem" 25: AllowPaging="True" 26: OnPageIndexChanged="PageIndexChanged_OnClick" 27: PageSize="10" 28: PagerStyle-Mode="NumericPages" 29: PagerStyle-HorizontalAlign="Right" 30: PagerStyle-CssClass="pageLinks" 31: AllowSorting="True" 32: OnSortCommand="SortCommand_OnClick" 33: /> 34: </form> 35: </body> 36: </html>
In Listing 6.11, you create a Web Form that implements both paging and sorting. This is done by adding event handlers for both the OnPageIndexChanged and OnSortCommand events and then setting the paging and sorting properties of the DataGrid . The code in Listing 6.11 is similar to the code in previous examples, using the event handler for paging from Listing 6.5 and the event handler for sorting from Listing 6.10. The only real difference is that the ORDER BY clause of the SQL statement is maintained across page requests by setting it as the Text property of an invisible Label control (line 32 of the code behind and line 15 of the Web Form). On each call to BindData() , the ORDER BY clause from the Label control is appended to the SqlStmt variable, which is used to retrieve the data. If the user sorts the table by a column and then clicks on a page-navigation link, the ORDER BY clause is maintained by ViewState .
The result is that when the user clicks on another column to sort by, the page number is retained and the DataGrid is re-sorted. Figure 6.10 shows the rendered output of Listing 6.11 after navigating to page 3 and sorting on the ContactTitle column.
Figure 6.10. Both paging and sorting can be used on a DataGrid simultaneously .
