Relations Between Tables in an XML Document

Creating a parent-child relation between a pair of tables is common in database applications. This section describes how to construct such a relation graphically with the Dataset Designer. As confirmation that the relation exists, the section concludes with a simple Visual Basic .NET piece of sample code. The logic in the sample applies the relation to generate an order history for a customer that pulls rows from an Orders DataTable based on a currently selected row in the Customers DataTable .

Graphically Creating a Relation Between Two Tables

Graphically linking two tables is a somewhat different process with Visual Studio .NET than with the Access Query Designer. If you try to graphically link two tables in Visual Studio .NET as you would with Access, the process will not work. This section carefully describes an approach to graphically linking tables in Visual Studio .NET. This approach automatically creates an XML schema because the dataset created by the process is a typed dataset.

The sample links local versions of the Customers table to the Orders table. As you will see, this kind of link is convenient for generating an order history for any given customer. Start by creating a new Windows form, Form2 , in the XMLSamples project and make Form2 the new startup object for the project. Using a separate form makes it easier to keep track of the projects in the tray below the form.

Note  

You can assign a form as the startup object for a project by right-clicking the project in Solution Explorer and choosing Properties. Select the General item below Common Properties in the project s Property Pages dialog box. Click the drop-down box below the Startup Object label, and select an object such as Form2 to invoke automatically when the project launches. Click OK to confirm your selection and close the dialog box.

Populate Form2 with its first dataset based on the Customers table in the Northwind database. Instead of naming the dataset dsCustomers , name it dsCustomersOrders . If you use the drag-and-drop techniques described in the preceding sample with the Shippers table from the Northwind database, Visual Studio .NET creates objects named OleDbConnection1 , OleDbDataAdapter1 , and DsCustomersOrders1 . The DsCustomersOrders1 variable in the tray points at the dsCustomersOrders.xsd dataset listed in Solution Explorer.

Despite the name for the DsCustomersOrders1 dataset object, the object represents data from only the Customers table at this point. To add data from the Orders table, we need an OleDbDataAdapter object pointing at that table. Dragging the Orders table from the Northwind database connection in Server Explorer to Form2 adds a second data adapter, OleDbDataAdapter2 , to the tray below the form. This second data adapter uses the OleDbConnection1 object initially created with the OleDbDataAdapter1 object.

Now, the application needs to set up the OleDbDataAdapter2 object as an interface between the Orders table and the dsCustomersOrders dataset. Right-click the OleDbDataAdapter2 object, and choose Generate Dataset. Instead of clicking the New radio button, click the Existing button to reference an existing dataset. Designate the dsCustomersOrders dataset in the current project, which is XMLSamples. In the Choose Which Table(s) To Add To The Dataset box, make sure that the Orders table is selected. Figure A-6 shows OleDbDataAdapter2 in parentheses after the Orders table to indicate that this data adapter acts as a conduit for the data source to the dataset. Click OK to close the Generate Dataset dialog box.

Figure A-6: The Generate Dataset dialog box for adding the Orders table as a data source to the dsCustomersOrders dataset in the XMLSamples project

After specifying a dataset for the OleDbDataAdapter2 object, the dsCustomersOrders.xsd file has two tables, Customers and Orders. You can view these tables graphically in the schema by double-clicking dsCustomersOrders.xsd in Solution Explorer. Now open or select the Toolbox. Select the Relation object from the Toolbox, and drag it to the Orders table. In general, you will add the Relation object to the child table in a relation. Visual Studio .NET responds by opening the Edit Relation dialog box. (See Figure A-7.) This dialog box automatically designates CustomerID as the key field from the Customers element and CustomerID as the foreign key field from the Orders element. Notice that I said Customers element and not Customers table. That s because the schema uses an XML element to represent the Customers table from the Northwind database in the dsCustomersOrders.xsd dataset. For this application, you can accept the other default settings as well. In fact, these settings are robust, so you can accept them in most situations. Click OK to form the relation between the Customers and Orders elements.

Figure A-7: The Edit Relation dialog box for designating a parent-child relation between the Customers and Orders elements in the dsCustomersOrders typed dataset

Note  

The Default selection for the Update Rule, Delete Rule, and Accept/Reject Rule on the Edit Relation dialog box pertains to referential integrity settings. See the Creating DataRelation Objects with the XML Designer topic in the Visual Studio .NET documentation for additional detail on the settings.

After clicking OK in the Edit Relation dialog box, Visual Studio .NET updates the Dataset view of the dsCustomersOrders dataset by adding a connection to depict a one-to-many relationship between the Customers element and the Orders element. In addition, the XML schema code updates to reflect the relation between the two tables. Closing the dsCustomersOrders.xsd file and saving the changes completes the process of graphically relating the two tables.

Using a Graphically Created Relation

Form2 has a single button ( Button1 ) on it. The sample application for using the relation uses the Form2_Load and Button1_Click procedures. This application generates the history of orders for the first customer in the Customers DataTable . With the default primary key index, this customer has a CustomerID value of ALFKI .

The Form2_Load procedure performs two kinds of tasks . First, it fills the two DataTable objects in the DsCustomersOrders1 dataset from the Northwind database. Even though I have reached the end of the book, I still find it easy to code as though DataTable objects magically fill themselves . Of course, this is not the case. Therefore, you will always need to fill DataTable objects before you can report on their contents. Second, the form load event procedure formats Button1 by assigning a Text property value and increasing the value of the control s Width property so that it does not clip the Text property value when the form displays.

 Private Sub Form2_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Fill local dataset from source database OleDbDataAdapter1.Fill(DsCustomersOrders1, "Customers") OleDbDataAdapter2.Fill(DsCustomersOrders1, "Orders") Format Button1 Button1.Text = "ALFKI Order History" Button1.Width += 50 End Sub 

The trick to using the relation is to understand how the GetChildRows method works. This method returns an array of DataRow objects based on a row in the parent table. The array of returned rows contains the matching child rows for the selected row in the parent table. In order to complete the specification of the method, you must designate the name of the Relation object. The following listing demonstrates how this procedure works. The Computing Extended Price for a Range of Orders section in Chapter 9 gives this method additional coverage.

The following listing is simple, but it demonstrates the basics of using a graphically specified Relation object. In the declaration area at the top of the procedure listing, the code assigns a value of 0 to the int1 variable to designate the first row in the parent data source (the first row from the Customers DataTable ). Next, the GetChildRows method populates the ary_Rows array with rows from the Orders DataTable that have a CustomerID value of ALFKI (or whatever the CustomerID value corresponds to in the currently designated row in the Customers DataTable ). Then, an assignment statement creates a header for a string that populates a message box with the order history. The statement computing the header must appear after populating the ary_Rows array because the header refers to the array. A For loop iterates through the array and populates the str1 string with the OrderID and OrderDate column values for each order from the currently selected parent row in the Customers DataTable . Finally, a message box presents the results. (See Figure A-8.) By changing the assignment for int1 , you can generate order histories for other customers.

Figure A-8: A message box based on a graphically defined relation that shows the order history for a customer whose CustomerID is ALFKI

 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Use int1 for target row from parent table (Customers), int2 for enumerator of child rows (Orders), and ary_Rows to hold collection of child rows; str1 stores selected column values from child row collection Dim int1 As Integer = 0 Dim int2 As Integer Dim ary_Rows() As DataRow Dim str1 As String Compute child rows for int1 value ary_Rows = _ DsCustomersOrders1.Customers(int1). _ GetChildRows("CustomersOrders") Define header for MsgBox str1 = "Order history for " & _ ary_Rows(0)("CustomerID") & vbCr & vbCr Loop through child rows and store selected column values in str1 For int2 = 0 To ary_Rows.GetUpperBound(0) str1 &= ary_Rows(int2)("OrderID") & ", " & _ ary_Rows(int2)("OrderDate") & vbCr Next Display selected column values MsgBox(str1) End Sub