Sync 3 related tables on a form

Hi,
You can try to maintain a multi query join string for the same purpose.
"select a.col1, b.col2, c.col3 from a,b,c where a.col1=b.col2 and.... etc.
then use the dataset return to work around with it. .NET 2003 has the added
functionality

I add this additional code snippet to use in a vb.net environment. you
might want to consider it as well. In this walkthrough you will create a
dataset to validate data in a one-to-many relationship (a one-to-many
relationship can also be called a keyed relationship). It will take you
through the process of creating a new Visual Basic or Visual C# project and
adding a new Dataset item to it. Then you will create complex type elements
that represent data tables with columns. You will learn how to define
primary keys in tables. Finally, you will learn how to create relationships
between tables based on the defined keys. When complete you will be able to
examine the underlying XML code that was created by the XML Designer, in
order to understand how datasets express keyed relationships in native XML.

This walkthrough will include creating tables, primary keys, and
relationships and will consist of the three major sections outlined below:

Add a Dataset to a project and create three tables.
Create a Customers table.
Create an Orders table.
Create an OrderDetails table.
Create two primary keys.
Create a primary key in the Customers table.
Create a primary key in the Orders table.
Create two relationships (DataRelation objects).
Create a relationship between the Customers table (one) and the Orders
table (many).
Create a relationship between the Orders table (one) and the OrderDetails
table (many).
Create a New Windows Application Project and Add a New Dataset to it
To create and name a new project

From the File menu, point to New, and then click Project to display the New
Project dialog box.
Depending on what language you want to use, select Visual Basic Projects or
Visual C# Projects in the Project Types pane, and then select Windows
Application.
Name the project KeyedRelationshipWalkthrough and then click OK to create
the project.
Because this walkthrough requires a dataset, you need to add one to your
project.

To add a new Dataset item to the project

From the Project menu, select Add New Item.
The Add New Item dialog appears.

From the Templates area of the Add New Item dialog box select Dataset.
Name the Dataset CustomerOrders and click Open.
Visual Studio will add a file called CustomerOrders.xsd (Visual Basic) and
CustomerOrders.vb (Visual Basic) or CustomerOrders.cs (Visual C#) to the
project and automatically load it into the designer.

From the Project menu, if it is not already, select Show All Files. (There
will be a visible border around the icon when selected)
In Solution Explorer expand the CustomerOrders.xsd node.
You can now see the CustomerOrders.vb (Visual Basic) or CustomerOrders.cs
(Visual C#) file that was created in step 3 above. This is the actual
dataset, or more accurately a dataset class. The CustomerOrders.xsd file is
an XML Schema that will define the structure of the dataset.

Create Three Tables in the Dataset
This section will explain how to add elements to your dataset that will
represent data tables.

To create the Customers table

From the XML Schema tab of the Toolbox, select an element and drop it onto
the designer.
In the top cell to the right of the 'E' highlight the default name of
element1 and type Customers.
Using the TAB key, navigate to the center cell of the next row and type
CustomerID. The data type is string for this element, which is the default
so you can just leave it. However, if you needed to assign a data type that
was not a string, you would use TAB to navigate to the drop-down list to
the right of the desired element and select the appropriate data type.
Note Also notice how you navigated past the small cell to the left of the
cell that said element1. That is the cell where you choose other types of
elements, such as attributes, and so on. Because the default value is E for
element we can leave that set as well.
Repeat Step 3 to create new rows in the Customers element for the
following:Element name Data type
CompanyName string
ContactName string
ContactTitle string
Address string
City string
Region string
PostalCode string

To create the Orders table

From the XML Schema tab of the Toolbox select an element and drop it onto
the designer.
In the top cell to the right of the 'E' highlight the default name of
element1 and type Orders.
Using the TAB key navigate to the center cell of the next row and type
OrderID and set the data type to string.
Repeat Step 3 and create new rows in the Orders element for the
following:Element name Data type
CustomerID string
OrderDate date
ShippedDate date
ShipVia string

To create the OrderDetails table

From the XML Schema tab of the Toolbox select an element and drop it onto
the designer.
In the top cell to the right of the 'E' highlight the default name of
element1 and type OrderDetails.
Using the TAB key, navigate to the center cell of the next row and type
OrderID and set the data type to string.
Repeat Step 3 and create new rows in the OrderDetails element for the
following:Element name Data type
ProductID integer
UnitPrice decimal
Quantity short

Create the Two Primary Keys in the Tables
This section will explain how to designate columns in the tables you
created in the previous section as primary keys. In order to create
relationships you must have at least one key defined in the parent table
(the parent table is the table that represents the one side of a
one-to-many relationship).

To create the primary key in the Customers table

In the Customers table, select the CustomerID row by clicking to the left
of the E.
Right-click, point to Add, select New key from the shortcut menu.
The Edit Key dialog box appears.
Change the name of the key to CustomersIDKey.
The Element drop-down list box should be set to Customers, which indicates
that you are in the Customers table.
Note The Customers table is really the Customers element that is defined
as a complex type; it is complex type elements that represent tables in XML
Schemas and Datasets. For more information see Creating One-to-Many
Relationships in XML Schemas and Datasets.
In the Fields area, select CustomerID from the drop-down list. If you
right-clicked on the CustomerID row to add the key this should be the
default value, if you clicked on a different row you will need to select
the CustomerID element.
Note The Fields area is where you select which element (within the
complex type definition of your table element) you want to define as the
Key.
Select the Dataset primary key check box, to define this key as a primary
key.
Note If you did not select the Dataset primary key checkbox then the key
would be defined as a unique key, instead of a primary key.
Click OK to close the Edit Key dialog box.
A key icon is placed on the CustomerID row to identify it as a key.

To create the primary key in the Orders table

In the Orders table select the OrderID row by clicking to the left of the
E.
Right-click the selected row, point to Add, and select New key from the
shortcut menu.
The Edit Key dialog box appears.

Change the name of the key to OrdersIDKey.
The Element drop-down should be set to Orders, which indicates that you are
in the Orders table.
In the Fields area, select OrderID from the drop down list. If you
right-clicked on the OrderID row to add the key, this should be the default
value. If you clicked on a different row, you will need to select the
correct element.
Select the Dataset primary key checkbox to define this key as a primary
key.
Click OK to close the Edit Key dialog box.
A key icon is placed on the OrderID row to identify it as a key.

You now have the keys defined that will be used to define the relationships
between the tables. You might be wondering why we did not define a key in
the OrderDetails table. At the beginning of this section it said, "In order
to create relationships you must have at least one key defined in the
parent table (the parent table is the table that represents the one side of
a one-to-many relationship)." The OrderDetails table does not represent the
one side in any relationships in this dataset, therefore no key definition
is required.

Create the Two Relationships Between the Tables
A relationship is the association between a keyed column in one table and
the many records that have an associated column in another table. In XML
Schemas relationships are defined with the keyref element. This section
will explain how to define these relationships.

To create a relationship (DataRelation object) between the Customers and
Orders table

Right-click anywhere on the Orders table, point to Add, and select New
Relation from the shortcut menu.
The Edit Relation dialog box appears.

Note Be sure to add the New Relation to the table representing the many
side of a one-to-many relationship (in this case add the Relation to the
Orders table).
The Edit Relation dialog box assigns a default name of CustomersOrders. You
can leave this as is.
The Parent Element should be set to Customers. The parent is the element
(or table) that represents the one side in a one-to-many relationship. In
this case the Customers table represents the one side.
The Child Element should be set to Orders. The child is the element (or
table) that represents the many side in a one-to-many relationship. In this
case, the Orders table represents the many side.
The Key should be set to CustomersIDKey that we defined earlier.
Note When creating a one-to-many relationship the key definition needs to
be in the Parent table, because that is the table that contains the unique
value.
The key fields are read only and contain the key column or columns defined
in the key definition.
Note Multipart keys are key definitions that have multiple columns in a
table that are part of the key definition. If a multipart key were defined
in CustomersIDKey then all the columns (or fields) would be listed in the
key fields area. This walkthrough does not include any multipart keys.
The Foreign Key Fields area is where you select the field in the child
table that is equivalent to the key in the Parent table. This field should
be set to CustomerID to match the key field in the parent table.
Click OK to accept the remaining default settings.
A DataRelation object is created and a visual representation appears on the
design surface.

To create a relationship (DataRelation object) between the Orders and
OrderDetails table

Right-click anywhere on the OrderDetails table, point to Add, and select
New Relation from the shortcut menu.
The Edit Relation dialog box appears.

Note Be sure to add the New Relation to the table representing the many
side of a one-to-many relationship (in this case add the Relation to the
OrderDetails table).
Change the default name of CustomersOrderDetails to OrdersOrderDetails to
reflect the proper tables in the relationship.
The Parent Element should be set to Orders.
The Child Element should be set to OrderDetails.
The Key should be set to OrdersIDKey, which was defined earlier.
The key fields are read only and contain the key column or columns defined
in the key definition selected in step 5.

The Foreign Key Fields area is were you select the field in the child table
that is equivalent to the key in the Parent table. Verify that this field
is set to OrderID to match the key field in the parent table.
Click OK to accept the remaining default settings.
A DataRelation object is created and a visual representation appears on the
design surface.

This is how your dataset should look in the XML Designer:



You have just created three related tables in a Dataset. If you swtich to
XML view, your code should be equivalent to the following. What is
important to note is how native XML is structured in a one-to-many (or
keyed) relational model. As you can see the Customers, Orders, and
OrderDetails all exist hierarchically at the same level, as direct children
of the CustomerOrders element. It is this CustomerOrders element that
represents the dataset.

Note Notice how the keys are defined hierarchically as children of the
CustomerOrders element. It is very important to understand why the key
definitions are not located as nested children within the element that
represents the table the key is defined in. If that were the case, the key
would only be unique for each record, not for all records in the table. By
having the key defined within the dataset itself (the CustomerOrders
element) the key controls uniqueness throughout the entire dataset.
<xs:schema id="CustomerOrders"
targetNamespace="http://tempuri.org/CustomerOrders.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/CustomerOrders.xsd"
xmlns:mstns="http://tempuri.org/CustomersOrders.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="CustomerOrders" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Customers">
<xs:complexType>
<xs:sequence>
<xs:element name="CustomerID" type="xs:string" minOccurs="0" />
<xs:element name="CompanyName" type="xs:string" minOccurs="0" />
<xs:element name="ContactName" type="xs:string" minOccurs="0" />
<xs:element name="ContactTitle" type="xs:string" minOccurs="0" />
<xs:element name="Address" type="xs:string" minOccurs="0" />
<xs:element name="City" type="xs:string" minOccurs="0" />
<xs:element name="Region" type="xs:string" minOccurs="0" />
<xs:element name="PostalCode" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Orders">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderID" type="xs:string" minOccurs="0" />
<xs:element name="CustomerID" type="xs:string" minOccurs="0" />
<xs:element name="OrderDate" type="xs:date" minOccurs="0" />
<xs:element name="ShippedDate" type="xs:date" minOccurs="0" />
<xs:element name="ShipVia" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="OrderDetails">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderID" type="xs:string" minOccurs="0" />
<xs:element name="ProductID" type="xs:integer" minOccurs="0" />
<xs:element name="UnitPrice" type="xs:decimal" minOccurs="0" />
<xs:element name="Quantity" type="xs:short" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="CustomersIDKey" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Customers" />
<xs:field xpath="mstns:CustomerID" />
</xs:key>
<xs:key name="OrdersIDKey" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Orders" />
<xs:field xpath="mstns:OrderID" />
</xs:key>
<xs:keyref name="CustomersOrders" refer="CustomersIDKey">
<xs:selector xpath=".//mstns:Orders" />
<xs:field xpath="mstns:CustomerID" />
</xs:keyref>
<xs:keyref name="OrdersOrderDetails" refer="OrdersIDKey">
<xs:selector xpath=".//mstns:OrderDetails" />
<xs:field xpath="mstns:OrderID" />
</xs:keyref> </xs:element>
</xs:schema>


Santhosh James Kutty

Microsoft Partner Support Engineer


This posting is provided “AS IS” with no warranties, and confers no rights.


Get Secure! - www.microsoft.com/security
 >> Stay informed about: Sync 3 related tables on a form