Recipe 1.13 Combining Sorting and Paging in a DataGrid

     

1.13.1 Problem

You are implementing a DataGrid with both sorting and pagination, and you are having trouble making the two features work together.

1.13.2 Solution

Enable the sorting features of the DataGrid control, and add custom code to support the sorting along with an indication of the current sort column and order (see Recipe 1.12 for details). Next, with pagination enabled, add a small amount of custom code to track the sort column and sort order so that they can be maintained between client round trips and used any time rebinding is required. Figure 1-16 shows a typical DataGrid with this solution implemented. Example 1-36 through Example 1-38 show the .aspx file and code-behind files for an application that produces this output.

Figure 1-16. Combining sorting and paging in a DataGrid output
figs/ancb_0116.gif

1.13.3 Discussion

Getting both sorting and paging to work at the same time is a notorious problem with a DataGrid . The key to making it all work is to track the sort column and sort order so that they can be used any time rebinding is required, whether because of a page change or a sort command. Likewise, it is useful to put the sort column and sort order data in the view state so that they are properly maintained between client round trips.

The DataGrid provides the basic plumbing for sorting and paging the data displayed in the grid. The DataGrid also provides a property ( CurrentPageIndex ) that is always available to indicate which page is to be displayed. Unfortunately, the DataGrid provides no information regarding the sort column or the sort order, forcing you, as a programmer, to track this information outside of the DataGrid so it will be available when performing pagination operations.

The application we've developed for this recipe should give you a good idea of how to handle sorting and paging simultaneously . It tracks the sort column and the sort order so that the proper data can be bound to the DataGrid any time rebinding is required ”for example, when the user clicks on a row header to resort a column or selects a page from the DataGrid control's built-in navigation control. Refer to Recipe 1.9 and Recipe 1.12 for more detailed discussions of the various nuances of this recipe.

1.13.4 See Also

Recipe 1.9; Recipe 1.12

Example 1-36. Combining sorting and paging in a DataGrid (.aspx)
 <%@ Page Language="vb" AutoEventWireup="false"      Codebehind="CH01DatagridWithSortingAndPagingVB.aspx.vb"      Inherits="ASPNetCookbook.VBExamples.CH01DatagridWithSortingAndPagingVB" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html>   <head>     <title>DataGrid With Sorting And Paging</title>     <link rel="stylesheet" href="css/ASPNetCookbook.css">   </head>   <body leftmargin="0" marginheight="0" marginwidth="0" topmargin="0">     <form id="frmDatagrid" method="post" runat="server">       <table width="100%" cellpadding="0" cellspacing="0" border="0">         <tr>           <td align="center">             <img src="images/ASPNETCookbookHeading_blue.gif">           </td>         </tr>         <tr>           <td class="dividerLine">             <img src="images/spacer.gif" height="6" border="0"></td>         </tr>       </table>       <table width="90%" align="center" border="0">         <tr>           <td><img src="images/spacer.gif" height="10" border="0"></td>         </tr>         <tr>           <td align="center" class="PageHeading">             DataGrid With Sorting And Paging (VB)           </td>         </tr>         <tr>           <td><img src="images/spacer.gif" height="10" border="0"></td>         </tr>         <tr>           <td align="center">  <asp:DataGrid   id="dgBooks"   runat="server"   BorderColor="000080"   BorderWidth="2px"   AutoGenerateColumns="False"   width="100%"   AllowSorting="True"   AllowPaging="True"   PageSize="5"   PagerStyle-Mode="NumericPages"   PagerStyle-PageButtonCount="5"   PagerStyle-Position="Bottom"   PagerStyle-HorizontalAlign="Center"   PagerStyle-NextPageText="Next"   PagerStyle-PrevPageText="Prev">   <HeaderStyle   HorizontalAlign="Center"   ForeColor="#FFFFFF"   BackColor="#000080"   Font-Bold=true   CssClass="TableHeader" />   <ItemStyle   BackColor="#FFFFE0"   cssClass="TableCellNormal" />   <AlternatingItemStyle   BackColor="#FFFFFF"   cssClass="TableCellAlternating" />     <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>  </td>         </tr>       </table>     </form>   </body> </html> 

Example 1-37. Combining sorting and paging in a DataGrid code-behind (.vb)
 Option Explicit On  Option Strict On '----------------------------------------------------------------------------- ' '   Module Name: CH01DatagridWithSortingAndPagingVB.aspx.vb ' '   Description: This class provides the code behind for '                CH01DatagridWithSortingAndPagingVB.aspx ' '***************************************************************************** Imports Microsoft.VisualBasic Imports System.Configuration Imports System.Data Imports System.Data.OleDb Imports System.Web.UI.WebControls Namespace ASPNetCookbook.VBExamples   Public Class CH01DatagridWithSortingAndPagingVB     Inherits System.Web.UI.Page     'controls on form     Protected WithEvents dgBooks As System.Web.UI.WebControls.DataGrid     '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"     '*************************************************************************     '     '   ROUTINE: Page_Load     '     '   DESCRIPTION: This routine provides the event handler for the page load     '                event.  It is responsible for initializing the controls      '                on the page.     '-------------------------------------------------------------------------     Private Sub Page_Load(ByVal sender As System.Object, _                           ByVal e As System.EventArgs) _             Handles MyBase.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     '*************************************************************************     '     '   ROUTINE: dgBooks_SortCommand     '     '   DESCRIPTION: 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.     '-------------------------------------------------------------------------     Private Sub dgBooks_SortCommand(ByVal source As Object, _                                     ByVal e As DataGridSortCommandEventArgs) _             Handles dgBooks.SortCommand       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 oder       '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     '*************************************************************************     '     '   ROUTINE: dgBooks_PageIndexChanged     '     '   DESCRIPTION: This routine provides the event handler for the page index     '                changed event of the datagrid.  It is responsible for      '                setting the page index from the passed arguments and     '                rebinding the data.     '-------------------------------------------------------------------------  Private Sub dgBooks_PageIndexChanged(ByVal source As Object, _   ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) _   Handles dgBooks.PageIndexChanged   Dim currentSortExpression As String   Dim currentSortOrder As enuSortOrder   'set new page index and rebind the data   dgBooks.CurrentPageIndex = e.NewPageIndex   '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)   'rebind the data in the datagrid   bindData(currentSortExpression, _   currentSortOrder)   End Sub  'dgBooks_PageIndexChanged  '*************************************************************************     '     '   ROUTINE: bindData     '     '   DESCRIPTION: This routine queries the database for the data to      '                displayed and binds it to the datagrid     '-------------------------------------------------------------------------     Private Sub bindData(ByVal sortExpression As String, _                          ByVal sortOrder As enuSortOrder)       Dim dbConn As OleDbConnection       Dim da As OleDbDataAdapter       Dim ds As DataSet       Dim strConnection As String       Dim strSQL As String       Dim index As Integer       Dim col As DataGridColumn       Dim colImage As String       Dim strSortOrder As String       Try         'get the connection string from web.config and open a connection          'to the database         strConnection = _             ConfigurationSettings.AppSettings("dbConnectionString")         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)         ds = New DataSet         da.Fill(ds)         '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='images/sort_ascending.gif' border='0'>"             Else               colImage = " <img src='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 = ds         dgBooks.DataBind( )       Finally         'cleanup         If (Not IsNothing(dbConn)) Then           dbConn.Close( )         End If       End Try     End Sub  'bindData   End Class  'CH01DatagridWithSortingAndPagingVB End Namespace 

Example 1-38. Combining sorting and paging in a DataGrid code-behind (.cs)
 //---------------------------------------------------------------------------- // //   Module Name: CH01DatagridWithSortingAndPagingCS.aspx.cs // //   Description: This class provides the code behind for //                CH01DatagridWithSortingAndPagingCS.aspx // //**************************************************************************** using System; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Web.UI.WebControls; namespace ASPNetCookbook.CSExamples {   public class CH01DatagridWithSortingAndPagingCS : System.Web.UI.Page   {     // controls on form     protected System.Web.UI.WebControls.DataGrid dgBooks;     // the following enumeration is used to define the sort orders     private enum enuSortOrder : int     {       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";     //************************************************************************     //     //   ROUTINE: Page_Load     //     //   DESCRIPTION: This routine provides the event handler for the page     //                load event.  It is responsible for initializing the     //                controls on the page.     //------------------------------------------------------------------------     private void Page_Load(object sender, System.EventArgs e)     {       String defaultSortExpression;       enuSortOrder defaultSortOrder;       // wire the event handlers       this.dgBooks.PageIndexChanged +=         new DataGridPageChangedEventHandler(this.dgBooks_PageIndexChanged);       this.dgBooks.SortCommand +=         new DataGridSortCommandEventHandler(this.dgBooks_SortCommand);       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     //************************************************************************     //     //   ROUTINE: dgBooks_SortCommand     //     //   DESCRIPTION: 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.     //------------------------------------------------------------------------     private 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 oder       // of the current column needs to be changed.       newSortExpression = e.SortExpression;       if (newSortExpression == 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     //************************************************************************     //     //   ROUTINE: dgBooks_PageIndexChanged     //     //   DESCRIPTION: This routine provides the event handler for the page     //                index changed event of the datagrid.  It is responsible     //                for setting the page index from the passed arguments     //                and rebinding the data.     //------------------------------------------------------------------------  private void dgBooks_PageIndexChanged(Object source,   System.Web.UI.WebControls.DataGridPageChangedEventArgs e)   {   String currentSortExpression;   enuSortOrder currentSortOrder;   // set new page index and rebind the data   dgBooks.CurrentPageIndex = e.NewPageIndex;   // get the current sort expression and order from the viewstate   currentSortExpression = (String)(ViewState[VS_CURRENT_SORT_EXPRESSION]);   currentSortOrder = (enuSortOrder)(ViewState[VS_CURRENT_SORT_ORDER]);   // rebind the data in the datagrid   bindData(currentSortExpression,   currentSortOrder);   }  // dgBooks_PageIndexChanged  //************************************************************************     //     //   ROUTINE: bindData     //     //   DESCRIPTION: This routine queries the database for the data to     //                displayed and binds it to the repeater     //------------------------------------------------------------------------     private void bindData(String sortExpression,       enuSortOrder sortOrder)     {       OleDbConnection dbConn = null;       OleDbDataAdapter da = null;       DataSet ds = 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 =            ConfigurationSettings.AppSettings["dbConnectionString"];         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);         ds = new DataSet( );         da.Fill(ds);         // 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='images/sort_ascending.gif' border='0'>";             }             else             {               colImage = " <img src='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 = ds;         dgBooks.DataBind( );       }  // try       finally       {         //clean up         if (dbConn != null)         {           dbConn.Close( );         }       }  // finally     }  // bindData   }  // CH01DatagridWithSortingAndPagingCS } 



ASP. NET Cookbook
ASP.Net 2.0 Cookbook (Cookbooks (OReilly))
ISBN: 0596100647
EAN: 2147483647
Year: 2006
Pages: 179

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net