To read the article online, visit http://www.4GuysFromRolla.com/articles/091708-1.aspx

Using ASP.NET 3.5's ListView and DataPager Controls: Grouping By a Data Field

By Scott Mitchell


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.

Products, grouped by suppliers.

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
1Ipoh CoffeeNULL
2Sasquatch AleBigfoot Breweries
3Chartreuse verteAux joyeux eccl�siastiques
4Queso Manchego La PastoraNULL
5Laughing Lumberjack LagerBigfoot Breweries
6Steeleye StoutBigfoot Breweries
7C�te de BlayeAux 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, ...,

Suppliers.CompanyName


FROM ((Products
   LEFT OUTER JOIN Categories ON
      Categories.CategoryID = Products.CategoryID)
   LEFT OUTER JOIN Suppliers ON
      Products.SupplierID = Suppliers.SupplierID)
ORDER BY CompanyName, 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">
   <LayoutTemplate>
      <table cellspacing="0" cellpadding="5" rules="all" border="1">
         ...
         
         <asp:PlaceHolder runat="server" ID="itemPlaceholder"></asp:PlaceHolder>
      </table>
   </LayoutTemplate>

   <ItemTemplate>
      <tr class='data<%# Container.DataItemIndex Mod 2 %>'>
         <td><%#Eval("ProductName")%></td>
         <td><%#Eval("CategoryName")%></td>
         <td><%#Eval("CompanyName")%></td>
         <td><%#Eval("UnitPrice", "{0:c}")%></td>
         <td>
            In Stock: <%#Eval("UnitsInStock")%><br />
            On Order: <%#Eval("UnitsOnOrder")%><br />
            Reorder Level: <%#Eval("ReorderLevel")%><br />
         </td>
         <td><%#Eval("Discontinued")%></td>
      </tr>
   </ItemTemplate>
</asp:ListView>

This ListView, when viewed through a browser, lists each product sorted by supplier name.

The products are sorted by supplier.

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 class attribute values for the table rows are alternating between data0 and data1, thereby giving the grid its zebra stripping. (In C# the syntax would be <%# Container.DataItemIndex % 2 %>.)

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">
   ...

   <ItemTemplate>
      

<%# AddGroupingRowIfSupplierHasChanged() %>



      <tr class='data<%# Container.DataItemIndex Mod 2 %>'>
         ...
      </tr>
   </ItemTemplate>
</asp:ListView>

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
Protected Function AddGroupingRowIfSupplierHasChanged() As String
   'Get the data field value of interest for this row
   Dim currentSupplierNameValue As String = Eval("CompanyName").ToString()

   'Specify name to display if dataFieldValue is a database NULL
   If currentSupplierNameValue.Length = 0 Then
      currentSupplierNameValue = "Unknown"
   End If

   'See if there's been a change in value
   If lastSupplierNameValue <> currentSupplierNameValue Then
      'There's been a change! Record the change and emit the table row
      lastSupplierNameValue = currentSupplierNameValue

      Return String.Format("<tr class=""group""><td colspan=""6"">Supplier: {0}</td></tr>", currentSupplierNameValue)
   Else
      'No change, return an empty string
      Return String.Empty
   End If
End Function

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.

Products, grouped by suppliers.

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>
<asp:DropDownList ID="GroupByField" runat="server" AutoPostBack="true">
   <asp:ListItem Value="CategoryName">Category</asp:ListItem>
   <asp:ListItem Value="CompanyName">Supplier</asp:ListItem>
   <asp:ListItem>Discontinued</asp:ListItem>
   <asp:ListItem Value="UnitsInStock">Units In Stock</asp:ListItem>
   <asp:ListItem Value="UnitsOnOrder">Units On Order</asp:ListItem>
   <asp:ListItem Value="ReorderLevel">ReorderLevel</asp:ListItem>
</asp:DropDownList>

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.
To dynamically sort the results by the selected group by data field we need to create an event handler for the data source control's 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
   e.Arguments.SortExpression = GroupByField.SelectedValue & ", ProductName"
End Sub

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
Protected Function AddGroupingRowIfNeeded() As String
   'Get the data field value of interest for this row
   Dim currentDataFieldValue As String = Eval(GroupByField.SelectedValue).ToString()

   'Specify name to display if dataFieldValue is a database NULL
   If currentDataFieldValue.Length = 0 Then
      currentDataFieldValue = "Unknown"
   End If

   'See if there's been a change in value
   If lastDataFieldValue <> currentDataFieldValue Then
      'There's been a change! Record the change and emit the table row
      lastDataFieldValue = currentDataFieldValue

      Return String.Format("<tr class=""group""><td colspan=""6"">{0}: {1}</td></tr>", GroupByField.SelectedItem.Text, currentDataFieldValue)
   Else
      'No change, return an empty string
      Return String.Empty
   End If
End Function

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 user can now select what data field to group the products by.

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!

  • By Scott Mitchell


    Further Readings:

  • Grouping Data with the ListView
  • Displaying Rows Grouped by Category
  • Accessing and Updating Data in ASP.NET 2.0: Examining the Data Source Control's Events
  • Building a Grouping Grid with the ASP.NET 3.5 LinqDataSource and ListView Controls
  • Attachments


  • Download the Demo (in ZIP format)
  • Article Information
    Article Title: ASP.NET.Using ASP.NET 3.5's ListView and DataPager Controls: Grouping By a Data Field
    Article Author: Scott Mitchell
    Published Date: September 17, 2008
    Article URL: http://www.4GuysFromRolla.com/articles/091708-1.aspx


    Copyright 2014 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers