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>
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.
