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. - continued - Read Part 15 Read Part 16 Read Part 17 Read Part 18

ASP.NET Data Web Controls Kick Start ASP.NET Data Web Controls Kick Start is author Scott Mitchell's most recent book, which thoroughly examines three of the most commonly used ASP.NET Web controls: the DataGrid, DataList, and Repeater. These three Web controls can be difficult to master due to their numerous features and capabilities. With this book, you'll quickly become an expert, learning the gritty details and true capabilities of each. This 400+ page book explores the topics in this article series in much greater depth, along with examining various topics and techniques not covered here. Scott Mitchell is the editor and founder of 4GuysFromRolla.com, author of the An Extensive Examination of the DataGrid Web Control article series, and author of numerous other ASP and ASP.NET books. [Buy this Book] [Visit the Book's Companion Web Site]

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.

<%@ Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.SQLClient" %> <script language="VB" runat="server"> Sub Page_Load(sender as Object, e as EventArgs) BindData() End Sub Sub BindData() '1. Create a connection Dim myConnection as New _ SqlConnection(ConfigurationSettings.AppSettings("connectionString")) '2. Create the command object, passing in the SQL string Dim strSQL as String = _ "SELECT FAQCategoryID, Name FROM tblFAQCategory ORDER BY Name" Dim myCommand as New SqlCommand(strSQL, myConnection) 'Set the datagrid's datasource to the datareader and databind myConnection.Open() dgMasterDetail.DataSource = _ myCommand.ExecuteReader(CommandBehavior.CloseConnection) dgMasterDetail.DataBind() myConnection.Close() End Sub </script> <asp:DataGrid runat="server" id="dgMasterDetail" AutoGenerateColumns="False" ...> <Columns> <asp:BoundColumn DataField="Name" HeaderText="FAQ Category" /> <asp:TemplateColumn HeaderText="FAQs"> <ItemTemplate> <i>The FAQs for the FAQCategory will go here... </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid>

[View a Live Demo!]

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:

<script language="VB" runat="server"> ... Sub buildFAQsDataGrid(sender as Object, e as DataGridItemEventArgs) If e.Item.ItemType = ListItemType.Item OR _ e.Item.ItemType = ListItemType.AlternatingItem then 'Build the DataGrid Dim dg as New DataGrid() 'Find out the CategoryID Dim CatID as Integer = e.Item.DataItem("FAQCategoryID") 'Bind the Data to the DataGrid dg.DataSource = GetFAQsByCategoryID(CatID) dg.DataBind() 'Add the DataGrid to the 2nd Column e.Item.Cells(1).Controls.Add(dg) End If End Sub </script> <asp:DataGrid runat="server" id="dgFAQsByCategory" AutoGenerateColumns="False" Font-Name="Verdana" Width="85%" Font-Size="11pt" HorizontalAlign="Center" ShowFooter="True" OnItemDataBound="buildFAQsDataGrid"> ... </asp:DataGrid>

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!