An Extensive Examination of the DataGrid Web Control: Part 4, Part 2
By 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:
Sub SortData(sender as Object, e as DataGridSortCommandEventArgs)
|
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,
after the ORDER BY
clause. (Since the tables tblFAQ
and
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
the SortExpressions
in the BoundColumn
s 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
ORDER BY Statements in Stored Procedures for information on how to accomplish this task...
|
Conclusion
Sorting the contents of a DataGrid is fairly simple and involves three manageable steps: setting the
AllowSorting
property (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.
Happy Programming!