Manipulating DataSet Relationships

   

---

Create and Manipulate DataSets: Manipulate DataSet Relationships.

As an in-memory representation of a database, the DataSet object retains the notion of relations between tables. As you would expect, this means that a DataSet schema can also store information on keys and relationships in the DataSet that it represents.

In this section, you'll see how to add keys to a DataSet schema, and then how to create relationships betweeen multiple tables that are part of the same DataSet schema.

Adding Keys to a DataSet Schema

The DataSet schema designer allows you to create two types of keys. You can identify a field or a set of fields as the primary key for a table, or you can identify a field or a set of fields as making up a unique key. Step By Step 1.7 enables you to practice these skills.

STEP BY STEP

1.7 Creating Keys in the DataSet Schema Designer

1. Ensure that the DataSet schema file that you edited in Step By Step 1.6 is open in the designer.

2. Select the key tool in the Toolbox. Drag the key from the Toolbox and drop it on the CustomerID element within the Customers table. This will open the Edit Key dialog box.

3. Name the key CustomersPrimaryKey . Select CustomerID in the Fields list, if it is not already selected. Check the DataSet Primary Key check box, as shown in Figure 1.11. Click OK to create the key. You'll see a key icon appear next to the CustomerID element.

   Figure 1.11. Creating a Primary Key with the DataSet Schema Designer.

   

4. Select the key tool in the Toolbox. Drag the key from the Toolbox and drop it on the CompanyName element within the Customers table. This will open the Edit Key dialog box.

5. Name the key CompanyNameUniqueKey . Select CompanyName in the Fields list, if it is not already selected. Click OK to create the key. You'll see a key icon appear next to the CompanyName element. Figure 1.12 shows the Customers table with both keys created.

   Figure 1.12. Creating Keys in the DataSet Schema Designer.

   

You can create one primary key, or as many unique keys as you want, for each table in the DataSet schema designer.

The primary key identifies the field (or combination of fields) that uniquely specifies an individual row in the table. Unique keys identify other fields or combinations of fields that cannot be repeated in different records of the same table.

EXAM TIP

Deleting a Key To delete a primary key or a unique key in the DataSet schema designer, you must first click in the row containing the key to select the row. Then you can right-click on that row and select Delete Key.

One-to-Many Relationships

One way to relate two tables in a DataSet schema is to identify common key columns and create a relationship between the tables using those columns . This is similar to the way that relational database products handle relationships. Step By Step 1.8 demonstrates this technique.

STEP BY STEP

1.8 Creating One-to-Many Relationships in the DataSet Schema Designer

1. Ensure that the DataSet schema file that you edited in Step By Step 1.7 is open in the designer.

2. Select the Relation tool in the Toolbox. Drag the Relation from the Toolbox and drop it on the Orders table. This will open the Edit Relation dialog box.

3. Name the relation CustomersOrders . Select Customers as the Parent Element and Orders as the Child Element. Select CustomerPrimaryKey as the Key to use. Select CustomerID as both the key field and the foreign key field. Leave all other options at their default values. Figure 1.13 shows this dialog box.

   Figure 1.13. Creating a Relation with the DataSet Schema Designer.

   

4. Click OK to create the relation. The designer will draw a relation object and connecting lines between the two tables, as shown in Figure 1.14.

   Figure 1.14. A Relation in the DataSet Schema Designer.

   

You can also specify a number of optional behaviors in the Edit Relation dialog box:

• To create a relationship that is used only as a constraint, but not for fetching child records, check the Create Foreign Key Constraint Only check box.

• To specify the behavior of child records when the key field in the parent record is updated, select a value from the Update Rule combo box. You can choose to automatically cascade changes to the child records, to set the key field in the child records to Null, or to set the key field in the child records to its default value.

• To specify the behavior of child records when a parent record is deleted, select a value from the Delete Rule combo box. You can choose to automatically cascade deletions to the child records, to set the key field in the child records to Null, or to set the key field in the child records to its default value.

• To specify the behavior of child records when a change to a parent record is accepted or rejected, select a value from the Accept/Reject Rule combo box. You can choose to automatically accept changes to the child records or to leave those changes to be accepted separately.

Nested Relationships

Although one-to-many relationships are the only kind you'll find in a typical relational database, the DataSet schema designer also supports nested relationships. In a nested relationship, the child table is stored as a complex data type within the parent table. Step by Step 1.9 will help you set up a nested relationship.

STEP BY STEP

1.9 Creating Nested Relationships in the DataSet Schema Designer

1. Ensure that the DataSet schema file that you edited in Step By Step 1.8 is open in the designer.

2. Select the Order Details table. Drag the Order Details table and drop it on top of the Orders table. This will create a relationship between the Orders table and the Order Details table, as shown in Figure 1.15.

   Figure 1.15. A Nested Relationship created with the DataSet Schema Designer.

   

3. Note that in this case, there's no explicit relationship object; the line between the two tables does not have a diamond on it. Instead, the Order Details table has been added as a complex type to the Orders table.

Because there are two different ways to relate tables in a DataSet schema, you'll have to choose between the two. Here are some points to help you decide whether to use one-to-many or nested relationships in your DataSet schemas:

• One-to-many relationships more directly represent the way that data is stored in a relational database. If your data is primarily stored in a database, this provides the most natural mapping.

• Nested relationships are more natural to represent in XML. If your data is not stored in a relational database, nested relationships provide a cleaner and more succinct XML representation of the connections between tables.

• If you require interoperability with other XML applications, nested relationships are more likely to be correctly interpreted.

REVIEW BREAK

• To create a primary key or a unique key in a DataSet schema, drag and drop the key tool from the Toolbox to an XML element.

• To create a one-to-many relationship, drag and drop the relation tool from the Toolbox to the child table of the relationship.

• To create a nested relationship, drag and drop the child table to the parent table.

---

Top