An Extensive Examination of the DataGrid Web Control: Part 14, Part 2By Scott Mitchell
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
ItemDateBoundevent fires for the row corresponding to the Arrays FAQ category (which has a
FAQCategoryIDof 1), we want to bind just those FAQs whose
FAQCategoryIDcolumn 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
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
RowFilter property to:
FAQCategoryID = 1.
A DataSet is made up of one or more
DataTables has a
DefaultView, which is a DataView object instance. We can create a new DataView, assign
it to the
DefaultView, set this new DataView's
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:
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
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
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:
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
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
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.
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
ItemDataBoundevent, 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
tblFAQtable in the
BindData()function, and then used a DataView to display only the appropriate FAQs for each FAQ category row.