To read the article online, visit http://www.4GuysFromRolla.com/articles/040203-1.aspx

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.

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

    Article Information
    Article Title: ASP.NET.An Extensive Examination of the DataGrid Web Control: Part 14
    Article Author: Scott Mitchell
    Published Date: April 2, 2003
    Article URL: http://www.4GuysFromRolla.com/articles/040203-1.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers