An Extensive Examination of the DataGrid Web Control: Part 14By 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 |
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:
tblFAQCategorytable has a row for each of the FAQ categories, such as: Arrays, Strings, Dates and Times, Database Errors, and so on. The
tblFAQtable contains a record for each individual FAQ, and contains columns like
ViewCount, and so on.
To relate the
tblFAQCategory tables, a foreign key relationship is used.
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
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
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
As discussed in Part 13 of this article series, the
event fires after each new row in the DataGrid is created and has been bounded to the appropriate row
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
OnItemDataBound="buildFAQsDataGrid". This wires up the DataGrid's
ItemDataBound event to the
buildFAQsDataGrid event handler.
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
ItemType is either an
If we are dealing with an item or alternating item, we next create a new DataGrid,
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
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!