Walkthrough: Creating a Dataset with Tables, Keys, and One-to-Many Relationships

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.

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