Using ASP.NET 3.5's ListView and DataPager Controls: Grouping By a Data Field
By Scott Mitchell
| A Multipart Series on ASP.NET's ListView and DataPager Controls |
|---|
|
This article is one in a series of articles on ASP.NET's ListView and DataPager controls, which were introduced with ASP.NET version 3.5.
|
Introduction
The ListView control renders its
ItemTemplate once for every item in its DataSource. As discussed in
Grouping Data with the ListView Control it is possible to inject grouping template
every N records. This flexibility allows for scenarios like displaying data in a multi-column table.
While the built-in grouping feature is certainly useful, when it comes to displaying data most people think of grouping to mean that records with similar attributes are lumped together. For instance, the Northwind database contains information about an assortment of products, and each product has attributes like product name, category, supplier, and so forth. While each product name is unique, many products share the same category and supplier. When someone says, "I want to group the product data," usually they mean they want to group it by one of these common attributes. The following screenshot shows the user interface people most people associate with the term grouping. Here products are grouped by supplier.
Unfortunately the ListView's grouping feature does not allow for this style of grouping. The good news is that with a few lines of code and markup we can construct such an interface. This article shows how to build a flexible grouping interface that allows the user to choose what data field to group the data by. Read on to learn more!
Step 1: Ordering the Data by the Group By Data Field
To display data grouped by a particular data field we need to first be sure to sort the data in the order of the field to be grouped by. This ensures that all of the records with the same values of the attribute being grouped by are naturally lumped together. For example, imagine that we are displaying products from the Northwind database grouped by supplier name, and that the
Products table
contains the following records:
ProductID |
ProductName |
SupplierName |
|---|---|---|
| 1 | Ipoh Coffee | NULL | 2 | Sasquatch Ale | Bigfoot Breweries | 3 | Chartreuse verte | Aux joyeux ecclésiastiques | 4 | Queso Manchego La Pastora | NULL |
5 | Laughing Lumberjack Lager | Bigfoot Breweries | 6 | Steeleye Stout | Bigfoot Breweries | 7 | Côte de Blaye | Aux joyeux ecclésiastiques |
Note: The Products table does not have a SupplierName column. Rather, it has a SupplierID field. The
name of the supplier resides in the Suppliers table, so your query would need to use a JOIN to get back the associated name.
In fact, you need to do an OUTER JOIN because the Products.SupplierID column can contain NULL values.
Clearly, this data needs to be sorted by the SupplierName column in order to have any hope of rendering an interface like the one
shown in the screenshot above. Let's start by creating a simple ListView control that displays the products in the Northwind database sorted by
supplier. Start by adding a ListView to a page and binding it to a data source control.
The demo available for download at the end of this article
has the Northwind database implemented as an Access database file in the App_Data folder (Northwind.mdb). Consequently, the
demo uses an AccessDataSource control to retrieve the data. The SELECT query used by the AccessDataSource control follows:
SELECT Products.ProductID, Products.ProductName, ...,
|
The Suppliers table's CompanyName column returns the name of the supplier. There are two OUTER JOINs in the SELECT query.
The first one JOINs to the Categories table to retrieve the product's associated category name. The second JOIN pulls
back the supplier name (i.e., Suppliers.CompanyName). The ORDER BY clause at the tail end of the SELECT statement
orders the results by the supplier name; those products with the same supplier are sorted alphabetically.
The above AccessDataSource is bound to a ListView. The ListView uses its LayoutTemplate and ItemTemplate to create a table with
a row for each product returned from the AccessDataSource control. The ListView's declarative markup follows. Some formatting markup has been removed
for brevity.
<asp:ListView ID="ProductsGroupedByDataField" runat="server" DataSourceID="ProductDataSource">
|
This ListView, when viewed through a browser, lists each product sorted by supplier name.
| Zebra Stripping the Table |
|---|
As you can see in the above screenshot, each row of the ListView has an alternate background color. I accomplished this using CSS by creating two
CSS classes, data0 and data1 (see the file ~/Styles.css in the demo code), which have different background colors.
Note that in the ItemTemplate the <tr> element has its class attribute set to the value
data<%# Container.DataItemIndex Mod 2 %>. The databinding syntax Container.DataItemIndex Mod 2 takes the index of
the current ListView item being rendered (0, 1, 2, ...) and mods it with 2. x Mod y operator returns the remainder of
x / y.
x mod 2 returns 0 if x is even and 1 if x is odd. Therefore, the |
Step 2: Adding Grouping Headers
While the above ListView displays the products sorted by supplier, it lacks the grouping headers shown in the screenshot in the introduction. What we want to do is inject a table row with the supplier's name immediately before the first product whose supplier name differs from the previous product's. If a product's supplier name is the same as the previous product then we do not want to emit any grouping row.
There are a variety of techniques available for emitting different markup based on the current data record being bound to the ListView. One of the
simplest ways is to use a formatting function. In short, we add a databindnig statement in the ItemTemplate that calls a method
in the code-behind class that returns the HTML to emit as a a string value. We need to create this method, of course, and have it return the
appropriate markup. (By the way, this technique works for any templated data Web control, such as the FormView and from within a TemplateField in a
GridView or DetailsView.)
Start by adding the following databinding syntax to the ListView's ItemTemplate:
<asp:ListView ID="ProductsGroupedByDataField" runat="server" DataSourceID="ProductDataSource">
|
By adding the above databinding syntax we are instructing the ListView to call the AddGroupingRowIfSupplierHasChanged each time it
renders the ItemTemplate, which it will do once per record being bound to the ListView. The AddGroupingRowIfSupplierHasChanged
method, which we will create momentarily, will return either an empty string (in the case that the supplier name hasn't changed since the last
product) or the HTML for a table row that announces the new supplier.
Next, create the AddGroupingRowIfSupplierHasChanged method in the page's code-behind class. This method must return a string value
and canont be marked Private (that is, it must be marked as Public or Protected). Keep in mind that this method
is called once for every record being bound to the ListView. Add the following code:
Dim lastSupplierNameValue As String = Nothing
|
The lastSupplierNameValue variable is defined outside of the method. It is a class level variable, meaning that there's only one
instance of this variable for the lifetime of the class, which starts when the request arrives and ends when the page is rendered completely and the
markup sent back to the client. Therefore, any changes to this variable in the AddGroupingRowIfSupplierHasChanged method are
remembered when this method is called again later in the page's lifecycle. The point of the lastSupplierNameValue is to hold the
value of the last unique supplier name that had a grouping header displayed.
The AddGroupingRowIfSupplierHasChanged method starts by retrieving the value of the CompanyName field for the current
record being bound to the ListView. This value is retrieved using the syntax Eval("CompanyName"). The value is then converted to a string
and saved to the currentSupplierNameValue variable.
If the CompanyName field is a database NULL value then currentSupplierNameValue is assigned an empty string.
In such a case we change the value of currentSupplierNameValue from an empty string to whatever text we want displayed for the supplier
name for those products that don't have a specified supplier. I've used the text "Unknown" but feel free to change this to whatever value best suits your
needs.
Following that, a check is made to see if the current supplier name matches up with the last known supplier name. If there is a mismatch then we have
a new supplier and we need to update lastSupplierNameValue and display the grouping header. If the current and last supplier names match
up then we don't want to add a grouping record; therefore, we return an empty string.
That's all there is to it! With this code in place we now get the desired grouping interface.
Allowing the User to Select the Data Field to Group By
The implementation shown above groups the products by supplier. But there are other database fields in the
Products table that the
user may want to group the data by: Category, Discontinued, Units In Stock, and so on. Let's look at how this more generalized functionality can be
created.
First things first: we need a user interface to allow the user to choose the data field to group by. There are an assortment of ways this can be
accomplished; the demo available for download at the end of this article uses a DropDownList with a ListItem for each grouping option.
<b>Group data by: </b>
|
You can have each DropDownList item show whatever text you want, but it's essential that the Value be the name of the database column
by which the results are to by grouped by.
Recall that grouping by a data field requires two tasks:
- Sorting the output by the data field, and
- Injecting a grouping header immediately before a product if the value of its group by data field differs from the previous product's value.
Selecting event. The Selecting event fires right before the data source control goes off to retrieve the data and provides
an opportunity to provide instructions on how to retrieve the data. The data source controls include an Arguments property
that can be used to specify the sort order of the data, among other things. Setting the sort order via the Arguments property instructs
the data source control to first get the data from the underlying source (the Northwind database, in this case) and then to order the results
by the specified expression. (For more information on working with data source control events see
Examining the Data Source Control's Events.)
The following Select event handler achieves this desired effect, sorting the results by the column selected from the GroupByField
DropDownList:
Protected Sub ProductDataSource2_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles ProductDataSource2.Selecting
|
Note that the Arguments property's SortExpression is set to the column selected from the GroupByField
DropDownList and the ProductName column. This ensures that for those products with the same group by data field values are sorted
alphabetically.
Now that the data being returned from the database is sorted by the user's selected group by data field, all that remains is to add a method to dynamically
inject a grouping header, if needed. I created a method named AddGroupingRowIfNeeded that performs this task. As you can see, it is
almost identical to the AddGroupingRowIfSupplierHasChanged method we looked at earlier. The only difference is that instead of getting
the current supplier name value we get the current value from the database field specified by the GroupByField DropDownList.
Dim lastDataFieldValue As String = Nothing
|
That's all there is to it!
The following screenshot shows this enhanced functionality in action. Here the products are grouped by Category, but the user can easily view the products grouped by some other criteria by selecting the group by data field from the drop-down list.
Conclusion
The ListView's built-in grouping functionality is useful if you want to emit markup every N items; however, it is not designed to group based on the actual data being displayed. The good news is that displaying data grouped by a data field is a pretty easy task. All you need to do is sort the data by the group by data field and then write a method that injects a grouping header when a new "group" is displayed. This can all be done with less than a dozen lines of code, and can easily be extended to let the user to select what data field to group the results by.
Happy Programming!
Further Readings:
Attachments
