Maintaining Sorting while Paging in an ASP.NET Datagrid

Introduction

The Datagrid server control offers much control and flexibility in presenting data. Two of the actions that are hard-wired into it are Paging and Sorting. On their own they work great, but not so well together. When you sort a column and then move to a previous or next page, the sorting preference is not maintained. In this article we will see how to maintain both by using the Viewstate object.

Creating the interface for our Datagrid

The idea behind this article came after trying to display this site's articles in a Datagrid. I was able to get lots of help from an article by Bipin Joshi, a good read and the basis for the code to follow. I am going to use a simple MS Access file, with only one table inside it for demonstration purposes. The table, article, is made of 5 fields:

This table includes basic information about some of the articles on this site. This Access file is included in the downloadable material for this article. After we enter some data into the table, let's see how to display it in a Datagrid.

<form runat="server" method="post">
<asp:Datagrid runat="server"
 Id="MyDataGrid"
 GridLines="Horizontal"
 AutoGenerateColumns="false"
 BorderColor="#CCCCCC"
 CellPadding="7"
 CellSpacing="0"
 AllowPaging="true"
 OnPageIndexChanged="PageGrid"
 AllowSorting="true"
 OnSortCommand="SortGrid"
 BorderWidth="1"
 PageSize="5">
 <headerstyle
  Font-Name="Tahoma"
  Font-Size="84%"
  Font-Bold="true"
  BackColor="#CCCCCC">
 </headerstyle>
 <itemstyle
  Font-Name="Verdana"
  Font-Size="70%"
  VerticalAlign="top"
  BackColor="#FFFFFF">
 </itemstyle>
 <alternatingitemstyle
  BackColor="#FFFFFF">
 </alternatingitemstyle>
 <pagerstyle
  Position="Top"
  Mode="NumericPages"
  HorizontalAlign="Center"
  BackColor="#FFFFD9">
 </pagerstyle>
<columns>
  <asp:TemplateColumn>
   <headertemplate>
    Title and Description
   </headertemplate>
  <ItemTemplate>
   <b><%# DataBinder.Eval(Container.DataItem, "title") %></b><br>
   <%# DataBinder.Eval(Container.DataItem, "description") %><br>
   <a href="<%# DataBinder.Eval(Container.DataItem, "url") %>">
   Read More...</a>
  </ItemTemplate>
 </asp:TemplateColumn>
 <asp:boundColumn
  DataField="posted"
  SortExpression="posted"
  HeaderText="Posted on"
  DataFormatString="{0:d MMM yyyy, dddd}"
  ItemStyle-Wrap="false">
 </asp:boundColumn>
 </columns>
</asp:DataGrid>
</form>



Binding our Datagrid

Our database query is going to look something like this: "SELECT title, description, url, posted FROM article ORDER BY posted DESC". The most important part in the query is what follows after the ORDER BY. We are going to make this dynamic, and use the Viewstate object to remember what we are sorting by and in what order. We will create 2 Viewstate objects:

viewstate("sortField") - what field to sort by
viewstate("sortDirection") - ASC or DESC


Our query can now become more dynamic: "SELECT title, description, url, posted FROM article ORDER BY " & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString().

I am not going to cover what a Viewstate is. If you are interested, you can take a closer look at it.

Now that we have seen how to make our query dynamic, let's look at the code to do this. First, we need to import the necessary libraries to work with our database:





<%@ Page language="vb" runat="server" explicit="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>

Our default Page_Load() method will simply check if the page is not posting back to itself. If it is not, it will set the 2 viewstates to some default values and call our custom BindGrid() method to perform our database connection, query and databind. When the page posts back to itself, there is no need to explicitly call the binding: it will be done automatically.


...
<script language="vb" runat="server">
Private Sub Page_Load(sender As Object, e As EventArgs)
 If Not Page.IsPostBack Then
  viewstate("sortField") = "posted"
  viewstate("sortDirection") = "DESC"
  BindGrid()
 End If
End Sub

Private Sub BindGrid()
 Dim strConn As string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/database/articles.mdb") & ";"
 Dim MySQL As string = "SELECT title, description, url, posted FROM article ORDER BY viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString()
 Dim MyConn As New OleDBConnection(strConn)
 Dim ds As DataSet = New DataSet()
 Dim Cmd As New OleDBDataAdapter(MySQL,MyConn)
 Cmd.Fill(ds,"Articles")
 Try
  MyDataGrid.DataSource = ds
  MyDataGrid.Databind()
 Catch e As Exception
  MyDataGrid.CurrentPageIndex = 0
 End Try
End Sub
...

Remember that in setting up our Datagrid, we asked it to perform a custom method called PageGrid() upon changing pages. When we click on the page number to go to, the Datagrid is posted back to itself, implementing the DataGridPageChangedEventArgs. Part of them is the page number we want: NewPageIndex. We take this number and assign it to the current page of the Datagrid before we bind it.




...
Private Sub PageGrid(ByVal source As Object, ByVal e As DataGridPageChangedEventArgs)
 MyDataGrid.CurrentPageIndex = e.NewPageIndex
 BindGrid()
End Sub
...

Finally, we have our sorting method: SortGrid(). When we click on a column it sends the SortExpression value of that column back to the server, which is implemented through the DataGridSortCommandEventArgs. In our example, this translates to the value of "posted" being sent when we click on the "Posted on" column. You can add more columns if you like, just make sure you are sending the correct expression back to the server.

This method sets our viewstates. The logic is simple. When we click on a column to sort by, we want to check if we are already sorting by that column. If we are not, then we simply set that new column as the field to sort by, and set the direction to sort by as DESC. If however, we are already sorting by the same column, then we switch the sorting order: from ASC to DESC and vice versa.


...
Private Sub SortGrid(ByVal source As Object, ByVal e As DataGridSortCommandEventArgs)
 If e.SortExpression.ToString() = viewstate("sortField").ToString() Then
  Select Case viewstate("sortDirection").ToString()
   Case "ASC"
    viewstate("sortDirection") = "DESC"
   Case "DESC"
    viewstate("sortDirection") = "ASC"
  End Select
 Else
  viewstate("sortField") = e.SortExpression
  viewstate("sortDirection") = "DESC"
 End If
 BindGrid()
End Sub


Now when you try to sort the columns by ASC or DESC order, and then navigate to a different page, you will notice that the Datagrid remembers your sorting order. If you'd like, you can download the Access database and the ASPX file that connects to it from the top of this page. Unzip them anywhere on your site in the same folder, and you are ready to go.

Conclusion

Using the Viewstate is a nice way of maintaining bidirectional sorting in the Datagrid. It's also possible to use Sessions, in pretty much the same way. We would just replace every occurence of viewstate([variable]) with Session([variable]), and it would work the same way. The Viewstate is a better way of doing it though. If we wanted, we could customize the sorting coluns to display an icon as well, showing the direction we are sorting by.