An Extensive Examination of the DataGrid Web Control: Part 4, Part 2By Scott Mitchell
In Part 1 we looked at the first two steps for getting sorting
to work: setting the DataGrid's
AllowSorting property to True and creating a sort event
handler. In this part we'll tackle the remaining step - creating a function to get the data in
the specified sorted order - and show how to tie it all together!
Creating a Function to Get the Data in the Specified Sorted Order
Ideally what we would like to have is some function that we can pass a sort expression to that then retrieves the data in sorted order and either binds the data directly to the DataGrid or returns a DataReader object which we can then bind to the DataGrid. Imagine, for a moment, that we had such a function, call it
BindData, which accepted a sort expression string as input. This function then retrieved the data in the order specified and bound it to the DataGrid. Given such a function our sort event handler would reduce down into one line:
Now the only problem is creating such a function! The body of this function depends on the data source
you're using and how you can sort the data. In the previous examples in this article series we've been
using a stored procedure used on ASPFAQs.com to get the 10 most
popular FAQs. Rather than adjust the stored procedure to allow an optional parameter specifying
what column to sort on (probably the better approach), I am just going to copy in the SQL text and
use that. Hence, my
BindData function will look like:
Note that we are dynamically adding the
sortExp variable at the end of the SQL query,
ORDER BY clause. (Since the tables
tblFAQCategory have many of the same column names, when specifying what to sort I needed
to specify the table alias and direction to sort, like:
F.ViewCount DESC. This means
SortExpressions in the
BoundColumns will need to be updated, like so:
Finally, when the page is first loaded we need to sort the DataGrid's data based on some default value.
I chose to sort by the ViewCount in descending order. Hence, in the
Page_Load event handler
we need to call
BindData("F.ViewCount DESC"), but only on the first page load. Realize
that the data is rebound whenever the user opts to sort by a particular column (which induces a postback), so
we don't want to also bind the data in the
Page_Load event handler.
The complete code and a live demo can be seen here.
|Specifying the Sort Order Using a Stored Procedure|
This article examined how to retrieve the properly ordered results by reissuing an in-line SQL statement. While this
approach definitely works, you may be wondering how to do the same using a stored procedure. That is, if you have
a stored procedure written that returns the rows of a particular table, how can you indicate to the stored procedure
to reorder the results differently? Read Dynamic
Sorting the contents of a DataGrid is fairly simple and involves three manageable steps: setting the
AllowSortingproperty (bar far the easiest of the three!); creating a sort event handler; and creating a means to dynamically order the data. With these three components in place, creating a sortable DataGrid is merely a matter of plugging the pieces together.