.NET Reference Guide

Using Relationships in DataSets

Last updated Sep 16, 2004.

Using Relationships in DataSets

The ADO.NET DataSet is a very handy object for manipulating data in memory. Most .NET programmers are familiar with using DataSet objects to manipulate data in a single database table. The load, manipulate, store model is familiar and smilar enough to ADO Recorset objects that Visual Basic and MFC programmers become comfortable with ADO.NET DataSet in short order. Beginning .NET programmers, whether or not they come from an ADO background, often treat the DataSet in much the same way as ADO programmers use Recordset, and in doing so miss out on much of what makes DataSet objects so wonderful to work with.

The DataSet consists of a collection of DataTable objects and Relations among the tables. With a DataSet you can create an in-memory representation of your relational database or subsets of it. For example, consider a simple order entry application that has four tables: Products, Customers, Orders, and OrderItems, as shown here.

Figure 37

There are three relationships among the tables:

1. There is a one-to-many relationship between Customers and Orders. Each Orders record contains the CustomerId of the Customers record to which it belongs.

2. There is a one-to-many relationship between Orders and OrderItems. Each OrderItems record contains the OrderId of the Orders record to which it belongs.

There is a one-to-many relationship between Products and OrderItems. Each OrderItems record contains the ProductId of the Products record that is being ordered.

I've created and saved a DataSet that implements this very simple order entry system. Rather than fool with a database for the example program, I created the DataSet in an XML file so that I can load it with the DataSet.ReadXml method. The saved DataSet is shown here:

The Sample DataSet

<?xml version="1.0" standalone="yes"?>
<CustomerOrders>
 <xs:schema id="CustomerOrders" xmlns="" 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:int" minOccurs="0" />

        <xs:element name="LastName" type="xs:string" minOccurs="0" />
        <xs:element name="FirstName" type="xs:string" minOccurs="0" />

        <xs:element name="Company" 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:int" minOccurs="0" />

        <xs:element name="CustomerId" type="xs:int" minOccurs="0" />
        <xs:element name="OrderDate" type="xs:dateTime" minOccurs="0" />

       </xs:sequence>
      </xs:complexType>
     </xs:element>
     <xs:element name="Products">
      <xs:complexType>
       <xs:sequence>

        <xs:element name="ProductId" type="xs:int" minOccurs="0" />
        <xs:element name="ProductNumber" type="xs:string" minOccurs="0" />

        <xs:element name="Description" type="xs:string" minOccurs="0" />
        <xs:element name="Price" type="xs:decimal" minOccurs="0" />

       </xs:sequence>
      </xs:complexType>
     </xs:element>
     <xs:element name="OrderItems">
      <xs:complexType>
       <xs:sequence>

        <xs:element name="OrderItemId" type="xs:int" minOccurs="0" />
        <xs:element name="OrderId" type="xs:int" minOccurs="0" />

        <xs:element name="ProductId" type="xs:int" minOccurs="0" />
        <xs:element name="Quantity" type="xs:int" minOccurs="0" />

        <xs:element name="Price" type="xs:decimal" minOccurs="0" />
       </xs:sequence>
      </xs:complexType>

     </xs:element>
    </xs:choice>
   </xs:complexType>
  </xs:element>
 </xs:schema>
 <Customers>

  <CustomerId>1</CustomerId>
  <LastName>Hacker</LastName>
  <FirstName>Josephine</FirstName>
  <Company>Hackers, Inc.</Company>

 </Customers>
 <Customers>
  <CustomerId>2</CustomerId>
  <LastName>Nuscgek</LastName>
  <FirstName>Jeb</FirstName>

  <Company>Scorched Earth Software</Company>
 </Customers>
 <Customers>
  <CustomerId>3</CustomerId>
  <LastName>Agnoulli</LastName>

  <FirstName>Joe</FirstName>
  <Company>Joe's Garage</Company>
 </Customers>
 <Customers>

  <CustomerId>4</CustomerId>
  <LastName>Wauters</LastName>
  <FirstName>Benjamin</FirstName>
  <Company>The Software Shop</Company>

 </Customers>
 <Orders>
  <OrderId>1</OrderId>
  <CustomerId>1</CustomerId>
  <OrderDate>2003-12-27T00:00:00.0000000-06:00</OrderDate>

 </Orders>
 <Orders>
  <OrderId>2</OrderId>
  <CustomerId>2</CustomerId>
  <OrderDate>2004-03-04T00:00:00.0000000-06:00</OrderDate>

 </Orders>
 <Orders>
  <OrderId>3</OrderId>
  <CustomerId>3</CustomerId>
  <OrderDate>2004-09-01T00:00:00.0000000-05:00</OrderDate>

 </Orders>
 <Orders>
  <OrderId>4</OrderId>
  <CustomerId>2</CustomerId>
  <OrderDate>2004-04-12T00:00:00.0000000-05:00</OrderDate>

 </Orders>
 <Orders>
  <OrderId>5</OrderId>
  <CustomerId>2</CustomerId>
  <OrderDate>2004-05-12T00:00:00.0000000-05:00</OrderDate>

 </Orders>
 <Orders>
  <OrderId>6</OrderId>
  <CustomerId>4</CustomerId>
  <OrderDate>2003-12-11T00:00:00.0000000-06:00</OrderDate>

 </Orders>
 <Orders>
  <OrderId>7</OrderId>
  <CustomerId>3</CustomerId>
  <OrderDate>2003-10-27T00:00:00.0000000-06:00</OrderDate>

 </Orders>
 <Products>
  <ProductId>1</ProductId>
  <ProductNumber>U47</ProductNumber>
  <Description>Cog, Small</Description>

  <Price>3.86</Price>
 </Products>
 <Products>
  <ProductId>2</ProductId>
  <ProductNumber>XYZZY</ProductNumber>

  <Description>Gear, Large</Description>
  <Price>27.89</Price>
 </Products>
 <Products>
  <ProductId>3</ProductId>

  <ProductNumber>PF5038</ProductNumber>
  <Description>Sprocket, 11-tooth</Description>
  <Price>14.52</Price>
 </Products>

 <Products>
  <ProductId>4</ProductId>
  <ProductNumber>QPI42</ProductNumber>
  <Description>Phase Inverter, Quantum</Description>

  <Price>186282</Price>
 </Products>
 <Products>
  <ProductId>5</ProductId>
  <ProductNumber>SC0101</ProductNumber>

  <Description>Cat, Schroedinger's</Description>
  <Price>273.58</Price>
 </Products>
 <Products>

  <ProductId>6</ProductId>
  <ProductNumber>PUH12</ProductNumber>
  <Description>Principal, Heisenberg Uncertainty</Description>
  <Price>42.42</Price>

 </Products>
 <Products>
  <ProductId>7</ProductId>
  <ProductNumber>WIDGR</ProductNumber>
  <Description>Widget, Green</Description>

  <Price>0.07</Price>
 </Products>
 <Products>
  <ProductId>8</ProductId>
  <ProductNumber>DH1</ProductNumber>

  <Description>Dohickey, Blue, with Fuzz</Description>
  <Price>1.87</Price>
 </Products>
 <Products>
  <ProductId>9</ProductId>

  <ProductNumber>TMGS</ProductNumber>
  <Description>Thingamagig, Spongy</Description>
  <Price>4.17</Price>
 </Products>

 <Products>
  <ProductId>10</ProductId>
  <ProductNumber>TMBS</ProductNumber>
  <Description>Thingamabob, Set, Assorted</Description>

  <Price>12.14</Price>
 </Products>
 <OrderItems>
  <OrderItemId>1</OrderItemId>
  <OrderId>1</OrderId>

  <ProductId>1</ProductId>
  <Quantity>5</Quantity>
  <Price>3.86</Price>
 </OrderItems>

 <OrderItems>
  <OrderItemId>2</OrderItemId>
  <OrderId>1</OrderId>
  <ProductId>10</ProductId>

  <Quantity>1</Quantity>
  <Price>12.14</Price>
 </OrderItems>
 <OrderItems>
  <OrderItemId>3</OrderItemId>

  <OrderId>2</OrderId>
  <ProductId>4</ProductId>
  <Quantity>3</Quantity>
  <Price>186282</Price>

 </OrderItems>
 <OrderItems>
  <OrderItemId>4</OrderItemId>
  <OrderId>2</OrderId>
  <ProductId>5</ProductId>

  <Quantity>1</Quantity>
  <Price>273.58</Price>
 </OrderItems>
 <OrderItems>
  <OrderItemId>5</OrderItemId>

  <OrderId>3</OrderId>
  <ProductId>8</ProductId>
  <Quantity>4</Quantity>
  <Price>1.87</Price>

 </OrderItems>
 <OrderItems>
  <OrderItemId>6</OrderItemId>
  <OrderId>4</OrderId>
  <ProductId>7</ProductId>

  <Quantity>2</Quantity>
  <Price>0.07</Price>
 </OrderItems>
 <OrderItems>
  <OrderItemId>7</OrderItemId>

  <OrderId>5</OrderId>
  <ProductId>1</ProductId>
  <Quantity>1</Quantity>
  <Price>3.86</Price>

 </OrderItems>
 <OrderItems>
  <OrderItemId>8</OrderItemId>
  <OrderId>6</OrderId>
  <ProductId>4</ProductId>

  <Quantity>1</Quantity>
  <Price>186282</Price>
 </OrderItems>
 <OrderItems>
  <OrderItemId>9</OrderItemId>

  <OrderId>7</OrderId>
  <ProductId>9</ProductId>
  <Quantity>50</Quantity>
  <Price>4.17</Price>

 </OrderItems>

</CustomerOrders>