Paging and Sorting in an ASP.NET DataGrid...

Allowing paging or sorting in a DataGrid is a very simple process. Allowing for both is only slightly more complicated. This article shows you how to do it.

By: John Kilgo

Date: May 25, 2003

Download the code.

As you may know by now it is quite easy to implement sorting or paging in a DataGrid. The two can be combined but if you do it exactly as you would seperately, you'll find that the sorting will not be maintained properly as you change pages. This is because ViewState does not maintain the proper sorting between postbacks to the server. We have to program in a way to do that. One way to do it is to use an invisible label on the page to maintain the ORDER BY clause between page requests. This is the technique this article will demonstrate.

First we will look at DGPageSort.aspx which implements the hidden Label control and the DataGrid control. Notice that we have we have included a label control (lblOrderBy) with its visible property set to False. Most of the DataGrid properties are to make it look decent. But we have also set up OnPageIndexChanged and OnSortCommand event handlers and a few other properties to setup paging and sorting. Everything else should be self explanatory.

<%@ Page Language="vb" Src="DGPageSort.aspx.vb" Inherits="DGPageSort"%>
<html>
<head>
<title>Paging and Sorting in a DataGrid</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body>
<h3><font face="Verdana">Paging and Sorting in a DataGrid</font></h3>
<form ID="form1" runat="server">
<asp:Label id="lblOrderBy" runat="server" Visible="False" />
<asp:DataGrid id="dtgCusts"
              Width="700"
              Cellpadding="2"
              Cellspacing="0"
              Gridlines="Horizontal"
              HeaderStyle-BackColor="IndianRed"
              HeaderStyle-Font-Bold="True"
              HeaderStyle-Font-Name="Verdana"
              HeaderStyle-Font-Size="12px"
              HeaderStyle-ForeColor="White"
              ItemStyle-BackColor="Gainsboro"
              ItemStyle-Font-Name="verdana"
              ItemStyle-Font-Size="12px"
              AllowPaging="True"
              OnPageIndexChanged="PageIndexChanged_Click"
              PageSize="10"
              PagerStyle-Mode="NumericPages"
              PagerStyle-Position="Top"
              PagerStyle-HorizontalAlign="Center"
              PagerStyle-CssClass="pageLinks"
              AllowSorting="True"
              OnSortCommand="SortCommand_Click"
              runat="server" />
</form>
</body>
</html>

Now for the code-behind file DGPageSort.aspx.vb. Since we will be populating the grid numerous times due to paging and sorting, we have move the data binding to a routine of its own. Notice in Sub SortCommand_Click we are setting the text value of the hidden label control to " ORDER BY " & e.SortExpression. This way ViewState maintains its value even when we change pages so its value is always there when we need it. In Sub BindTheData we append the text value of the hidden label control to the end of the SQL statement so that our sort order is always maintained. Following is the code.

Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DGPageSort : Inherits Page

  Protected dtgCusts As DataGrid
  Protected lblOrderBy As Label
  Protected strSql As String = "SELECT CompanyName, ContactName, ContactTitle, Phone FROM Customers"

  Sub Page_Load(sender As Object, e As EventArgs)
    If Not Page.IsPostBack Then
      BindTheData()
    End If
  End Sub

  Sub BindTheData()
    Dim strConn As String
    StrConn = ConfigurationSettings.AppSettings("NorthwindConnection")
    strSql = strSql + lblOrderBy.Text
    Dim myDataSet As DataSet = new DataSet()
    Dim myDataAdapter As SqlDataAdapter = new SqlDataAdapter(strSql, strConn)
    myDataAdapter.Fill(myDataSet, "Customers")

    dtgCusts.Datasource = myDataSet.Tables("Customers")
    dtgCusts.DataBind()
  End Sub

  Sub PageIndexChanged_Click(sender As Object, e As DataGridPageChangedEventArgs)
    dtgCusts.CurrentPageIndex = e.NewPageIndex
    BindTheData()
  End Sub

  Sub SortCommand_Click(sender As Object, e As DataGridSortCommandEventArgs)
    lblOrderBy.Text = " ORDER BY " & e.SortExpression
    BindTheData()
  End Sub

End Class

I hope you have found how easy it is to combine paging and sorting in a single DataGrid.

you may download the code here.