The Relations1 Project

Team Fly

Page 539

The listing is a bit lengthy because it calculates totals, formats the cells of the ListView control, and so on. We'll focus on the statements that navigate through the hierarchy of the DataSet's rows. When the user selects an item in the list, the following actions are performed from within the control's SelectedIndexChanged event handler:

1. The detail lines that refer to the selected product are copied from the Order Details DataTable into an array of DataRow objects with the DataTable's Select method:

 DetailRows = _     ProductSales1.Order_Details.Select(''ProductID = " & productID)

The DetailRows array contains all the rows of the Order Details DataTable that refer to the product whose ID we passed to the Select method as argument.

2. The program creates a new DataTable, the OrdersTable, with the same structure as the original Orders DataTable of the DataSet. This DataTable will store all the rows of the Orders DataTable that correspond to the details selected in step 1. The following statements iterate through the rows of the DetailRows DataTable, retrieve the order to which the detail belongs, and add it to the OrdersTable DataTable. The GetParentRow method accepts as argument the name of the relation between the Order Details and Orders tables.

 For Each DetailRow In DetailRows     OrderRow = DetailRow.GetParentRow("OrdersOrder_Details")     OrdersTable.Rows.Add(OrderRow.ItemArray) Next

3. The OrdersTable DataTable now contains the orders that include the product selected on the ListBox control. Another loop iterates through the rows of this DataTable and displays them on the ListView control. In addition, it keeps track of the number of orders placed by each customer and the total amount spent by each customer for the selected product.

4. To retrieve the customer name from each order, the program calls the FindByCustomerID method of the Customers DataTable, passing as argument the customer's ID with the following statements. The CustomerRow variable is of the ProductSales.CustomersRow type.

 CustomerID = OrderRow.Item("CustomerID") CustomerRow = _           ProductSales1.Customers.FindByCustomerID(CustomerID)

The remaining statements populate the ListView control, calculate the totals, and perform other straightforward tasks.

The Relations project demonstrates an interesting alternative to the DataGrid control for building interfaces that display related data. It involves quite a bit of code, as opposed to the DataGrid, but you have absolute control over the appearance of the data and you can display all the levels of your data hierarchy.

Figure 18.7 shows an application that maps more complicated relations on a ListView control. The Relations1 project maps the publishers of the Pubs database, along with their titles and each title's

Team Fly