Dynamic Column Sorting and Paging in ASP.NET

Column sorting in an ASP page is a very useful feature giving the user the ability to sort the presented data to their liking, that and can easily be done in one direction by passing the appropriate column sorting order into your query string. But to dynamically and bi-directionally sort is another thing not common and inherent in .NET right out of the box; well unless this is .NET 2.0, which is cool and standard. But since that won't be official for a while, we'll deal with .NET 1.1 in the meantime. At any rate, anyone searching the internet for this kind of functionality will undoubtedly run into quite a few ways of doing this. But I felt I'd just present it in a simple a manner as possible, without any extra hubbub.

I first toyed with the idea of bi-directional and dynamic column sorting and paging back in classic ASP. Good ol' ASP is slightly a little easier to internally manipulate I think in comparison to a .NET Datagrid. A Datareader loop is something similar to a regular ASP standard recordset loop. But a datagrid in .NET is another animal altogether. You can enable sorting and pass into it your chosen field column to sort and the direction, and it'll work like it's supposed to. But what if you needed to constantly resort in the other direction for any given column?

In this article I will demonstrate how to easily do just that, and retain your sort order even when paging. So let's have a look at the entire .NET page.

Let's Get Our Data

<%@ Page Language="VB" Debug="False" Strict="True" Explicit="True" Buffer="True"%>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<title>Dynamic Column Sorting and Paging in ASP.NET</title>
</head>
<body bgcolor="#FFFFFF" topmargin="0" marginheight="0">
<script language="VB" runat="server">
Sub Page_Load (sender As Object, e As EventArgs)
    If SortExp.Text = "" then
        BindSQL ("title asc")
    Else
        BindSQL (SortExp.Text)
    End If
End Sub

Sub BindSQL (SortField As String)
    Dim MyConnection As SqlConnection
    Dim MyCommand As SqlDataAdapter
    Dim DS as Dataset
    Dim sqlStr As String = "SELECT authors.au_fname, authors.au_lname, titles.title, titles.price FROM authors, titleauthor, titles WHERE titleauthor.au_id = authors.au_id AND titleauthor.title_id = titles.title_id ORDER BY title asc"
    Dim strConn As String = "server=(local);uid=sa;pwd=;database=pubs;Trusted_Connection=yes;"
    Dim RcdCount As Integer
    SortExp.Text = SortField
    MyConnection = New SqlConnection(strConn)
    MyCommand = New SqlDataAdapter(sqlStr, MyConnection)
    DS = new Dataset()
    MyCommand.Fill(DS, "pubs")
    Dim Source As DataView = DS.Tables(0).DefaultView
    Source.Sort = SortField
    Pubs.DataSource = Source
    Pubs.Databind()
End Sub

Function SortOrder (Field As String) As String
    Dim so As String = SortExp.Text
    If Field = so Then
        SortOrder = Replace (Field,"asc","desc")
    Else
        SortOrder = Replace (Field,"desc","asc")
    End If
End Function

Sub BookList_Sort (Sender As Object, E As DataGridSortCommandEventArgs)
    pubs.CurrentPageIndex = 0 'To sort from top
    BindSQL (SortOrder (E.SortExpression).ToString()) 'Rebind our Datagrid
End Sub
Sub BookList_PageChange (Source As Object, E As DataGridPageChangedEventArgs)
    pubs.CurrentPageIndex = E.NewPageIndex
    BindSQL (SortExp.Text)
End Sub
</script>
<BR><BR>
<H2>Dynamic Column Sorting and Paging in ASP.NET</H2>
<BR><BR>
<form runat="server">
    <ASP:Datagrid id="Pubs" runat="server"
    Pagesize="10"
    AllowSorting="True"
    AllowPaging="True"
    AllowCustomPaging="False"
    PagerStyle-Visible = "True"
    PagerStyle-Mode = "NumericPages"
    HeaderStyle-BackColor="Blue"
    HeaderStyle-ForeColor="White"
    OnSortCommand="BookList_Sort"
    OnPageIndexChanged="BookList_PageChange"
    AutoGenerateColumns="false"
    >
    <Columns>
        <asp:BoundColumn HeaderText="Title" SortExpression="title asc" DataField="title" Headerstyle-Horizontalalign="Center" ItemStyle-Wrap="false"/>
        <asp:BoundColumn HeaderText="Last Name" SortExpression="au_lname asc" DataField="au_lname" Headerstyle-Horizontalalign="Center" ItemStyle-Wrap="false"/>
        <asp:BoundColumn HeaderText="First Name" SortExpression="au_fname asc" DataField="au_fname" Headerstyle-Horizontalalign="Center" ItemStyle-Wrap="false"/>
        <asp:BoundColumn HeaderText="Price" SortExpression="price asc" DataField="price" Headerstyle-Horizontalalign="Center" ItemStyle-Wrap="false"/>
    </Columns>
    </ASP:DataGrid>
    <asp:Label id="SortExp" runat="server" Visible="False" />
</form>
</body>
</html>

Let's Open Up This Code

To begin with, we imported our namespaces that'll enable us to access our database and retrieve our data. Next we initialize any default actions to be executed upon page load. In this instance we check to see if we have a default sorting order and if we don't we assign it one or simply allow it to accept what's passed in. After that we added our databinding subroutine that'll be called upon to get our data and bind our grid. Now the one new thing that we are accepting is the parameter SortField as String that is passed into our sub, which is going to be the sort order for our columns, and this is pulled from our hidden field located at the end of our page as a label web control. We do of course want to retain the state of our sort across pages when we page our data, so I chose this method among others.

You can opt for alternatives to maintaining state, as there are nine ways in which .NET can accommodate state management. You can read more about user state in - Nine Options for Managing Persistent User State in Your ASP.NET Application. But in this example I chose  the least effortless way to maintain state across postback, and this being our hidden label web control.

<asp:Label id="SortExp" runat="server" Visible="False" />

Now moving on, after dimming our variables I pull in the sort expression that is passed once you click on a column heading. I discuss this process further down. Now, once we connect to our database and fill our datagrid, we now need to create a DataView from our DataSet and pass into it the sort order we want, because without a Dataview this wouldn't be possible. So once we bind our grid and retrieve our data, we can can now choose which column to sort. But how does our datagrid know which column to sort and which order initially?

How we sort the things we do

To begin, we first setup our datagrid properties and make sorting possible by setting Allowsorting to true thereby allowing our column headings to activate and that is how our datagrid will know. We also set Autogeneratecolumns to false so we can determine what columns we prefer to display. To have this happen we need to setup within our datagrid, TemplateColumns , and this is shown below. Notice how a boundcolumn web control is placed within our column tags in our datagrid, and within each BoundColumn is where we want to specify the column and DataField we want to initially display and in this instance the default sorting order we wish to return when the column get's clicked. In our example listed below, we specified a default SortExpression setting of "title asc."

    <Columns>
        <asp:BoundColumn HeaderText="Title" SortExpression="title asc" DataField="title" Headerstyle-Horizontalalign="Center" ItemStyle-Wrap="false"/>
    </Columns>

The additional parameters listed in our Datagrid template columns are Headerstyle-Horizontalalign to align the text in the heading, and ItemStyle-Wrap which is equivalent to the HTML nowrap. Our SortExpression is the default sort order for our respective column, which will kick off our sub and functions, as we'll soon see. Finally in our datagrid we have the crucial event handler OnSortCommand that will trigger our subroutine that does all the magic.

    Sub BookList_Sort (Sender As Object, E As DataGridSortCommandEventArgs)
        pubs.CurrentPageIndex = 0 'To sort from top BindSQL(SortOrder(E.SortExpression))
    End Sub

This sub is called upon by clicking our column heading in our datagird. It first sets our CurrentPageIndex to 0, so our data rebinds from the top, and then binds our grid, and passes into our databind subroutine ( BindSQL ) the appropriate sort order. Now depending on the initial sort order, you'll notice that the BindSQL command has another command - Sortorder . This command calls the SortOrder function , which does the magic, and sends into our BindSQL sub the new sortorder to rebind and resort our datagrid. As you'll now see, it's really pretty simple.

    Function SortOrder (Field As String) As String
    Dim so As String = SortExp.Text
        If Field = so Then
            SortOrder = Replace (Field,"asc","desc")
        Else
            SortOrder = Replace (Field,"desc","asc")
        End If
    End Function

When this function receives its parameter it does two things. It checks to see what was sort order was passed to it, and upon this information returns to the datagrid method the order in which to rebind our grid with.

When we click on any given column, i.e. title, we end up passing into this function "title asc." Once this happens, our function SortOrder checks to see that if title asc has been already passed in by comparing the string passed to it and determine if it's equal with the value in our hidden label web control  SortExp . Otherwise it proceeds to send out to our hidden field the new datagrid sorting order, by replacing the sent sortorder with its reverse. When this is determined the BookList_Sort sub binds the datagrid with the sort order according to the selected column and initial direction or reverse direction if this has been already initially sorted that way, and mission accomplished.

As far as paging is concerned, the built in paging capability in this example doesn't need much explanation. We simply add the necessary datagrid properties to facilitate this, these being:

Pagesize="10"
AllowPaging="True"
AllowCustomPaging="False"
PagerStyle-Visible = "True"
PagerStyle-Mode = "NumericPages"
OnPageIndexChanged="BookList_PageChange"

We set up a default pagesize of 10, allow paging and disabled custom paging . Also we made sure our pager control is visible, and that we want numeric style paging, instead of the left/right arrows. FInally, we added our event handler call when the datagrid paging is activated, in turn calling our BookList_PageChange method, listed below, to handle our paging and rebind our grid with the correct sort order, and that's it.

    Sub BookList_PageChange (Source As Object, E As DataGridPageChangedEventArgs)
        pubs.CurrentPageIndex = E.NewPageIndex 'Set datagrid to current page index
        BindSQL (SortExp.Text) ' Rebind Datagrid with sort order
    End Sub

Although, I have only demonstrated dual directional sorting with standard built in paging, this code can be easily retrofitted to accommodate custom datagrid paging - Custom ASP.NET Datagrid Paging With Exact Count, add Data Caching, set up a Yahoo/Hotmail style Datagrid and even building your very own Full Featured and powerful Custom Datagrid Control that even has drag & drop column capabilities, like the ones you see selling for hundred of dollars!

So instead of bogging the reader (that's you) with too much info, I thought it best to just focus on this one piece of functionality alone, so you can firmly grasp the methodology of it, and leave the process of adding the rest of the bells and whistles up to you.

Conclusion

Well that's really all there is to it in a nutshell. In turn it was all pretty easy, and you ended up giving your users some cool dual sorting and paging capabilities.

Until next time, Happy .NETing.

You might also like...

Comments

About the author

Dimitrios Markatos

Dimitrios Markatos United States

Dimitrios, or Jimmy as his friends call him, is a .NET developer/architect who specializes in Microsoft Technologies for creating high-performance and scalable data-driven enterprise Web and des...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Theory is when you know something, but it doesn't work. Practice is when something works, but you don't know why. Programmers combine theory and practice: Nothing works and they don't know why.”