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.
Comments