In ADO.NET dataset browse multiple related tables (5) Author: Microsoft www.ASPCool.com Time: 2003-1-23 17:39:24 Views: 6716
Creating a relationship Double-click the dsnorthwind.xsd file in the Solution Explorer (Solution Explorer). The file will be opened in the XML Designer (XML Designer). From the Toolbar's XML Schema tab, drag the RELATION to the order table. In the Edit Relation dialog box, set the following properties: Elements Set Name CustomersRDERS Parent Customers Child ORDERS Key Fields Customerid Foreign Key Fields Customerid Click OK to create a relationship and close the dialog. From the XML Schema tab of the toolbar, drag the Relation on the order list. In the Edit RELATION dialog box, set the following properties: Elements Set Name ORDERDETAILS PARENT ORDERS CHILDERDETAILS Key Fields ORDERID Foreign Key Fields ORDERID Click OK to create a relationship and close the dialog. From the XML Schema tab of the toolbar, drag the Relation on the order list. In the Edit Relation dialog box, set the following properties: Elements Set Name ProductsOrderDetails Key Fields Productid Foreign Key Fields ProductId Click OK to create a relationship and close the dialog. Save the project. The ordering schedule and the product table have been added to the data set, but you still need to add the code to fill them with data in runtime. With data padding table In Solution Explorer, right-click Form1 and select View Code from the shortcut menu. In the Form1_Load event handler, add the following code to the comment "Data Fill Table" and Daorders.Fill (DSNorthWind1) row: 'VisNorthWind1) Daproducts.Fill (dsnorthwind1) // C # DaorderDetails. Fill (DSNorthWind1); DAPRODUCTS.FILL (DSNorthWind1); populates the RTF text box with data Now you want to add a code to the project so that you can display all orders in the RTF text box when you select a certain order in the list box. The following code will call the getChildRowS method based on the selected order in the list box. All related records in the ordering schedule will be assigned to DRAORDETAILS arrays. The content of each data line will be displayed in the RTF text box.
Note: Please note how the nested for Each loop first selects a data line, then loops in all columns of the data line to access the entire relevant record. Set the RTF text box to display all orders in the 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 SELECTEDEXCHANGED event of the list box lborders. Add the following code: 'Visual Basic Private Sub lborders_selectedIndexchanged_ (Byval E AS System.EventArgs) _ handles lborders.selected IndexChanged' When you select a new order, clear the RTF text box. RTBDETAILS.CLEAR () declares an integer used to save the selected order ID. DIM SELECTEDORDERID AS INTEGER 'Sets the selected item to an integer. SelectEdOrderid = ctype (lborders.selectedItem, Integer) declares a data line for saving records of selected orders. DIM DRSELECTEDORDER AS DATAROW DRSELECTEDORDER = _ DSNorthWind1.Orders.FindbyOrderId (SELECTEDORDERID) 'Declare a data line array for saving 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 selecting a new order, clear the RTF text box. RTBDETAILS.CLEAR (); // Declare an integer used to save the selected order ID. INT selectedORDERID; // Set the selected item to an integer. SelectEdOrderid = (int) lborders.selectedItem; // Declare a data line for saving records of selected orders.
DataRow DrselectedOrder; DRSELECTEDORDER = dsnorthwind1.orders.findbyRDERID (SELECTEDOrID); // Declare a data line array for saving 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 ";}}}}} Save Item. Run the application. Select a order in the list box, and its order will be detained in the RTF text box. Select another order in the list box. The order in the RTF text box will be updated. Browse multi-to-multi-relational tables constitute multi-parallel-multiplier tables are usually connected by ensuring a third party table that guarantees data integrity. In the Rooler database, the order table and product table are related. Because some orders may contain a lot of products, and some products are sold in many orders. These two tables are connected by ordering, and the ordering table is used to establish their own specific columns using columns in both tables and associated with these data. Browsing three tables with multi-parallel relationships and tables that handle a pair of multi-relations are not much different. To browse many-to-many relationships, you can access the product based on a single record in the ordering schedule, which will return the product name and displayed in the order details. You can use the getParenTrow method to access the product name from your product table. Calling the getParentrow method will return a single data line, and the getChildRows method will return to the data line array (as shown in the example above). Get the product name from the order detail record In the Solution Explorer (Solution Explorer), right-click Form1 and select View Code from the shortcut menu. In the Selected IndexChanged event handler of list box (LBorders), add the following code between the FOR Each line: 'Visual Basic Details & = Product Name: "& _ CType (DRDETAILS.GETPARETROW (" ProductsOrderDetails ") _ (" ProductName "), string & controlchars.crlf // c # details =" Product Name: " DRDETAILS.GETPARENTROW (" ProductSorderDetails) ["ProductName"] "/ n"; save the project. Run the application. Select a order in the list box, the product name and details will be displayed in the RTF text box.
