Including Subheadings in a Datagrid

By Dave Long and Scott Mitchell

Introduction

A short while ago I was tasked with adding subheadings into a datagrid, something I have not tried to do, or even thought of doing prior to this assignment. Thus, the task was a little bit beyond my skillset. Having searched around the net for examples, I came up dry. I eventually solved this problem, so I thought I'd share with you how I accomplished adding subheadings to a DataGrid.

- continued -

First, let me define what I mean by subheadings. Oftentimes, the data displayed in a DataGrid can be logically grouped in some manner. For example, in Scott Mitchell's An Extensive Examination of the DataGrid Web Control, Scott displays the FAQs from the ASPFAQs.com database. Each of these FAQs belongs to precisely one FAQ category. In the Northwinds database's Products table, each product belongs to precisely one product category. In both of these examples, we can improve the DataGrid UI by showing how these FAQs or products are grouped by category. To illustrate this concept, see the screenshot below, which displays the products from the Northwinds database:

In this article we will examine how to build a DataGrid that provides this user interface. This article assumes you are familiar with DataGrids, databinding, and the DataGrid's ItemDataBound event. If this is not the case, consider reading An Extensive Examination of the DataGrid Web Control and/or the DataGrid FAQs at DataWebControls.com.

Building the HTML for the DataGrid with SubHeadings

First, create a new ASPX page called SubHeading.aspx and add a DataGrid. For this example, we'll display the products from the Northwinds database's Products table in the DataGrid, specifically showing three database fields in the DataGrid: ProductName, UnitPrice, and UnitsInStock. To display just these three fields, be sure that you set the DataGrid's AutoGenerateColumns property to False. Next, add the following markup to the DataGrid's <Columns> section:

<asp:BoundColumn DataField="ProductName" HeaderText="Product Name"></asp:BoundColumn> <asp:BoundColumn DataField="UnitPrice" HeaderText="Unit Price"></asp:BoundColumn> <asp:BoundColumn DataField="UnitsInStock" HeaderText="Stock Level"></asp:BoundColumn>

Next, you need to wire up the DataGrid's ItemDataBound event to the name of the ItemDataBound event handler we'll be creating shortly. To accomplish this, simply add ItemDataBound="DataGrid1_ItemDataBound" in the DataGrid's declaration. (We'll examine the code for this event handler in the next section.)

Finally, you can set some of the stylistic properties of the DataGrid, if you like. In my example, I have set the ItemStyle's BackColor to a light gray - #dedfde, and the HeaderStyle to a bold font with a purple background and white foreground.

When you are done, your HTML should look something like the following. The syntax in bold indicates important, required syntax:

<asp:datagrid id="DataGrid1" runat="server" AutoGenerateColumns="False" ForeColor="Black" BackColor="White" CellPadding="3" GridLines="None" CellSpacing="1" OnItemDataBound="DataGrid1_ItemDataBound"> <HeaderStyle font-bold="True" forecolor="White" backcolor="#4A3C8C"></HeaderStyle> <ItemStyle backcolor="#DEDFDE"></ItemStyle> <Columns> <asp:BoundColumn DataField="ProductName" HeaderText="Product Name"></asp:BoundColumn> <asp:BoundColumn DataField="UnitPrice" HeaderText="Unit Price"></asp:BoundColumn> <asp:BoundColumn DataField="UnitsInStock" HeaderText="Stock Level"></asp:BoundColumn> </Columns> </asp:datagrid>

Examining the Source Code for the DataGrid with SubHeadings

The source code portion for the DataGrid with subheadings requires two event handlers: one for the Page_Load event, and one for the DataGrid's ItemDataBound event. In the Page_Load event handler we will do three things:

Retrieve the results from our SQL query into a DataSet, ordered so that products of the same category appear next to each other,
Loop through the DataSet's default DataTable, and insert a new row into the DataTable at each point where there is a change from one product's category to the next, and
Bind the modified DataTable to the DataGrid

In the DataGrid's ItemDataBound event handler, we will check each row to see if the row is one of the rows signifying a change in category (i.e., one of the rows that we artificially inserted in the Page_Load event handler). If it is such a row, we'll display this row as a subheading.

Let's first turn our attention to the Page_Load event handler:

Sub Page_Load(Sender As Object, E As EventArgs) Handles MyBase.Load ' TODO: Update the ConnectionString to connect to you Own SQL server. Dim ConnectionString As String ConnectionString = "server=...;database=northwind;UID=...;PWD=..." Dim CommandText As String = "Select CategoryName, ProductName, " & _ "Cast(UnitPrice as varchar(50)) as UnitPrice, UnitsInStock " & _ "from Products " & _ " INNER JOIN Categories On " & _ " Products.categoryID = Categories.CategoryID " & _ "order by Products.categoryID" Dim myConnection As New SqlConnection(ConnectionString) Dim myCommand As New SqlDataAdapter(CommandText, myConnection) Dim ds As New DataSet myCommand.Fill(ds) 'Before DataBind - Add New Rows Here for Each Sub Heading 'We'll come back to this shortly! DataGrid1.DataSource = ds DataGrid1.DataBind() End Sub

The Page_Load event handler provided above will display a DataGrid displaying the Product Name, Price, and Units in Stock. The SQL query used to populate the DataSet retrieves the name of the product's category, the name of the product, and the product's price and number of units in stock. Note that to get the category name we need to join the Products table with the Categories table. Also, note that we are ordering the results by categoryID; this has the effect that all products of the same category will be listed together. Finally, realize that the UnitPrice field is converted to a varchar(50). We'll see why we did this in a moment.

While the DataGrid displays three DataSet fields - ProductName, UnitPrice, and UnitsInStock - the DataSet we populated contains a fourth field not included in the DataGrid (CategoryName). This field is needed to determine when we change from displaying products of one category type to another. Once this occurs, we want to display a subheading with the title specified by the CategoryName field.

In order to determine where these subheadings need to be inserted, we need to insert new rows into the DataSet's DataTable. To accomplish this, we'll iterate through all of the row's in the DataSet's DataTable. Each time there is a difference in the previous row's CategoryName field and the current row's CategoryName field, we'll insert a new row. This new row will serve as a placemarker for a subheading, which will be physically created in the DataGrid's ItemDataBound event.

The following code illustrates iterating through the DataSet's DataTable, inserting a row where needed. This code should be placed in the Page_Load event handler, where the comment 'We'll come back to this shortly! was placed.

Dim curCat As String 'The Current Category we are on Dim prevCat As String 'The category of the Previous Item Dim row As TableRow 'the New Row we need to Insert Dim i As Integer = 0 'An Index for specifying the Insertion Point. 'Loop through the rows of the DataTable Do While i <= ds.Tables(0).Rows.Count - 1 curCat = ds.Tables(0).Rows(i).Item("CategoryName") If curCat <> prevCat Then prevCat = curCat Dim shRow As DataRow = ds.Tables(0).NewRow shRow("ProductName") = ds.Tables(0).Rows(i).Item(0) shRow("UnitPrice") = "SubHead" ds.Tables(0).Rows.InsertAt(shRow, i) i += 1 End If i += 1 Loop

This code can be a bit difficult to understand at first glance. Essentially, we are looping through all of the rows in the DataSet's DataTable. At each row, we read the row's CategoryName field into a local variable, curCat. If curCat doesn't equal the CategoryName of the previous row, we set prevCat equal to curCat and insert a new row into the DataTable. Specifically, for this inserted row we set the row's ProductName field to the value of the current row's CategoryName and the inserted row's UnitPrice column to the string value SubHead.

The reason we set the inserted row's UnitPrice column to the string value SubHead is so that it serves as a marker. In the DataGrid's ItemDataBound event handler, we'll be looking for rows whose UnitPrice field equals SubHead; these rows will be displayed differently than other rows, as we'll see shortly. Recall from our examination of the Page_Load event handler that the UnitPrice field in the SQL query was converted to a varchar(50). This was done so that we could set it to a string value here (namely the string value SubHead). We could have optionally left this as a money field and instead set it to some negative number in the inserted row, but this only would work assuming that a product's price could never be negative.

All that remains left to write is the DataGrid's ItemDataBound event handler. This event handler inspects each row and dynamically adjusts its style in the event that the row is a subheader.

Private Sub DataGrid1_ItemDataBound(sender As Object, e As DataGridItemEventArgs) Select Case e.Item.ItemType Case ListItemType.AlternatingItem, ListItemType.Item 'Check the Row Type 'See if we have a Subheader! If e.Item.Cells(1).Text.Equals("SubHead") Then 'Format the data, and then align the text of each cell to the left. 'Set the cell to a ColSpan of 3 e.Item.Cells(0).ColumnSpan = 3 'Remove the two right cells e.Item.Cells.RemoveAt(2) e.Item.Cells.RemoveAt(1) 'Align the cell to the left, make its text bold, and set 'its background color e.Item.Cells(0).Attributes.Add("align", "Left") e.Item.Cells(0).Font.Bold = True e.Item.BackColor = Color.FromArgb(204,204,255) End If End Select End Sub

[View a Live Demo!]

That's all there is to it! Be sure to check out the live demo to see a working example and the complete source code.

Another Approach for Grouping Related Data...

In the FAQ Displaying Rows Grouped By Category, Scott Mitchell examines an alternate approach for displaying rows in a DataGrid grouped by category name.

Happy Programming!

By Dave Long and Scott Mitchell