An Extensive Examination of the DataGrid Web Control: Part 14
By Scott Mitchell
The 14th Part in a Multi-Part Series |
---|
This article is the fourteenth piece of a multi-part series on using the DataGrid Web control that will span
several months. The ASP.NET DataGrid Web control, which displays database information in an HTML table, is
highly versatile. The basics of the DataGrid were discussed in Part 1;
information on specifying display properties of the DataGrid was discussed in
Part 2. In Part 3
we examined how to associate custom events with the DataGrid.
In Part 4 we looked at how to extend
Part 3 to provide custom sorting on the results of a DataGrid. In
Part 5 we examined how to use templates to further customize
the DataGrid's appearance. Part 6 examined how to use the
DataGrid's built-in editing capabilities, while Part 7 looked at
customizing the editing interface using the EditItemTemplate. In Part 8
we looked at how to add client-side code to a ButtonColumn's client-side onclick event.
In Part 9 we examined how to enhance the DataGrid's editing
interface by having the editing interface's TextBox receive focus when the page is loaded.
In Part 10 we looked at how to (automatically) add filtering
buttons so that one can filter the data in a DataGrid. In Part 11
we examined how to create a DataGrid Web control with a column of related radio buttons.
In Part 12 we examined how to create a sortable DataGrid
that can be sorted in ascending and descending order. Part 13
examined how to sum up a DataGrid column and have the sum displayed in the footer. This part looks
at how to build a master/detail DataGrid.
|
|
Introduction
A common relationship in database design is a one-to-many relationship. With a one-to-many relationship, a record in one table has zero to many related items, which are typically represented as records in a related table. For example, the ASPFAQs.com database has two tables:
tblFAQ
and tblFAQCategory
. The tblFAQCategory
table has a row for each of the FAQ categories, such as: Arrays, Strings, Dates and Times, Database Errors,
and so on. The tblFAQ
table contains a record for each individual FAQ, and contains
columns like Description
, Question
, ViewCount
, and so on.
To relate the tblFAQ
and tblFAQCategory
tables, a foreign key relationship is used.
The tblFAQCategory
table's primary key is FAQCategoryID
, and therefore serves
as a unique identifier for each FAQ category. The tblFAQ
table contains a foreign key
FAQCategoryID
, which assigns each FAQ to a particular FAQ category.
A master/detail report is a report type that Web developers for data-driven Web sites are commonly
asked to produce. A master/detail report is one that lists each high-level item - such as each FAQ
category - and beneath each high-level item lists the subitems that belong to that particular item -
such as all the FAQs of a particular category. In this article we'll examine how to create such
a master/detail report using the DataGrid. (An example master/detail report can be seen in the
screenshot on the right.)
Listing the FAQ Categories
Before we concern ourselves with how to list the various FAQs for each FAQ category, let's get the easy part of this task out of the way first - creating a DataGrid that lists each FAQ category. This is a fairly simple task, one that we've examined in previous installments of this article series.
The following code lists the categories from the tblFAQCategory
table. Note that our
SQL query retrieves both the primary key FAQCategoryID
column as well as the Name
column,
although only the Name
column is displayed in the DataGrid. This is because later, when
we want to include the FAQs for the particular FAQ category, we'll need to know the FAQCategoryID
column value.
|
Notice that the DataGrid has two columns, one for the FAQ category's name, and one for the FAQs for the particular category. In this second TemplateColumn we'll be programmatically adding a DataGrid that displays the FAQs for the particular FAQ category.
Displaying the FAQs for the Particular FAQ Category
Now that we have our DataGrid that displays the various FAQ categories, we need to enhance it so that the FAQ for each FAQ category is also displayed. To accomplish this we need to be able to programmatically add a DataGrid with the appropriate FAQs for the FAQ category. This can be accomplished by creating an event handler for the DataGrid's
ItemDataBound
event.
As discussed in Part 13 of this article series, the ItemDataBound
event fires after each new row in the DataGrid is created and has been bounded to the appropriate row
in the DataSource
. At this point, we can programmatically inspect the data bound to the
row (which will contain the FAQCategoryID
information we need), and alter the actual row's
contents. To add an event handler for the ItemDataBound
event, we simply need to create an
appropriate event handler and wire it up to the DataGrid's event. The following code demonstrates this:
|
The above code shows a couple things, and has a couple holes in it. First, note that the DataGrid
declaration includes: OnItemDataBound="buildFAQsDataGrid"
. This wires up the DataGrid's
ItemDataBound
event to the buildFAQsDataGrid
event handler.
The buildFAQsDataGrid
event handler starts by checking to see the type of item being
created. Note that the ItemDataBound
event fires when the DataGrid header and footers are being
created as well. Since we are only interested in the actual items and alternating items, we check to make
sure that the new DataGridItem
's ItemType
is either an Item
or
AlternatingItem
.
If we are dealing with an item or alternating item, we next create a new DataGrid, dg
.
This DataGrid will be used to display the FAQs for the particular category. Following this, we create
a local variable named CatID
, and assign to it the value of the FAQCategoryID
value from the DataSource
. Next, we call a function - which is not shown - called
GetFAQsByCategoryID(CategoryID)
, which presumably returns the FAQs for a particular
category. We then call the DataBind()
method and add the DataGrid to the TemplateColumn.
The above code, with the addition of a GetFAQsByCategoryID()
function, will do the trick.
However, it's horribly inefficient (assuming that the GetFAQsByCategoryID()
function makes
a database call that retrieves the FAQs for the particular category). Its inefficiency springs from
the fact that for each FAQ category, an entire new database access must transpire. That is, if
there are 15 FAQ categories, there will be 15 separate database queries. While each database query will
only return the needed records, it would be much more efficient to do one large database query at the
beginning, getting all of the FAQs, and then only binding the correct ones for each
FAQ category row. (The reason this approach is more efficient than the former approach is because
the majority of the "cost" of a database query is in setting up and tearing down the connection. We can
amortize this building up/tearing down cost by doing just one large query at the beginning.)
Of course, the problem we are now faced with is how to bind only a selective chunk of data to a DataGrid. We'll tackle this in Part 2 of this article.
Read Part 2!