Using ASP.NET 3.5's ListView and DataPager Controls: Grouping By a Data FieldBy Scott Mitchell
The ListView control renders its
ItemTemplateonce 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
Productstable contains the following records:
|1||Ipoh Coffee||2||Sasquatch Ale||Bigfoot Breweries||3||Chartreuse verte||Aux joyeux eccl�siastiques||4||Queso Manchego La Pastora||
||5||Laughing Lumberjack Lager||Bigfoot Breweries||6||Steeleye Stout||Bigfoot Breweries||7||C�te de Blaye||Aux joyeux eccl�siastiques|
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
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:
The Suppliers table's CompanyName column returns the name of the supplier. There are two
OUTER JOINs in the
The first one
JOINs to the Categories table to retrieve the product's associated category name. The second
back the supplier name (i.e.,
ORDER BY clause at the tail end of the
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
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
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, |
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
By adding the above databinding syntax we are instructing the ListView to call the
AddGroupingRowIfSupplierHasChanged each time it
ItemTemplate, which it will do once per record being bound to the ListView. The
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
Protected). Keep in mind that this method
is called once for every record being bound to the ListView. Add the following code:
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.
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
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
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
Productstable 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.
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.
Selectingevent 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
Argumentsproperty that can be used to specify the sort order of the data, among other things. Setting the sort order via the
Argumentsproperty 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.)
Select event handler achieves this desired effect, sorting the results by the column selected from the
Note that the
SortExpression is set to the column selected from the
DropDownList and the
ProductName column. This ensures that for those products with the same group by data field values are sorted
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
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.
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.