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