Sorting in DataList
Written on: July 22nd, 2002.
We all know that, the DataList web server control does not support in-built sort option. Still we can provide users with the sort option for all columns in a DataList. The logic is very simple. The DataView object has a property called Sort. We are going to make use of this property to sort the rows in a DataList. For our example, we will consider the table, stores from the database (SQL Server 2000), pubs.
Aspects that we will be learn in this article.
- How to populate a DataList?
- How to build the User Interface for Sorting in a DataList?
- How to make user of the Sort property of DataView?
- An alternate solution for the Sort property!
Populating the DataList
For our example, we will take the table STORES in the PUBS Database. Since stored procedures are very much better than inline query, we use a stored procedure called, sp_stores_sel, which contains a single SQL statement. The SQL statement would be Select * from stores. And finally, we need to bind the DataView to the DataList.
How to build the User Interface for Sorting in a DataList?
We are going to provide hyperlinks for all column headings. So, user can click any column they like. The column clicked will be sorted. As simple as that. When user clicks on any column, we will invoke a Server Side method. The server side method will bind the datalist and will also sort the datalist. We will also provide the user with a DropDownList, in which, they can select the type of sort they need, typically Ascending or Descending. Let us take a look at the Header template to see how we can provide hyperlinks to column headings.
Code for HeaderTemplate of the DataList.
<table width="100%" style="font: 10pt verdana" cellpadding=0 cellspacing=0>
<th align=left><a href="datalistsort.aspx#this" onserverclick="SortStoreID" runat="server">Store ID</a></th>
<th align=left><a href="datalistsort.aspx#this" onserverclick="SortStoreName" runat="server">Store Name</a></th>
<th align=left><a href="datalistsort.aspx#this" onserverclick="SortStoreAddress" runat="server">Address</a></th>
<th align=left><a href="datalistsort.aspx#this" onserverclick="SortStoreCity" runat="server">City</a></th>
<th align=left><a href="datalistsort.aspx#this" onserverclick="SortStoreState" runat="server">State</a></th>
How it works?
Important Note: The name of the file that I have used in the href property is "datalistsort.aspx". You should replace this with your aspx filename, unless you keep the filename as myself.
We create a hyperlink for each column names. The target (href) of the hyperlink is set to name of the aspx page itself. Then, we set the property, OnServerClick. For the first column, StoreID, we invoke a server side method called, SortStoreID. This method will populate the DataList and will also sort the rows based on the SortID. The logic is the same for all other columns. Let us take a look at the ServerSide method, SortStoreID.
|Public Sub SortStoreID(ByVal s As Object, ByVal e As EventArgs)|
SortDataList("stor_id " & cboSortType.SelectedItem.Value)
lblStatus.Text = "Currently Sorted on StoreID: " & cboSortType.SelectedItem.Text
Private Sub SortDataList(ByVal strSort As String)
Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim objCmd As New SqlDataAdapter("exec sp_stores_sel", objConn)
Dim objDS As New DataSet()
Dim objDT As New DataTable()
Dim objDV As New DataView()
objDT = objDS.Tables(0)
objDV = objDS.Tables(0).DefaultView
objDV.Sort = strSort
dList.DataSource = objDV
How it works?
Inside the method, SortStoreID we invoke another private method, SortDataList which accepts a string as the argument. This argument is nothing but the sort criteria for the DataList. The method, SortDataList is the heart for the sorting. Apart from using the DataSet, we make use of DataTable and DataView. DataView has a property called, sort. This property takes a sort expression as the input. Sort Expression is made up of the field name and the sort type. Sort type can be either ASC or DESC, which stands for ascending and descending respectively.
An alternate solution for the Sort property!
Instead making use of the SORT property, we can do the sorting in the Stored procedure itself. All we need is to pass necessary arguments to the stored procedure. The necessary argument is nothing but the field name that needs to be sorted and the sort type. So, if you have some time, try this alternative.
Sample output of our scenario
Test this Script
Fig: Sorting in DataList.
Download the code
Click here to download the ASPX page
Click here to download the Stored Procedure
Paging in DataList
Thus, we have gone through how to provide sort option for the DataList.
Send your comments to firstname.lastname@example.org