Huge Hierarchy

[Previous] [Next]

Although Chapter 14 covers hierarchical recordsets in some depth, I wanted to build a sample that illustrates how helpful they can be in certain situations. The Huge Hierarchy sample retrieves information from the sample Northwind database into, well, a huge hierarchy.

Before we talk about the structure of the hierarchy, let's briefly talk about how the code connects to your database. The Huge Hierarchy sample uses a copy of the modSettingsFile module to interact with a local settings file. The connection string that was last used successfully is stored in this settings file. If you are running this sample for the first time or if you couldn't connect to your database the last time you ran this sample, the initialization code displays the familiar Data Link Properties dialog box, prompting you to indicate the location of a Northwind database. (If you don't have SQL Server 7 or Microsoft Data Engine [MSDE] installed and you don't have a copy of the sample Microsoft Access database, you can create your own copy with the Make Northwind sample described later in this appendix.) You don't have to specify in the Data Link Properties dialog box that you plan to use MSDataShape (the OLE DB provider for data shaping). The code in the sample handles that for you.

Once you connect to a Northwind database, the Huge Hierarchy sample displays a multiple-document interface (MDI) parent form that lists the available customers in a drop-down list box. Select a customer, and click the Go button to display the hierarchy of information on the customer's orders in a new MDI child form, as shown in Figure B-4.

Figure B-4. Hierarchy containing a customer's orders.

Now let's talk about the hierarchy itself. This sample retrieves all order information for a customer into a hierarchical recordset and displays that data on a form. That customer's orders are grouped first by the employee who took the order. For each employee, you can see information about that employee, the number of orders that the employee placed for this customer, and the combined total for those orders. You can navigate through the employees who placed orders for this customer by using the horizontal scroll bar that appears above the employee information.

Below the employee information, the sample displays in a grid each of the orders the employee placed for this customer. This information includes the total cost of the order and the total number of items in the order—information that does not appear in the Northwind database's Orders table. The data shape provider's aggregation features let you gather this information from each order's child data, pulled from the Order Details table. Because each order can be sent through a different shipper, information about the shipper that's handling the selected order appears beneath the grid of orders. The sample also displays the items in each order in a grid beneath the shipper information, as well as information about the product ordered.

The MDI child form that displays all this information is called frmHugeHierarchy, and the code that generates the hierarchical Recordset is located in the form's Load event. Figure B-5 breaks down this mammoth query, making it easier to follow.

Figure B-5. Hierarchical query and its syntax.

Figure B-5 uses the shorthand <Orders Query>—rather than including that portion of the query—to save space and make the hierarchical query easier to follow. Here are those queries, copied from the Form_Load procedure:

 strOrdersQuery = "SELECT OrderID, CustomerID, OrderDate, " & _ "EmployeeID, ShipVia, Freight, ShippedDate " & _ "FROM Orders WHERE CustomerID = '" & CustomerID & "'" strOrderDetailsQuery = "SELECT OrderID, ProductID, Quantity, " & _ "UnitPrice, Discount, " & _ "((Quantity * UnitPrice) - Discount) " & _ "AS ItemTotal FROM [Order Details] " & _ "WHERE OrderID = ?" strProductsQuery = "SELECT ProductID, ProductName, " & _ "QuantityPerUnit FROM Products" strShippersQuery = "SELECT ShipperID, CompanyName, Phone FROM Shippers" strEmployeesQuery = "SELECT EmployeeID, FirstName + ' ' + LastName " & _ "AS EmployeeName, Title FROM Employees" 

I used a parameterized query for the order detail information so that I retrieve only the details in the orders for the customer, thus improving performance and minimizing the amount of data to store in the hierarchy. For the queries to retrieve product, shipper, and employee information, I simply retrieved the entire table. Why? I could have used parameterized queries for this information. However, I estimated that a large percentage of the data in the Products, Shippers, and Employees tables would be retrieved in each hierarchy, and therefore I would see no benefit in using the parameterized syntax.

Once the code executes the query to generate the hierarchical recordset, all that's left to do is bind controls to the different levels of the hierarchy. As I've mentioned elsewhere in this book, I'm generally not a big fan of bound controls. However, in this sample they're used only to display the contents of the recordset, and they've greatly simplified the code in the application.

While the hierarchical query itself is very complex, the code in the form is incredibly simple: open a hierarchical recordset, obtain references to the different levels of the hierarchy, and bind controls to those levels. That's it. Look at the amount of information on that form, and then think about how little code it took to retrieve and display it all. And that's the point of the sample—in some situations, hierarchical recordsets can greatly simplify your code.