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.


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.227.102.50