ASP.NET & Databases : Part 7

Published 12/11/01

Introduction

Welcome the the seventh part in the series of ASP.NET & Databases. Sadly, this will be the last part in the series as I have decided that I will cover separate topics in other articles. In this part we will go into sorting data in a DataGrid. DataGrid's don't have complete support for sorting built into them, but there are a few features that help you to develop a way to sort your data.

Setting up the DataGrid

First, there are several things that must happen to a DataGrid before you can sort -

1. You must turn AutoGenerateColumns on (=true). This means that you will lose any special columns and lose the ability to edit the data. It is a wise idea to create one page for viewing and another for editing.

2. You must add the attribute AllowSorting and set it to true. This makes each column header a link that will reload the page when clicked.

3. You must add the attribute OnSortCommand and set it to the method that will sort the data (in this case dg_sort).

4. You must add the method dg_sort.

We will be using the same page that we have been using for the whole series to view the data. See part 6 for a complete listing of that page. After making these changes, your DataGrid will look like this -

<asp:DataGrid id="dg" runat="server" Bordercolor="black" gridlines="vertical" font-names="Arial" font-size="10pt" HeaderStyle-BackColor="#FFCC00" ItemStyle-BackColor="#FFCC66" AlternatingItemStyle-BackColor="#FFFFFF" AutoGenerateColumns="True" OnEditCommand="dg_edit" OnCancelCommand="dg_cancel" OnUpdateCommand="dg_update" OnSortCommand="dg_sort" AllowSorting="True" > </asp:dataGrid>

(The changes are in red.)

Here is a picture of what the DataGrid should look like -

dg_sort

The method to sort (dg_sort) is like any other of the DataGrid commands that you have been working in, however it takes a different parameter in and needs to sort a DataView.

What's a DataView?

A DataView is a view of your DataSet, when you assigned your DataTable to your DataGrid, it assigned the default DataView to the DataGrid. The default view is just a direct snapshot of what the data looked like when it came out of the database.
You can create new DataViews and change their properties to manipulate the DataTable.

We won't be going into the full workings of a DataView, just one property, the sort property.

sub dg_sort(sender as object, e as DataGridSortCommandEventArgs) Dim dtable as DataTable = ds.Tables("users") Dim dview as new DataView(dtable) dview.sort = e.SortExpression.ToString & " ASC" dg.DataSource = dview dg.databind() end sub

This method simply creates a DataTable which is the table in your DataSet (the users table) and calls it dtable. The next line creates a new DataView of that table (dview).
The next line sets the sort property of the DataView which consists of - "The Column to sort by" & Ascending | Descending. You've probably seen that in the parameters we're using DataGridSortCommandEventArgs as the EventArgs and this contains the SortExpression (the column name). We simply assign that to the sort property and specify that we want ASC or Ascending data.
Then we set the DataGrid's DataSource to the new DataView and DataBind()

That wasn't too hard, was it?

Now try clicking on the different column headings to sort the data.

Ascending or Descending?

With that method, you can only do it one way (we chose Ascending). However, you can make it both. Below, I've created a form with two radio buttons (Ascending and Descending), you chose one of those and click on the column heading.

<form runat="server"> <asp:DataGrid id="dg" runat="server" Bordercolor="black" gridlines="vertical" font-names="Arial" font-size="10pt" HeaderStyle-BackColor="#FFCC00" ItemStyle-BackColor="#FFCC66" AlternatingItemStyle-BackColor="#FFFFFF" AutoGenerateColumns="True" OnEditCommand="dg_edit" OnCancelCommand="dg_cancel" OnUpdateCommand="dg_update" OnSortCommand="dg_sort" AllowSorting="True" > </asp:dataGrid> <p> <asp:radiobuttonlist runat="server" id="sortby" repeatdirection="horizontal" textalign="right" > <asp:listitem selected="true">Ascending</asp:listitem> <asp:listitem>Descending</asp:listitem> </asp:radiobuttonlist> </form>

This is simply the DataGrid with two radio buttons.

sub dg_sort(sender as object, e as DataGridSortCommandEventArgs) Dim sortby = Request.Form("sortby") Dim dtable as DataTable = ds.Tables("users") Dim dview as new DataView(dtable) If sortby = "Ascending" Then dview.sort = e.SortExpression.ToString & " ASC" Else If sortby = "Descending" Then dview.sort = e.SortExpression.ToString & " DESC" End If dg.DataSource = dview dg.databind() end sub

All that has changed, is to check which radio button is selected and to use the appropriate sort string.

You can have a whole form with other properties relating to how to sort the data and then use something like this.

Below are some screenshots of the sorting at work.