Recipe 2.11. Sorting Data in Ascending/Descending Order Within a DataGrid Problem You are displaying a table of data in a DataGrid, and you want to let the user sort the data and change the sort order by clicking on the column headers. Solution Enable the DataGrid control's sorting features, and add custom coding to support the sorting along with an indication of the current sort column and order. In the .aspx file, enable the DataGrid control's sorting features. In the code-behind class for the page, use the .NET language of your choice to: Create a data-binding method (bindData in our example) that does the following: Generates the SQL statement required to get the data from the database with an ORDER BY clause based on the value of the sortExpression parameter Fills a DataTable with the ordered data from the database Outputs an image indicating the sort column and sort order beside the current sort column heading
Call the data-binding method from the Page_Load method (to support the initial display of the grid) and from the event that is fired when the user clicks on a column header (the dgBooks_SortCommand event in our example).
Figure 2-11 shows the appearance of a typical DataGrid sorted by title in ascending order, the information in the first column. Examples 2-26, 2-27 through 2-28 show the .aspx and code-behind files for an example application that produces this result. Figure 2-11. DataGrid with ascending/descending sorting output 
Discussion The DataGrid control provides the basic plumbing required to support sorting. It will generate the links for the column headers to raise the SortCommand server-side event when a column header is clicked. The DataGrid does not provide the code required to perform the actual sorting, but very little code is required to complete that job. To enable sorting, the AllowSorting attribute of the DataGrid element must be set to true. In addition, the SortExpression attribute of the BoundColumn element must be set to the expression that will be used in your code to perform the sorting. This would normally be set to the name of the database column displayed in the DataGrid column; however, it can be set to any value required by your code to perform the sorting. Your code will need to perform the actual sorting. For example, the application that we developed for this recipe supports sorting in a centralized manner by using sortExpression and sortOrder parameters with its bindData method. The parameters are used in the ORDER BY clause of the SQL statement. Your code will need to perform the sorting in ascending and descending order as well as visually indicating the sort order, which requires a bit of coding. This is driven by needing to know the current sort column and the sort order to determine what needs to be done when the user clicks a column header. From here on, it's useful to examine our example application to see how we've juggled these needs. We've added an enumeration and several constants to the top of the code-behind class shown in Examples 2-27 (VB) and 2-28 (C#). The enuSortOrder enumeration defines the available sort orders used to store and compare sort order data. The sortExpression and columnTitle arrays are used to define the sort expression and column title for each column in the DataGrid. As their names imply, the VS_CURRENT_SORT_EXPRESSION and VS_CURRENT_SORT_ORDER constants define the names of variables stored in the ViewState to track the current sort expression and order between page submittals. In our example application, the Page_Load method performs two operations. First, the view state variables used to store the current sort expression and sort order (VS_CURRENT_SORT_EXPRESSION and VS_CURRENT_SORT_ORDER) are set to their default values. For this example, the title column is sorted in ascending order by default. Second, the bindData method is called to pass the current sort expression and sort order. Two features of the bindData method are worth special note. First, the SQL statement used to query the database includes an ORDER BY clause that reflects the current sort order. Second, the bindData method loops through each column in the grid, determines which column is the sort column, and marks the sort order for the column. The sort column is determined by comparing the current sort expression passed to the bindData method with the sort expression for each of the columns. The sort expression for the one column that matches the current sort expression is the sort column. After finding the sort column, the sort order is checked to determine whether the ascending or descending image should be output in the header for the sort column. Finally, the header text is set to the title for the column, and when relevant, the HTML image tag used to indicate the sort order is set. For columns that are not the current sort column, the image-related HTML is set to an empty string. When the user clicks a column header in the grid, the dgBooks_SortCommand method is called and that method determines the changes that need to be made prior to rebinding the data. The first step is to get the current sort expression and sort order from the view state, as shown here: 
currentSortExpression = CStr(viewstate(VS_CURRENT_SORT_EXPRESSION)) currentSortOrder = CType(viewstate(VS_CURRENT_SORT_ORDER), enuSortOrder) 
currentSortExpression = (String)(this.ViewState[VS_CURRENT_SORT_EXPRESSION]); currentSortOrder = (enuSortOrder)(this.ViewState[VS_CURRENT_SORT_ORDER]);
After getting the current information, we determine if a column other than the current sort column has been clicked. If so, the clicked column is set as the new sort column, and the sort order is set to ascending. If not, we need to change the sort order of the original column. After determining the sort expression and sort order, the view state is updated to reflect what will be the current information once the page is rendered. Finally, the data is rebound to the grid by calling the bindData method with the new sort information.  | When working with this recipe's sample application, the following code must be placed before the DataBind statement. As a general rule, changes of this sort made to a DataGrid control placed after data binding may not be displayed as intended.
Dim col As DataGridColumn … For index = 0 To dgBooks.Columns.Count - 1 col = dgBooks.Columns(index) 'check to see if this is the sort column If (col.SortExpression = sortExpression) Then 'this is the sort column so determine whether the ascending or 'descending image needs to be included If (sortOrder = enuSortOrder.soAscending) Then collmage = " <img src='/books/1/505/1/html/2/images/sort_ascending.gif' border='0'>" Else collmage = " <img src='/books/1/505/1/html/2/images/sort_descending.gif' border='0'>" End if Else 'This is not the sort column so include no image html collmage = "" End If 'If (col.SortExpression = sortExpression) 'set the title for the column col.HeaderText = columnTitle(index) & collmage Next index
for (index = 0; index < dgBooks.Columns.Count; index++) { col = dgBooks.Columns[index]; // check to see if this is the sort column if (col.SortExpression == sortExpression) { // this is the sort column so determine whether the ascending or // descending image needs to be included if (sortOrder == enuSortOrder.soAscending) { collmage = " <img src='/books/1/505/1/html/2/images/sort_ascending.gif' border='0'>"; } else { collmage = " <img src='/books/1/505/1/html/2/images/sort_descending.gif border='0'>"; } } else { // This is not the sort column so include no image html collmage = ""; } // if (col.SortExpression == sortExpression) // set the title for the column col.HeaderText = columnTitle[index] + collmage; } // for index
|
|
Example 2-26. DataGrid with ascending/descending sorting (.aspx) <%@ Page Language="VB" MasterPageFile="~/ASPNetCookbookVB.master" AutoEventWireup="false" CodeFile="CH02DatagridAscDescSortingVB.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH02DatagridAscDescSortingVB" Title="DataGrid With Ascend/Descend Sorting " %> <asp:Content Runat="server" ContentPlaceHolder> <div align="center" > DataGrid With Ascend/Descend Sorting (VB) </div> <asp:DataGrid runat="server" BorderColor="#000080" BorderWidth="2px" HorizontalAlign="Center" AutoGenerateColumns="False" Width="90%" AllowSorting="True" OnSortCommand="dgBooks_SortCommand" > <HeaderStyle HorizontalAlign="Center" Css /> <ItemStyle css /> <AlternatingItemStyle css /> <Columns> <asp:BoundColumn DataField="Title" SortExpression="Title" /> <asp:BoundColumn DataField="ISBN" ItemStyle-HorizontalAlign="Center" SortExpression="ISBN" /> <asp:BoundColumn DataField="Publisher" ItemStyle-HorizontalAlign="Center" SortExpression="Publisher" /> </Columns> </asp:DataGrid> </asp:Content>
|
Example 2-27. DataGrid with ascending/descending sorting code-behind (.vb) Option Explicit On Option Strict On Imports Microsoft.VisualBasic Imports System.Configuration Imports System.Data Imports System.Data.OleDb Namespace ASPNetCookbook.VBExamples ''' <summary> ''' This class provides the code behind for ''' CH02DatagridAscDescSortingVB.aspx ''' </summary> Partial Class CH02DatagridAscDescSortingVB Inherits System.Web.UI.Page 'the following enumeration is used to define the sort orders Private Enum enuSortOrder soAscending = 0 soDescending = 1 End Enum 'strings to use for the sort expressions and column title 'separate arrays are used to support the sort expression and titles 'being different Private ReadOnly sortExpression() As String = {"Title", "ISBN", "Publisher"} Private ReadOnly columnTitle() As String = {"Title", "ISBN", "Publisher"} 'the names of the variables placed in the viewstate Private Const VS_CURRENT_SORT_EXPRESSION As String = "currentSortExpression" Private Const VS_CURRENT_SORT_ORDER As String = "currentSortOrder" '''****************************************************************** ''' <summary> ''' This routine provides the event handler for the page load event. It ''' is responsible for initializing the controls on the page. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Private Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Load Dim defaultSortExpression As String Dim defaultSortOrder As enuSortOrder If (Not Page.IsPostBack) Then 'sort by title, ascending as the default defaultSortExpression = sortExpression(0) defaultSortOrder = enuSortOrder.soAscending 'store current sort expression and order in the viewstate then 'bind data to the DataGrid ViewState(VS_CURRENT_SORT_EXPRESSION) = defaultSortExpression ViewState(VS_CURRENT_SORT_ORDER) = defaultSortOrder bindData(defaultSortExpression, _ defaultSortOrder) End If End Sub 'Page_Load '''******************************************************************** ''' <summary> ''' This routine provides the event handler for the datagrid sort event. ''' It is responsible re-binding the data to the datagrid by the selected ''' column. ''' </summary> ''' ''' <param name="source">Set to the source of the event</param> ''' <param name="e">Set to the event arguments</param> Protected Sub dgBooks_SortCommand(ByVal source As Object, _ ByVal e As DataGridSortCommandEventArgs) Dim newSortExpression As String Dim currentSortExpression As String Dim currentSortOrder As enuSortOrder 'get the current sort expression and order from the viewstate currentSortExpression = CStr(ViewState(VS_CURRENT_SORT_EXPRESSION)) currentSortOrder = CType(ViewState(VS_CURRENT_SORT_ORDER), enuSortOrder) 'check to see if this is a new column or the sort order 'of the current column needs to be changed. newSortExpression = e.SortExpression If (newSortExpression = currentSortExpression) Then 'sort column is the same so change the sort order If (currentSortOrder = enuSortOrder.soAscending) Then currentSortOrder = enuSortOrder.soDescending Else currentSortOrder = enuSortOrder.soAscending End If Else 'sort column is different so set the new column with ascending 'sort order currentSortExpression = newSortExpression currentSortOrder = enuSortOrder.soAscending End If 'update the view state with the new sort information ViewState(VS_CURRENT_SORT_EXPRESSION) = currentSortExpression ViewState(VS_CURRENT_SORT_ORDER) = currentSortOrder 'rebind the data in the datagrid bindData(currentSortExpression, _ currentSortOrder) End Sub 'dgBooks_SortCommand '''********************************************************************* ''' <summary> ''' This routine queries the database for the data to displayed and binds ''' it to the datagrid ''' </summary> ''' ''' <param name="sortExpression">Set to the sort expression to use for ''' sorting the data</param> ''' <param name="sortOrder">Set to the requried sort order</param> Private Sub bindData(ByVal sortExpression As String, _ ByVal sortOrder As enuSortOrder) Dim dbConn As OleDbConnection = Nothing Dim da As OleDbDataAdapter = Nothing Dim dTable As DataTable = Nothing Dim strConnection As String Dim strSQL As String Dim index As Integer Dim col As DataGridColumn = Nothing Dim colImage As String Dim strSortOrder As String Try 'get the connection string from web.config and open a connection 'to the database strConnection = ConfigurationManager. _ ConnectionStrings("dbConnectionString").ConnectionString dbConn = New OleDbConnection(strConnection) dbConn.Open() 'build the query string and get the data from the database If (sortOrder = enuSortOrder.soAscending) Then strSortOrder = " ASC" Else strSortOrder = " DESC" End If strSQL = "SELECT Title, ISBN, Publisher " & _ "FROM Book " & _ "ORDER BY " & sortExpression & _ strSortOrder da = New OleDbDataAdapter(strSQL, dbConn) dTable = New DataTable da.Fill(dTable) 'loop through the columns in the datagrid updating the heading to 'mark which column is the sort column and the sort order For index = 0 To dgBooks.Columns.Count - 1 col = dgBooks.Columns(index) 'check to see if this is the sort column If (col.SortExpression = sortExpression) Then 'this is the sort column so determine whether the ascending or 'descending image needs to be included If (sortOrder = enuSortOrder.soAscending) Then colImage = " <img src='/books/1/505/1/html/2/images/sort_ascending.gif' border='0'>" Else colImage = " <img src='/books/1/505/1/html/2/images/sort_descending.gif' border='0'>" End If Else 'This is not the sort column so include no image html colImage = "" End If 'If (col.SortExpression = sortExpression) 'set the title for the column col.HeaderText = columnTitle(index) & colImage Next index 'set the source of the data for the datagrid control and bind it dgBooks.DataSource = dTable dgBooks.DataBind() Finally 'cleanup If (Not IsNothing(dbConn)) Then dbConn.Close() End If End Try End Sub 'bindData End Class 'CH02DatagridAscDescSortingVB End Namespace
|
Example 2-28. DataGrid with ascending/descending sorting code-behind (.cs) using System; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Web.UI.WebControls; namespace ASPNetCookbook.CSExamples { /// <summary> /// This class provides the code behind for /// CH02DatagridAscDescSortingCS.aspx /// </summary> public partial class CH02DatagridAscDescSortingCS : System.Web.UI.Page { // the following enumeration is used to define the sort orders private enum enuSortOrder { soAscending = 0, soDescending = 1 } // strings to use for the sort expressions and column title // separate arrays are used to support the sort expression and titles // being different static readonly String[] sortExpression = new String[] { "Title", "ISBN", "Publisher" }; static readonly String[] columnTitle = new String[] { "Title", "ISBN", "Publisher" }; // the names of the variables placed in the viewstate static readonly String VS_CURRENT_SORT_EXPRESSION = "currentSortExpression"; static readonly String VS_CURRENT_SORT_ORDER = "currentSortOrder"; ///**************************************************************** /// <summary> /// This routine provides the event handler for the page load event. It /// is responsible for initializing the controls on the page. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> private void Page_Load(object sender, System.EventArgs e) { String defaultSortExpression; enuSortOrder defaultSortOrder; if (!Page.IsPostBack) { // sort by title, ascending as the default defaultSortExpression = sortExpression[0]; defaultSortOrder = enuSortOrder.soAscending; // bind data to the DataGrid this.ViewState.Add(VS_CURRENT_SORT_EXPRESSION, defaultSortExpression); this.ViewState.Add(VS_CURRENT_SORT_ORDER, defaultSortOrder); bindData(defaultSortExpression, defaultSortOrder); } } // Page_Load ///**************************************************************** /// <summary> /// This routine provides the event handler for the datagrid sort event. /// It is responsible re-binding the data to the datagrid by the selected /// column. /// </summary> /// /// <param name="source">Set to the source of the event</param> /// <param name="e">Set to the event arguments</param> protected void dgBooks_SortCommand(Object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e) { String newSortExpression = null; String currentSortExpression = null; enuSortOrder currentSortOrder; // get the current sort expression and order from the viewstate currentSortExpression = (String)(this.ViewState[VS_CURRENT_SORT_EXPRESSION]); currentSortOrder = (enuSortOrder)(this.ViewState[VS_CURRENT_SORT_ORDER]); // check to see if this is a new column or the sort order // of the current column needs to be changed. newSortExpression = e.SortExpression; if (newSortExpression.Equals(currentSortExpression)) { // sort column is the same so change the sort order if (currentSortOrder == enuSortOrder.soAscending) { currentSortOrder = enuSortOrder.soDescending; } else { currentSortOrder = enuSortOrder.soAscending; } } else { // sort column is different so set the new column with ascending // sort order currentSortExpression = newSortExpression; currentSortOrder = enuSortOrder.soAscending; } // update the view state with the new sort information this.ViewState.Add(VS_CURRENT_SORT_EXPRESSION, currentSortExpression); this.ViewState.Add(VS_CURRENT_SORT_ORDER, currentSortOrder); // rebind the data in the datagrid bindData(currentSortExpression, currentSortOrder); } // dgBooks_SortCommand ///**************************************************************** /// <summary> /// This routine queries the database for the data to displayed and binds /// it to the datagrid /// </summary> /// /// <param name="sortExpression">Set to the sort expression to use for /// sorting the data</param> /// <param name="sortOrder">Set to the requried sort order</param> private void bindData(String sortExpression, enuSortOrder sortOrder) { OleDbConnection dbConn = null; OleDbDataAdapter da = null; DataTable dTable = null; String strConnection = null; String strSQL = null; int index = 0; DataGridColumn col = null; String colImage = null; String strSortOrder = null; try { // get the connection string from web.config and open a connection // to the database strConnection = ConfigurationManager. ConnectionStrings["dbConnectionString"].ConnectionString; dbConn = new OleDbConnection(strConnection); dbConn.Open(); // build the query string and get the data from the database if (sortOrder == enuSortOrder.soAscending) { strSortOrder = " ASC"; } else { strSortOrder = " DESC"; } strSQL = "SELECT Title, ISBN, Publisher " + "FROM Book " + "ORDER BY " + sortExpression + strSortOrder; da = new OleDbDataAdapter(strSQL, dbConn); dTable = new DataTable(); da.Fill(dTable); // loop through the columns in the datagrid updating the heading to // mark which column is the sort column and the sort order for (index = 0; index < dgBooks.Columns.Count; index++) { col = dgBooks.Columns[index]; // check to see if this is the sort column if (col.SortExpression == sortExpression) { // this is the sort column so determine whether the ascending or // descending image needs to be included if (sortOrder == enuSortOrder.soAscending) { colImage = " <img src='/books/1/505/1/html/2/images/sort_ascending.gif' border='0'>"; } else { colImage = " <img src='/books/1/505/1/html/2/images/sort_descending.gif' border='0'>"; } } else { // This is not the sort column so include no image html colImage = ""; } // if (col.SortExpression == sortExpression) // set the title for the column col.HeaderText = columnTitle[index] + colImage; } // for index // set the source of the data for the datagrid control and bind it dgBooks.DataSource = dTable; dgBooks.DataBind(); } // try finally { //clean up if (dbConn != null) { dbConn.Close(); } } // finally } // bindData } // CH02DatagridAscDescSortingCS }
|
|
