Identity In Hierarchy | Programming MicrosoftВ® ADO.NET 2.0 Core Reference

[Previous] [Next]

The programmers I've spoken to who have worked with hierarchical recordsets have been pleased, overall, with their experiences. However, they frequently ask about one specific scenario: how do you build a hierarchy that uses a relationship based on an auto-incrementing identity field if you need to add new records to that hierarchy?

For example, imagine a hierarchy involving the Orders and Order Details tables in the Northwind database. The logical field on which to base a relationship is the OrderID field. Unfortunately, you'll encounter problems if you place new orders and details for those orders into this hierarchy. The Identity In Hierarchy example is designed to help you understand why this scenario has problems and to offer an alternative solution.

The Identity In Hierarchy sample works with the SQL Server OLE DB provider and ODBC driver with a Northwind database from SQL Server 7 or MSDE, or with the Jet 4.0 OLE DB Provider and a Northwind database in Access 2000 format. An important part of the sample will not work with databases created using previous versions of Access; the ADO cursor engine will be unable to retrieve the newly generated auto-increment identity values.

Like the Huge Hierarchy sample, the Identity In Hierarchy sample uses the modSettingsFile module to try retrieving the last successful connection string from a settings file. If this attempt fails, the sample displays the Data Link Properties dialog box to request a valid connection string to connect to a Northwind database and then saves that string in the settings file for future use.

Once the sample successfully connects to a Northwind database, it displays an MDI parent form with three menu options: Bad Hierarchy, Good Hierarchy, and Order Entry. If you click the first option, the sample displays a form called frmBadHierarchy, shown in Figure B-6. The code in this form opens a small orders and order details hierarchy—pulling the contents of two existing orders from the Northwind database and adding to the hierarchy two new orders (with two line items per order). Two bound grids display the two levels of the hierarchy. When the form appears, everything looks great. So why does the form name imply that this is a "bad" hierarchy?

click to view at full size.

Figure B-6. Select Bad Hierarchy to display the frmBadHierarchy form, titled Bad Hierarchy Based On Identity Field.

Let's look at the line items for the two new orders. Because these orders are pending in the hierarchy, they do not have a value for the OrderID field yet. If you're working with a SQL Server or an MSDE database and you click either of the pending orders, you'll notice the same four line items appear in the lower grid for both orders. If you're using the Jet 4.0 OLE DB Provider, selecting either pending order will display a blank line in the lower grid. (The difference in the behavior here is due to a difference in how the providers handle comparisons with null values.) Here's why: When you move to a new record in the orders level of the hierarchy, the data shape provider looks at the value of the OrderID field for the order and then makes visible the records in the order details level of the hierarchy with the same value in the OrderID field. Because these pending orders do not have a value for the OrderID field, there is no way for the data shape provider to locate only their line items. So what should you do? If only you could add a new field to each level of the hierarchy and base the relationship between the levels on that field. Fortunately, you can.

If you close the Bad Hierarchy form and click the Good Hierarchy menu option, the sample will display a form with a hierarchy that has a similar structure to the bad hierarchy. You'll quickly notice that each level of the hierarchy has a new field that contains a globally unique identifier (GUID). Now move to the pending orders and make sure that two line items are visible for each pending order. The sample uses this new field, which I call PseudoKey, rather than the OrderID field to set the relationship for the hierarchy.

NOTE
I chose a GUID field because you can request a GUID from Windows and know that you'll receive a unique value. You could base the relationship on an integer instead and simply keep track of the values you've used.

This is the query string that the "bad" hierarchy uses to retrieve its order data:

 SHAPE {SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipVia FROM Orders WHERE OrderID = 10250 OR OrderID = 10251} AS Orders

And here's the query string that the "good" hierarchy uses:

 SHAPE {SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipVia FROM Orders WHERE OrderID = 10250 OR OrderID = 10251} AS Orders APPEND NEW adGUID AS PseudoKey

Essentially, it's the same query with a blank field for handling GUID values. The entire query looks like this:

 SHAPE (SHAPE {SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipVia FROM Orders WHERE OrderID = 10250 OR OrderID = 10251} AS Orders APPEND NEW adGUID AS PseudoKey) APPEND ((SHAPE {SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details] WHERE OrderID = 10250 OR OrderID = 10251} AS OrderDetails APPEND NEW adGUID AS PseudoKey) RELATE PseudoKey TO PseudoKey) AS OrderDetails

Inserting new orders and line items into the hierarchy requires one additional step: generating a new GUID value for each order. Actually, that's the easy part. This type of hierarchy is great for adding new orders because you can supply a new value for the PseudoKey field. But the orders and line items that this query initially retrieves from the database have no value for the PseudoKey field.

After the code opens the hierarchical Recordset, it loops through the orders and supplies a value for the PseudoKey field based on the OrderID field. The code also loops through all the order details in the hierarchy (using the reshaping feature covered in Chapter 14) and supplies the appropriate value for the PseudoKey field in the order details as well. Without this (somewhat laborious) code, you would have problems viewing the line items of an existing order.

Only one small challenge remains. How do you submit new orders and line items? If you call the UpdateBatch method on a reference to the orders level of the hierarchy, you'll submit the new orders. But if you try to do the same thing with a reference to the order details level of the hierarchy, you'll receive an error because you haven't supplied values for the OrderID field of the new line items.

The third menu option on the MDI parent form is Order Entry. Click this menu option, and you'll see a simple form that displays four orders: two that already exist and two that are pending. You can view or edit the orders and add new orders. (Forgive the simplistic user interface for editing and adding orders.) This important code in this part of the sample appears in the Click event for the Submit Changes button. After submitting the changes in the orders level of the hierarchy, this code locates the newly submitted orders and then loops through the line items for each of these orders to set the value for the OrderID field.

NOTE
If you're wondering how ADO retrieves the value for the OrderID field of the new orders, go back to Chapter 11 and read the section on adResyncAutoIncrement. And don't even think about collecting $200 on your way there.

And with that, our quest is at an end. By clicking the Submit Changes button you now can see the new OrderID values for the orders you've added in the grid of orders. Click the Display Order button to see the line items for the order. The new orders won't appear in your database. The code wraps the calls to UpdateBatch in a transaction that it then rolls back, allowing you to run the sample over and over again.