You are implementing a
DataGrid with both
sorting and pagination, and you are having trouble making the two
features work together.
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.
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.
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 NamespaceExample 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
}Get ASP.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
