Processing Parent-Child Relationships

Parent-child relationships are very popular in database applications. With this kind of relationship, you can dynamically specify a set of child rows that match a parent row. For example, choosing any row in a DataTable object based on the Orders table can lead to the return of a matching set of child rows from another DataTable object based on the Order Details table. You can use this type of relationship to dynamically populate a sub form based on the currently selected row in a main form. Alternatively, you can use the parent-child relationship to compute the sum of a field across individual child rows based on a parent row specified at run time. The samples for this section demonstrate two approaches to this second task with ADO.NET. The first approach uses an untyped dataset. The second approach uses a typed dataset.

Computing Child Values with an Untyped Dataset

The sample described in this section computes the total extended price for a range of up to 10 orders starting with an OrderID that the user specifies. The procedure looks up the parent row in one DataTable object for the OrderID that a user specifies and then computes the total extended price for the matching rows in a second DataTable object based on the Order Details table. Then, the procedure repeats this computation for up to the next nine rows in the Orders table. If the user inputs an out-of-bounds OrderID for the initial order, the application reminds the user to input an order that falls under the correct range. If the user inputs an OrderID that does not have nine rows behind it, the application computes the total extended price for as many parent rows as are available.

The Application s Form and Message Box

Figure 9-8 shows the Design view and initial operational view of Form2 in the DatasetForms project, which implements the application. The form has three controls ”a couple of buttons and a text box. A click to Button1 uses an untyped dataset to compute the total extended prices for a range of orders. A click to Button2 performs the same set of calculations with a typed dataset. This contrast s implications about the robustness of untyped datasets vs. typed ones are interesting. The text box on the form provides an easy mechanism for a user specifying the initial OrderID in a set of orders.

Figure 9-8: Design view and an initial operational view of a form to compute total extended price for a range of orders

The load event procedure for Form2 transforms the Design view into the initial operation view. This procedure formats the buttons and the text box. Because both buttons perform the same calculation but with different kinds of datasets, the button Text property assignments designate the type of dataset used for a calculation. Instead of using a Label control to designate what type of value to enter into TextBox1 , the form enters an initial value, Enter OrderID . The Form2_Load procedure also demonstrates how to specify the horizontal alignment for the value in a text box through an assignment of the TextAlign property of TextBox1 . IntelliSense offers a menu of three items for left, right, or center horizontal alignment.

 Private Sub Form2_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ˜Format button captions Button1.Text = "Untyped" Button2.Text = "Typed" ˜Format text box caption TextBox1.Text = "Enter OrderID" TextBox1.TextAlign = HorizontalAlignment.Right End Sub 

If a user inputs a value of 10248, the application returns a message box similar to the one shown in Figure 9-9. In this instance, the text box contains 10 sentences ”one for each of the orders from 10248 through 10257. Each sentence specifies the total extended price for an order. You can confirm the values by opening the Order Subtotals query in the Northwind database, which computes the total extended price for all orders in the database. The application computes the total extended price for no more than 10 orders at a time. In addition, it does so without putting any load on the database file because the computation applies to the application s two DataTable objects rather than being made against the original database. The ability to compute parent-child relationships dynamically further enhances the value of datasets.

Figure 9-9: A message box showing total extended price for a range of orders based on a parent-child relationship

Architecture of the Solution

The best view of the application s architecture is available from the module- level declarations behind Form2 and the Button1_Click procedure. The application has three module-level declarations. Two of these are for data adapters pointing at the Orders and Order Details tables in the Northwind database. As you know, the Northwind database relates these tables by their OrderID column values. Similarly, the two DataTable objects in the das1 dataset link to one another via their OrderID column values. All three of the objects declared at the module level get referenced by multiple procedures, which is why the application declares them at the module level.

Users launch the application with two steps. First, they input a value to TextBox1 . Second, they click one of the two buttons. For example, clicking Button1 causes the application to present a message box with up to 10 sentences reporting the total extended price for successive orders, including the order designated by the OrderID value in TextBox1 . The Button1_Click procedure performs two kinds of tasks . First, it sets up the main task. However, the most important role for the Button1_Click procedure is to invoke a pair of procedures that perform the most significant parts of the application. These called procedures perform the application s main task.

The Button1_Click procedure starts by clearing and then filling the Orders and OrderDetails DataTable objects in the das1 dataset. You might want to put steps such as these in a load event procedure for a form ” especially if the data does not change frequently. However, placing these steps in the Button1_Click procedure has the merit of consolidating all the actions for a single task. Next, the procedure specifies the PrimaryKey property values for the Orders and OrderDetails DataTable objects. Many tasks work better in ADO.NET when DataTable objects have primary keys. The last task performed directly by the Button1_Click procedure is to remove a DataRelation item in the Relations collection for the das1 dataset (if one exists). The application needs some way of recognizing an existing Relation item to freely create a new DataRelation object between Orders and OrderDetails DataTable objects. Automatically removing an existing Relation item between the DataTable objects is one way to accomplish this. You can build smarter approaches to this task, but this approach gets the job done without distracting attention from the main objective of the application.

So what is the main purpose of the application? Clearly, the intent is to compute total extended price for a range of up to 10 orders. In the context of this sample, how the application does this is as important as the fact that the application does it. The sample computes the extended prices based on a dynamically formed relation between a row in the Orders DataTable object and any matching rows in the OrderDetails DataTable object. By calling the AddParentChildRelation procedure, the Button1_Click procedure specifies a DataRelation object between the two DataTable objects in the das1 dataset. Next, the Button1_Click procedure calls the ExtendedPriceReport procedure. This procedure takes advantage of the DataRelation object created by the AddParentChildRelation procedure to compute a set of total extended prices. The word total is the most important one in the preceding sentence because total clarifies that the total extended price for all or most orders will be a sum across the multiple line items within an order. The Relation object specified in the AddParentChildRelation procedure designates which line items to use for computing the total extended price for a particular order.

Note  

One important reason for isolating the logic contained in the AddParentChildRelation and ExtendedPriceReport procedures is to facilitate the reuse of the code in a parallel version of this sample that uses a typed dataset.

 ˜Declare and instantiate two data adapters and a ˜dataset; point the data adapters at the Orders ˜and Order Details tables in the Northwind database Dim dap1 As New OleDb.OleDbDataAdapter( _ "Select * FROM Orders", _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft " & _ "Office\Office10\Samples\Northwind.mdb") Dim dap2 As New OleDb.OleDbDataAdapter( _ "Select * FROM [Order Details]", _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft " & _ "Office\Office10\Samples\Northwind.mdb") Dim das1 As New System.Data.DataSet() Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click ˜Fill Orders and OrderDetails DataTable objects ˜in the das1 dataset das1.Clear() dap1.Fill(das1, "Orders") dap2.Fill(das1, "OrderDetails") ˜Assign primary keys to the Orders and ˜OrderDetails DataTable objects based on ˜the OrderID and ProductID columns das1.Tables("Orders").PrimaryKey = _ New DataColumn() _ {das1.Tables(0).Columns("OrderID")} das1.Tables("OrderDetails").PrimaryKey = _ New DataColumn() _ {das1.Tables(1).Columns("OrderID"), _ das1.Tables("OrderDetails").Columns("ProductID")} ˜Remove any prior Relation; add Relation between ˜Orders and OrderDetails DataTable objects based ˜on OrderID columns in both DataTable objects If das1.Relations.Count > 0 Then das1.Relations.Remove(das1.Relations(0)) End If AddParentChildRelation(das1, "Orders", _ "OrderID", "OrderDetails", "OrderID") ˜Pass dataset and data relation name ExtendedPriceReport(das1, _ "OrdersOrderDetails") End Sub 

Adding a Relation Between Two DataTable Objects

The AddParentChildRelation procedure is beautiful in its simplicity. The procedure performs just one function ”it adds a parent-child DataRelation object between a pair of DataTable objects within a dataset. Because of its simplicity, this is the kind of procedure that you can readily adapt for inclusion in your own custom applications. You must pass five arguments to the procedure when calling it. These arguments specify the elements for creating a parent-child DataRelation object between a pair of DataTable objects in a dataset. The first argument is the dataset. The procedure accepts this argument with a type of System.Data.DataSet . The remaining four arguments are string variables naming the parent and child DataTable objects as well as the DataColumn object within each DataTable object that links to the other DataTable object.

The AddParentChildRelation procedure has just three short code blocks. First, it defines the DataColumn objects for both the parent and child DataTable objects to participate in the relation. Second, the procedure instantiates a DataRelation object between the parent and child DataTable objects. Third, the procedure adds the DataRelation object to the dataset that was passed into the procedure as an argument.

 Sub AddParentChildRelation( _ ByVal mydas As System.Data.DataSet, _ ByVal ParentTableName As String, _ ByVal ParentMatchColumnName As String, _ ByVal ChildTableName As String, _ ByVal ChildMatchColumnName As String) ˜Define parent and child columns for ˜a DataRelation object Dim parentcol As DataColumn = _ mydas.Tables(ParentTableName). _ Columns(ParentMatchColumnName) Dim childcol As DataColumn = _ mydas.Tables(ChildTableName). _ Columns(ChildMatchColumnName) ˜Instantiate a new DataRelation object Dim drl1 As DataRelation Dim str1 As String = _ ParentTableName & ChildTableName drl1 = New _ DataRelation(str1, parentcol, _ childcol) ˜Add the DataRelation object to the Relations ˜collection for mydas mydas.Relations.Add(drl1) End Sub 

Computing Extended Price for a Range of Orders

The ExtendedPriceReport procedure is mostly responsible for computing the extended price for a range of orders. The procedure computes the extended price for up to 10 orders from some starting point in the Orders DataTable . However, the procedure needs some help discovering what the right starting point is. The need arises from the fact the OrderID a user inputs to TextBox1 does not indicate which row number in the Orders DataTable object to use as starting point. Visual Basic .NET requires a transformation of the OrderID that points at a specific row in the DataTable object. Multiple approaches exist for performing this kind of task. The FirstRowIndex Function procedure implements one of these approaches. The ExtendedPriceReport invokes the FirstRowIndex Function procedure to find a starting row in the Orders DataTable object for computing a range of total extended prices.

The ExtendedPriceReport procedure starts by using the ParentTable and ChildTable methods to return the parent and child DataTable objects in the DataRelation object created for the das1 dataset by the AddParentChildRelation procedure. Next, after declaring some variables, the ExtendedPriceReport procedure invokes the FirstRowIndex procedure to return the row index value for the Orders DataTable object that matches the OrderID value in TextBox1 . The return value from the FirstRowIndex procedure indicates either an offset from the first row in the Orders DataTable object or an error. A value of -1 indicates the row is out of bounds, in which case the procedure displays an error message. If the return value from the FirstRowIndex procedure is an Integer greater than or equal to 0, a For...Next loop uses the value as a starting point for up to 10 iterations (the initial pass through the loop, plus up to nine more). In each pass, the first action is to extract a row from the Orders DataTable object ( tbl1 ). If the attempt to extract the row fails (for any reason, including no more rows being available), the procedure exits the loop.

After verifying a valid row for an index value in a Try Catch Finally statement, the sample code calls the GetChildRows method. This method takes the DataRelation object specifying the relation created in the AddParentChildRelation procedure as an argument. The method returns an array of DataRow objects ( ary_Rows ) with the subset of rows in the OrderDetails DataTable object matching the OrderID value in the designated Orders DataTable row ( drw1 ). A nested For Next loop for the rows in the ary_Rows array computes extended price for each line in an order and accumulates the result in a Decimal variable ( dec1 ). As you learned in Chapter 2, the Decimal data type in Visual Basic .NET replaces the old Currency data type from classic Visual Basic. For brevity, the extended price expression uses a numeric index value instead of a named index value for the columns. However, you can use named indexes; the column index values of 2, 3, and 4 represent UnitPrice, Quantity, and Discount, respectively. The last statement in the outer For...Next loop adds the OrderID and extended price as a sentence with a carriage return to the str1 string variable. After exiting the outer For...Next loop, a MsgBox function displays the string variable updated on each pass through the loop.

The FirstRowIndex function takes a DataTable object as an argument and returns either a -1 value or an Integer value pointing at a row in the DataTable object. By using the Find method, the procedure verifies whether the OrderID value in TextBox1 exists within the DataTable . The Find method requires a primary index for a DataTable object. If the Find method returns Nothing , the procedure sets its return value to -1 and exits. Otherwise, the procedure enters into a loop and searches for the row with a column value matching the numeric value denoted in TextBox1 . A For Next loop tracks each row search starting with a value of 0. The last value searched is the offset that the ExtendedPriceReport procedure needs as a starting row for computing the extended prices of orders. The procedure returns this value.

 Sub ExtendedPriceReport( _ ByVal mydas As DataSet, _ ByVal drlname As String) ˜Point DataTable variable at the parent ˜table (Orders) Dim tbl1 As DataTable Dim drl1 As DataRelation = _ mydas.Relations(drlname) tbl1 = drl1.ParentTable ˜Point another DataTable variable at ˜the child table (OrderDetails) Dim tbl2 As DataTable = drl1.ChildTable ˜Declare variables for looping through parent ˜and child rows Dim drw1 As DataRow Dim ary_Rows() As DataRow Dim int1, int2 As Integer Dim dec1 As Decimal Dim str1 As String Dim i, j As Integer ˜Save first row index for TextBox1, but ignore value if it is out of bounds i = FirstRowIndex(tbl1) If i = -1 Then MsgBox("OrderID too high or too " & _ "low. Please enter another " & _ "OrderID.", MsgBoxStyle.Exclamation, _ "Out-of-bounds message") Exit Sub End If ˜Attempt to loop through 10 rows starting ˜with the first row For j = i To i + 9 Try ˜Select a parent row drw1 = tbl1.Rows(j) Catch exc1 As System.Exception ˜Exit if less than 10 rows ˜follow the first row Exit For End Try ˜Return array of child rows matching selected parent row ary_Rows = drw1.GetChildRows(mydas.Relations(0)) ˜Loop through child rows to accumulate extended ˜price for child rows matching parent row For int1 = 0 To ary_Rows.GetUpperBound(0) dec1 += ary_Rows(int1)(2) * _ ary_Rows(int1)(3) * (1 - ary_Rows(int1)(4)) Next ˜Compute string to display computation across child ˜rows for parent rows int2 = drw1(0) str1 &= "Extended Price = " & _ Format(dec1.ToString, "Currency") & _ " for Order ID " & int2.ToString & _ "." & vbCr dec1 = 0 Next Display string with computations MsgBox(str1) End Sub Function FirstRowIndex( _ ByVal tbl1 As DataTable) As Integer ˜Variable declarations Dim drw1 As DataRow Dim int1 As Integer ˜Invoke Find method for first row drw1 = tbl1.Rows.Find(TextBox1.Text) ˜Find first row index If drw1 Is Nothing Then ˜Return for row not found Return -1 Else For int1 = 0 To tbl1.Rows.Count - 1 If tbl1.Rows(int1)(0) = _ CInt(TextBox1.Text) Then ˜Return index for found row Return int1 End If Next End If End Function 

Computing Child Values with a Typed Dataset

You can build the preceding solution with a typed dataset instead of an untyped dataset. One advantage of using a typed dataset is that you can create the dataset graphically. The graphical creation of a dataset such as the one in the preceding sample can be accomplished in just two steps. First, drag the Orders and Order Details tables from Server Explorer to the Dataset Designer. Second, drag a DataSet icon from the Data tab of the Toolbox to the Windows form ( Form2 ) that needs to reference the typed dataset. When you build the dataset graphically, you gain the benefit of having automatically created primary keys for your DataTable objects in the dataset. Therefore, you need to modify your setup code for the solution so that you do not attempt to re-create them with your code.

Graphically Creating a Dataset with Two DataTables

To create a dataset graphically with two DataTable objects, you need to use the Dataset Designer. Start to open the designer by choosing Project, Add New Item. Then, select DataSet in the Templates pane in the Add New Item dialog box and click Open . This opens the Dataset Designer. If you have already created the datasets described so far in this chapter, Visual Studio .NET will automatically suggest Dataset3.xsd as the filename for your dataset schema. However, you can use any name you choose with an .xsd extension. Simply revise the suggested filename for the schema file in the Name box on the Add New Item dialog box. The sample code does not refer to the dataset schema directly, but rather to a variable pointing at the schema. Therefore, you must coordinate the variable s name with the sample code.

After the Dataset Designer opens, click the Server Explorer link on the designer to show Server Explorer (if it is not already available). Then, drag over separately the Orders and Order Details tables from the Northwind connection to the Dataset Designer. That s all there is to it! The dataset is done. Figure 9-10 shows the Dataset Designer window between Server Explorer on the left and Solution Explorer on the right. Server Explorer appears with the Tables folder for the Northwind connection open. The Dataset Designer appears with the Orders table display stacked above the Order Details table display. Solution Explorer shows Dataset3.xsd selected because that object was used most recently.

Figure 9-10: Adding multiple tables to a dataset with the Dataset Designer

Because of your experience with the graphical Query Designer in Access (or other databases), you might be tempted to add a foreign key constraint between the tables by dragging from the OrderID column in the Order Details DataTable to the OrderID column in the Orders DataTable . This opens the Edit Relation dialog box in Visual Studio .NET. The dialog box in Figure 9-11 shows the Orders DataTable object as the parent table for the Order Details DataTable object. The OrderID columns in both DataTable objects apparently link the two DataTable objects. The settings in Figure 9-11 show a foreign key constraint named OrdersOrder_x0020_Details. (See the Name box in the figure.) Do not graphically add a foreign key constraint this way. Doing so can generate an error in the sample code; see the next section for the details and the workaround to the error. The Chapter Appendix describes the correct process for specifying a Relation object between two DataTable objects with the Dataset Designer in Visual Studio .NET.

Note  

Because some developers are likely to graphically add a foreign key constraint by dragging the OrderID column from the child DataTable to the parent DataTable , I purposely added it to the Dataset3.xsd file with the settings shown in Figure 9-11. My code sample removes the faulty specification if it exists. If the constraint does not exist, the sample code works properly.

Figure 9-11: If you specify a foreign key constraint by dragging a column from the child DataTable to a parent DataTable , the Edit Relation dialog box might look like this.

As you have learned, a dataset schema can act as a class for the definition of an object instance based on it. You can create an instance of the Dataset3 class by dragging a DataSet icon from the Data tab of the Toolbox and referencing DatasetForms.Dataset3 as the source for a typed dataset object. (See Figure 9-12.) This adds an icon named Dataset31 to the tray below Form2 in the DatasetForms project. Your code for the solution can refer to a variable named Dataset31 , just as the preceding sample referred to a variable named das1 to designate an untyped dataset instance.

Figure 9-12: To create a typed dataset, you can reference a DataSet in the current project.

Processing a Typed Dataset with a Foreign Key Constraint

If you perform the preceding steps for creating a typed dataset, you will have a dataset variable named Dataset31 pointing at a dataset defined by the selections in Figure 9-10 and Figure 9-11. To process this dataset and compute extended prices for a range of orders, you need a slightly different setup procedure than the one for the untyped dataset. On the other hand, the supporting procedures ( AddParentChildRelation , ExtendedPriceReport , and FirstRowIndex ) are used and remain completely unchanged. Therefore, by reviewing the Button2_Click procedure, you can appreciate all the special factors associated with processing the typed dataset.

The Button2_Click procedure for processing the typed dataset has two major design differences and one major operational difference from the Button1_Click procedure previously discussed for computing extended prices with an untyped dataset. The first design difference appears around the code for filling a DataTable object with rows from the Order Details table in the Northwind database. If you specify a foreign key constraint incorrectly (as many developers will be naturally inclined to do), an attempt to fill the child DataTable object, which is the Order Details DataTable in this sample, fails because of the throwing of a System.Data.ConstraintException object. Therefore, the procedure wraps the attempt to fill the Order Details DataTable object within a Try Catch Finally statement. If the procedure catches the System.Data.ConstraintException object, it attempts to recover by removing the default name for the foreign key constraint (see Figure 9-11) and attempting to refill the Order Details DataTable .

A second design distinction between the click event procedure for Button2 and the one for Button1 is that the Button2_Click procedure has no need to add a primary key. This is because the built-in code for generating the typed dataset automatically adds primary keys that match those in the original data source. When creating your own untyped dataset, it is necessary to explicitly add primary key constraints.

The third difference between the Button1_Click procedure and the Button2_Click procedure is an operational one. This distinction pertains strictly to the first pass through the click event procedure, and it applies only if an application designer graphically creates a foreign key constraint in the Order Details DataTable object. This foreign key constraint creates a DataRelation object that exists side by side with the constraint for the parent-child relationship. Removing the foreign key constraint does not remove the parallel DataRelation object for the parent-child relationship. Therefore, on the first pass through the click event procedure for Button2 , the procedure detects the DataRelation object for the foreign key and removes it. I call this an operational distinction because the same code exists in the Button1_Click procedure, but there is no DataRelation object to remove on the first pass through that click event procedure. Conditionally testing for a DataRelation object is relevant to either procedure after the first pass through the click event procedure. This is because the first pass through either procedure will always add a valid DataRelation object. If the code blindly added another object, it could fail because of an attempt to create a duplicate DataRelation object.

 Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Dim das3 As DataSet = Dataset31 ˜Clear dataset and fill Orders ˜DataTable object in the dataset das3.Clear() dap1.Fill(das3, "Orders") ˜Demonstrates problem with link between two ˜DataTable objects in the Dataset Designer; ˜cannot fill child DataTable object Try dap2.Fill(das3, "Order Details") Catch exc1 As System.Data.ConstraintException ˜Fix and recover from error; optionally , ˜uncomment MsgBox function to document ˜error ˜MsgBox(exc1.GetType.ToString & vbCr & _ ˜ exc1.Message) das3.Tables(1).Constraints. _ Remove(das3.Tables(1). _ Constraints( _ "OrdersOrder_x0020_Details")) dap2.Fill(das3, "Order Details") Catch exc1 As System.Exception MsgBox("Get help from your consultant." _ , , "Help") End Try ˜Remove any prior Relation; add Relation between ˜Orders and Order Details DataTable objects based ˜on OrderID columns in both DataTable objects If das3.Relations.Count > 0 Then das3.Relations.Remove(das3.Relations(0)) End If AddParentChildRelation(das3, "Orders", _ "OrderID", "Order Details", "OrderID") ˜Pass dataset and data relation name ExtendedPriceReport(das3, _ das3.Relations(0).RelationName) End Sub