Recipe 2.12. Combining Sorting and Paging in a DataGrid


Problem

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

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 2.10 for details). Next, with pagination enabled, add a small amount of custom code to track the sort column and order so they can be maintained between client round trips and used any time rebinding is required. Figure 2-12 shows a typical DataGrid with this solution implemented. Examples 2-29, 2-30 through 2-31 show the .aspx file and code-behind files for an application that produces this output.

Figure 2-12. Combining sorting and paging in a DataGrid output


Discussion

Getting 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 order so 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 order data in the view state so 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 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 order, forcing the 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 order so 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 Recipes 2.9 and 2.10 for more detailed discussions of the various nuances of this recipe.

See Also

Recipes 2.9 and 2.10

Example 2-29. Combining sorting and paging in a DataGrid (.aspx)

 <%@ Page Language="VB" MasterPageFile="~/ASPNetCookbookVB.master" AutoEventWireup="false" CodeFile="CH02DatagridWithSortingAndPagingVB.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH02DatagridWithSortingAndPagingVB" Title="DataGrid With Sorting And Paging" %> <asp:Content  Runat="server" ContentPlaceHolder> <div align="center" > DataGrid With Sorting And Paging (VB) </div> <asp:DataGrid runat="server" BorderColor="#000080" BorderWidth="2px" AutoGenerateColumns="False" width="90%" HorizontalAlign="Center" AllowSorting="True" AllowPaging="True" PageSize="5" PagerStyle-Mode="NumericPages" PagerStyle-PageButtonCount="5" PagerStyle-Position="Bottom" PagerStyle-HorizontalAlign="Center" PagerStyle-NextPageText="Next" PagerStyle-PrevPageText="Prev" PagerStyle-Css OnPageIndexChanged="dgBooks_PageIndexChanged" 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-30. Combining sorting and paging in a DataGrid 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 ''' CH02DatagridWithSortingAndPagingVB.aspx ''' </summary> Partial Class CH02DatagridWithSortingAndPagingVB 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 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      '''*************************************************************  ''' <summary>  ''' 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.  ''' </summary>  '''  ''' <param name="source">Set to the sender of the event</param>  ''' <param name="e">Set to the event arguments</param>  Protected Sub dgBooks_PageIndexChanged(ByVal source As Object, _   ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs)  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 'dgCustomers_PageIndexChanged    '''*******************************************************************  ''' <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 'CH02DatagridWithSortingAndPagingVB End Namespace 

Example 2-31. Combining sorting and paging in a DataGrid 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  /// CH02DatagridWithSortingAndPagingCS.aspx  /// </summary>  public partial class CH02DatagridWithSortingAndPagingCS : 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 == 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 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.   /// </summary>   ///   /// <param name="source">Set to the sender of the event</param>   /// <param name="e">Set to the event arguments</param>   protected 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)(this.ViewState[VS_CURRENT_SORT_EXPRESSION]);     currentSortOrder =   (enuSortOrder)(this.ViewState[VS_CURRENT_SORT_ORDER]);     // rebind the data in the datagrid     bindData(currentSortExpression,  currentSortOrder);   } // dgCustomers_PageIndexChanged   ///**************************************************************   /// <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   } // CH02DatagridWithSortingAndPagingCS } 



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

Similar book on Amazon

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