An Extensive Examination of the DataGrid Web Control: Part 4, Part 2

By Scott Mitchell

---

Read Part 1

---

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)    BindData(e.SortExpression) End Sub

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:

Sub BindData(sortExpr as String) '1. Create a connection Dim myConnection as New _ SqlConnection(ConfigurationSettings.AppSettings("connStr")) '2. Create the command object, passing in the SQL string Dim strSQL as String = "SELECT TOP 10 FAQID, F.Description, " & _ "F.ViewCount, F.FAQCategoryID, SubmittedByName, " & _ "SubmittedByEmail, DateEntered, CatName = C.Name " & _ "FROM tblFAQ F (nolock) " & _ "INNER JOIN tblFAQCategory C (nolock) ON " & _ " C.FAQCategoryID = F.FAQCategoryID " & _ "WHERE Approved=1 ORDER BY " & sortExpr Dim myCommand as New SqlCommand(strSQL, myConnection) 'Set the datagrid's datasource to the datareader and databind myConnection.Open() dgPopularFAQs.DataSource = _ myCommand.ExecuteReader(CommandBehavior.CloseConnection) dgPopularFAQs.DataBind() End Sub

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 BoundColumns will need to be updated, like so:

<asp:DataGrid runat="server" id="dgPopularFAQs" AllowSorting="True" OnSortCommand="SortResults"> ... <Columns> ... <asp:BoundColumn DataField="ViewCount" <b>SortExpression="F.ViewCount DESC" </b> DataFormatString="{0:#,###}" HeaderText="Views" ItemStyle-HorizontalAlign="Center" /> ... <asp:BoundColumn DataField="DateEntered" SortExpression="DateEntered DESC" HeaderText="Date Added" DataFormatString="{0:MM-dd-yyyy}"/> </Columns> </asp:datagrid>

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!

By Scott Mitchell