Browsing multiple related tables in a ado.net dataset (5)

ado| Data Creation Relationship

In Solution Explorer (Solution Explorer), double-click the Dsnorthwind.xsd file. The file opens in the XML Designer (XML Designer).
From the XML Schema (XML Schemas) tab of the toolbar, drag the relation (relationship) onto the order table.
In the Edit Relation dialog box, set the following properties:

Element settings
Name CustomersOrders
Parent Customers
Child Orders
Key Fields CustomerID
Foreign Key Fields CustomerID


Click OK to create the relationship and close the dialog box.
From the XML Schema (XML Schemas) tab of the toolbar, drag the relation (relationship) onto the order BOM.
In the Edit Relation dialog box, set the following properties:

Element settings
Name ordersorderdetails
Parent Orders
Child OrderDetails
Key Fields OrderID
Foreign Key Fields OrderID


Click OK to create the relationship and close the dialog box.
From the XML Schema (XML Schemas) tab of the toolbar, drag the relation (relationship) onto the order BOM.
In the Edit Relation dialog box, set the following properties:

Element settings
Name productsorderdetails
Parent Products
Child OrderDetails
Key Fields ProductID
Foreign Key Fields ProductID


Click OK to create the relationship and close the dialog box.
Save the project.
The Order BOM and product table have been added to the dataset, but you still need to add code to populate them with data at run time.

Populating tables with Data

In Solution Explorer (Solution Explorer), right-click Form1 and select View Code from the shortcut menu.
In the Form1_Load event handler, add the following code between the note "Populate Table with Data" and the daOrders.Fill (DSNORTHWIND1) Row:

' Visual Basic
Daorderdetails.fill (DSNORTHWIND1)
Daproducts.fill (DSNORTHWIND1)
C#
Daorderdetails.fill (DSNORTHWIND1);
Daproducts.fill (DSNORTHWIND1);



Populate rich text boxes with data
Now you want to add code to your project so that when you select an order in the list box, you can display all the order details in the rich text box.

The following code invokes the GetChildRows method based on the selected order in the list box. All related records in the order BOM are assigned to an array of data rows named draOrderDetails. The contents of each data row are displayed in the Rich text box.

Note: Note that the nested for Each loop is how to first select a data row and then loop through all the columns in the data row to access the entire related record.
Set up rich text boxes to display all order Details

In Solution Explorer (Solution Explorer), right-click Form1 and select View Designer from the shortcut menu.
Double-click the list box to create an event handler for the SelectedIndexChanged event lborders the list box.
Add the following code:

' Visual Basic
Private Sub lborders_selectedindexchanged _
(ByVal sender as System.Object, ByVal e as System.EventArgs) _
Handles lborders.selectedindexchanged
' Clears the rich text box when you select a new order.
Rtbdetails.clear ()
' Declares an integer to hold the selected order ID.
Dim Selectedorderid as Integer
' Sets the selected item to an integer.
Selectedorderid = CType (Lborders.selecteditem, Integer)

' Declares a data row that holds the record for the selected order.
Dim Drselectedorder as DataRow
Drselectedorder = _
DsNorthwind1.Orders.FindByOrderID (Selectedorderid)

' Declares a data row array to hold the related records.
Dim draorderdetails () as DataRow
draOrderDetails = _
Drselectedorder.getchildrows ("OrdersOrderDetails")

Dim details as String = ""
Dim Drdetails as DataRow
Dim Dcdetails as DataColumn
For each drdetails in draOrderDetails
For each dcdetails in DrDetails.Table.Columns
Details &= dcdetails.columnname & ":"
Details &= drdetails (dcdetails). ToString ()
Details &= Controlchars.crlf
Next
Details &= Controlchars.crlf
Next
Rtbdetails.text = Details
End Sub

C#
private void lborders_selectedindexchanged
(object sender, System.EventArgs e)
{
When you select a new order, clear the rich text box.
Rtbdetails.clear ();

Declares an integer to hold the selected order ID.
int Selectedorderid;
Sets the selected item to an integer.
Selectedorderid = (int) Lborders.selecteditem;

Declares a data row that holds the record for the selected order.
DataRow Drselectedorder;
Drselectedorder =
DsNorthwind1.Orders.FindByOrderID (Selectedorderid);

Declares an array of data rows to hold related records.
Datarow[] draOrderDetails;
draOrderDetails =
Drselectedorder.getchildrows ("OrdersOrderDetails");

String details = "";
foreach (DataRow drdetails in draOrderDetails)
{
foreach (DataColumn dcdetails in DrDetails.Table.Columns)
{
Details + + dcdetails.columnname + ":";
Details + = Drdetails[dcdetails]. ToString () + "\ n";
}
Details + = "\ n";
}
Rtbdetails.text = details;
}



Save the project.
Run the application.
Select an order in the list box, and its order details will appear in the Rich text box.
Select another order in the list box. The order details in the rich text box will be updated.
Browse Many-to-many Relationships
Tables that make up a many-to-many relationship are usually connected by a third-party table that guarantees data integrity. In the Northwind database, the Order table and the product table are related. Because some orders may contain many products, and some products are sold in many orders. The two tables are connected through the order list, and the order BOM uses the columns in the two tables to establish its own specific columns and correlate the data. Browsing the three tables that make up a many-to-many relationship is not much different from the table that handles a one-to-many relationship.

To browse a many-to-many relationship, you can access the product based on a single record in the order BOM, which returns the product name and appears in the order details.

You can use the GetParentRow method to access product names from the product table. Calling the GetParentRow method returns a single row of data, and the call to the GetChildRows method returns an array of data rows, as shown in the previous example.

Get the product name from the order detail record

In Solution Explorer (Solution Explorer), right-click Form1 and select View Code from the shortcut menu.
In the SelectedIndexChanged event handler for the list box (lborders), add the following code to the for each row:

' Visual Basic
Details &= "Product Name:" & _
CType (Drdetails.getparentrow ("ProductsOrderDetails") _
("ProductName"), String) & Controlchars.crlf
C#
Details + = "Product Name:" +
Drdetails.getparentrow ("ProductsOrderDetails") ["ProductName"]
+ "\ n";



Save the project.
Run the application.
Select an order in the list box.
The product name and details are displayed in the Rich text box. The form now displays related data from all four tables.



Figure 2: A form showing the product name and order Details

Select another order in the list box. The order details in the rich text box will be updated.