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

An Extensive Examination of the DataGrid Web Control: Part 14, Part 2

By Scott Mitchell


  • Read Part 1

  • In Part 1 we examined one way to create a master/detail report, but this approach sufferred from some inefficiency. In this final part, we'll examine how to surmount these inefficiencies using a DataSet and DataView.

    Filtering Data with a DataView


    Our new challenge is to be able to read in all of the FAQs and then selectively bind FAQs that meet certain criteria to a DataGrid. For example, when the ItemDateBound event fires for the row corresponding to the Arrays FAQ category (which has a FAQCategoryID of 1), we want to bind just those FAQs whose FAQCategoryID column equals 1 as well. Fortunately, this task is quite easy to accomplish with the use of the DataSet and a DataView.

    The DataView object in the .NET Framework allows for data to be viewed in a manner other than the way it's presented. For example, if we had a DataSet with a list of FAQs, we might want to view the data as if it was sorted upon some particular column. Or we might want to only view those FAQs that meet a certain criteria. We can accomplish this using a DataView. To get a filtered view of data, we use the DataView's RowFilter property. Specifically, we specify the WHERE clause we want to apply to the data. So, to see just the FAQs whose FAQCategoryID equals 1, we could set the RowFilter property to: FAQCategoryID = 1.

    A DataSet is made up of one or more DataTables. Each DataTables has a DefaultView, which is a DataView object instance. We can create a new DataView, assign it to the DataTable's DefaultView, set this new DataView's RowFilter property, and then bind the DataView to the programmatically created DataGrid. Following these sequence of steps, the "details" DataGrid created for each "master" row will contain just those "detail" subitems than belong to the "master" item.

    The updated code for the buildFAQsDataGrid event handler looks as follows:

    <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")
          
          'Create a DataView that has only the applicable FAQs
          Dim properFAQs as DataView = FAQsDS.Tables("FAQs").DefaultView
          properFAQs.RowFilter = "FAQCategoryID=" & CatID
          
          'Bind the Data to the DataGrid
          dg.DataSource = properFAQs
          dg.DataBind()
          
          'Add the DataGrid to the 2nd Column
          e.Item.Cells(1).Controls.Add(dg)
        End If  End Sub
    </script>
    

    The text in bold is the text that has changed since the previous incarnation of this event handler. Note that we create a DataView, properFAQs, and assign it to the DefaultView of the FAQs table in the FAQsDS DataSet. (We will examine how to populate this DataSet shortly.) Next, the DataView's RowFilter property is set according to the value of the FAQCategoryID. Lastly, the DataView is assigned to the DataGrid's DataSource property, the DataGrid's DataBind() method is called, and the DataGrid is programmatically added to the master DataGrid's TemplateColumn.

    All that remains unanswered is where the FAQsDS DataSet comes from and how it is populated. With this improved approach to a master/detail report, we need to also rework our BindData function. Rather than simply getting just the FAQ categories, we need to get all of the FAQ categories as well as all of the FAQs. Furthermore, we need to create a DataSet instance (FAQsDS) that is accessible to all functions on this ASP.NET Web page. These aims are accomplished with the following code:

    <script language="VB" runat="server">
      ...	
    	
      Dim FAQsDS as New DataSet()
    	
      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 strCategorySQL, strFAQSQL as String 
        strCategorySQL = "SELECT FAQCategoryID, Name " & _
                         "FROM tblFAQCategory ORDER BY Name"
        
        strFAQSQL = "SELECT FAQID, FAQCategoryID, Description, ViewCount " & _
                    "FROM tblFAQ ORDER BY Description"
                    
        Dim myCatCommand as New SqlCommand(strCategorySQL, myConnection)
        Dim myCatDA as New SqlDataAdapter(myCatCommand)
        
        Dim myFAQCommand as New SqlCommand(strFAQSQL, myConnection)    
        Dim myFAQDA as New SqlDataAdapter(myFAQCommand)
        
        'Fill the dataset
        myConnection.Open()
        myCatDA.Fill(FAQsDS, "Categories")
        myFAQDA.Fill(FAQsDS, "FAQs")
        myConnection.Close()
        
        'Bind the Categories DataSet to the DataGrid
        dgFAQsByCategory.DataSource = FAQsDS.Tables("Categories")
        dgFAQsByCategory.DataBind()
      End Sub
    </script>
    
    [View a Live Demo!]

    First, notice that a DataSet object instance, FAQsDS, is created in global scope, so that all functions in this ASP.NET Web page can refer to the DataSet. In the BindData() function, two SQL queries are formulated: one to retrieve all of the FAQ categories, and the other to retrieve all of the FAQs. Two DataTables are added to the FAQsDS DataSet, one denoted Categories and the other denoted FAQs. The Categories DataTable is then bound to the "master" DataGrid.

    Be sure to check out the live demo to see the master/detail report in action.

    Conclusion


    In this article we examined how to create a master/detail report using a DataGrid. This involved creating an event handler for the "master" DataGrid's ItemDataBound event, and then programmatically creating and adding a "detail" DataGrid for each row of the "master" DataGrid. To improve performance, rather than issue a separate SQL query for each row, we retrieved all of the FAQs from the tblFAQ table in the BindData() function, and then used a DataView to display only the appropriate FAQs for each FAQ category row.

    Happy Programming!

  • By Scott Mitchell

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


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