Modeling Relational Data with XML Schemas

This section describes how to implement a variety of relational data concepts by using XML schemas. It is assumed that the reader has a firm understanding of how to load XML into a DataSet . The focus here is on the XML data itself and learn how to express relational data in a format that the .NET Compact Framework DataSet can handle. Learning to write XML from scratch can consume an entire large book in itself. This section proceeds by examining XML fragments that show off important DataSet features and commenting on the relevant points.

Examining XML with the XML_DataSetView Utility

The XML_DataSetView utility is located in the folder \SampleApplications\Chapter8. There are C# and Visual Basic versions.

The XML_DataSetView utility is included because it provides a convenient means of loading an XML file into a DataSet and viewing the tables. To use the XML_DataSetView utility, follow these steps:

1. Launch the application.

2. Enter the name of the XML file to load into the text box. Click the Load XML button. The file which you plan to load must be present before clicking the Load XML button. There are many ways to acquire an XML file to load. For example, you can create an XML file with the XmlPhoneBook sample application or the XML_Creator utility, or you can try writing one from scratch.

3. When you click "Load XML," the application responds by filling in the ListBox with the names of the tables in the loaded DataSet . The DataGrid shows the data in the table currently selected in the ListBox.

This sample application demonstrates no concepts that have not been previously covered. However, it is an enormously useful utility to have on hand to test XML data and schemas and determine how quickly your device can load them.

An important lesson you should take from this section is that trying to derive your own schema from scratch is not easy. Instead, there are simpler approaches you can take:

• Start with one of the following sample schemas shown and expand it to suit your needs.

• Use the schema editor in the Visual Studio IDE and test your creation using the XML_DataSetView utility.

• Create a DataSet that holds the relational database you want to model, and call WriteXmlSchema() to dump out the schema alone. You can also use one of the WriteXml() overloads that writes schema.

Examining a Simple Schema that Describes a Single Table

In the following first XML listing, we look at a single table that holds a variety of data types. The schema demonstrates how to declare a variety of column types, and the actual data following the schema demonstrates how the tables and columns of a very simple DataSet are laid out.

Let's start with some general observations. The entire DataSet , complete with schema, is declared by the name of the DataSet . In the following code, the name of the DataSet is NewDataSet, which is the default value for a DataSet that is created without a name. So, the entire DataSet XML description appears between the <NewDataSet> and </NewDataSet> tags.

The schema is denoted with the xs:schema keyword, and the end of the schema is marked with </xs:schema> .

The actual data rows for the tables in the DataSet appear after the schema. Each row in the table appears between two tags that bear the name of the table. This simple DataSet holds a single table named Demo Table, and there are two rows in that table. So, each row's data appears between the <Demo x0020 Table> and </Demo x0020 Table> tags. The x0020 represents the space in the name for the table.

The columns in the row are given values by referring to the column name. For example, the value for the column named AnInt16 is set to the value 1 with this code:

 <AnInt16>1</AnInt16> 

Column values are declared between the <Demo x0020 Table> and </Demo x0020 Table> tags. If the row has a column for which no value is given, then it receives a default value, which can be null. This is equivalent to programmatically adding a row to a DataSet without setting all of the column values. If there is a restriction on the value of a column, for example, not null, then missing a column value can cause an exception when the XML data is loaded.

The key observations about declaring data types in schema are outlined in Table 8.1.

Table 8.1. Declaring Data Types in Schema for XML DataSets

DATA TYPE	DECLARATION	COMMENT
String	xs:string
Int16	xs:short
Int32	xs:int
Int64	xs:long
UInt16	xs:unsignedShort
UInt32	xs:unsignedInt
UInt64	xs:unsignedLong
Float	xs:float	This type is different from the decimal type because decimal values can represent higher precision without round-off error.
Decimal	xs:decimal
Boolean	xs:boolean
DateTime	xs:DateTime
Char	xs:string	Char types are declared as string with a restriction that the length be exactly one. The following code sample shows the declaration for a Char type.

Sample code: Declaring a Char type with XML Schema

 <xs:element name="AChar" minOccurs="0">  <xs:simpleType>   <xs:restriction base="xs:string">    <xs:length value="1" />   </xs:restriction>  </xs:simpleType> </xs:element> 

The full text for the XML code describing a simple DataSet appears in Listing 8.1.

Listing 8.1 XML for a simple DataSet

 <NewDataSet>  <xs:schema id="NewDataSet" xmlns=""          xmlns:xs="http://www.w3.org/2001/XMLSchema"          xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="NewDataSet" msdata:IsDataSet="true">    <xs:complexType>     <xs:choice maxOccurs="unbounded">      <xs:element name="Demo_x0020_Table">       <xs:complexType>        <xs:sequence>         <xs:element name="AString" type="xs:string" minOccurs="0" />         <xs:element name="AnInt16" type="xs:short" minOccurs="0" />         <xs:element name="AnInt32" type="xs:int" minOccurs="0" />         <xs:element name="AnInt64" type="xs:long" minOccurs="0" />         <xs:element name="AUInt16" type="xs:unsignedShort" minOccurs="0" />         <xs:element name="AUInt32" type="xs:unsignedInt" minOccurs="0" />         <xs:element name="AUInt64" type="xs:unsignedLong" minOccurs="0" />         <xs:element name="AFloat" type="xs:float" minOccurs="0" />         <xs:element name="ADecimal" type="xs:decimal" minOccurs="0" />         <xs:element name="ABoolean" type="xs:boolean" minOccurs="0" />         <xs:element name="AChar" minOccurs="0">          <xs:simpleType>           <xs:restriction base="xs:string">            <xs:length value="1" />           </xs:restriction>          </xs:simpleType>         </xs:element>         <xs:element name="ADateTime" type="xs:dateTime" minOccurs="0"/>        </xs:sequence>       </xs:complexType>      </xs:element>     </xs:choice>    </xs:complexType>   </xs:element>  </xs:schema>  <Demo_x0020_Table>   <AString>AString1</AString>   <AnInt16>1</AnInt16>   <AnInt32>2</AnInt32>   <AnInt64>3</AnInt64>   <AUInt16>4</AUInt16>   <AUInt32>5</AUInt32>   <AUInt64>6</AUInt64>   <AFloat>7</AFloat>   <ADecimal>8</ADecimal>   <ABoolean>true</ABoolean>   <AChar>c</AChar>   <ADateTime>2001-06-16T00:00:00.0000000-07:00</ADateTime>  </Demo_x0020_Table>  <Demo_x0020_Table>   <AString>AString2</AString>   <AnInt16>10</AnInt16>   <AnInt32>20</AnInt32>   <AnInt64>30</AnInt64>   <AUInt16>40</AUInt16>   <AUInt32>50</AUInt32>   <AUInt64>60</AUInt64>   <AFloat>70</AFloat>   <ADecimal>80</ADecimal>   <ABoolean>false</ABoolean>   <AChar>d</AChar>   <ADateTime>2003-06-24T00:00:00.0000000-07:00</ADateTime>  </Demo_x0020_Table> </NewDataSet> 

Autoincremented Fields and Unique Constraints

With the next chunk of XML code, we examine how to describe autoincremented fields and unique constraints.

A data column is declared as being autoincremented in the same line as its data type is declared. To declare a column autoincremented, use msdata:AutoIncrement="true" . Set the starting value with msdata:AutoIncrementSeed and the autoincrement step with msdata:AutoIncrementStep . For example, the following line declares the ContactID column to be autoincremented with a start value of 10 and a step value of 5:

 <xs:element name="ContactID" msdata:AutoIncrement="true"         msdata:AutoIncrementSeed="10"         msdata:AutoIncrementStep="5" type="xs:int" minOccurs="0" /> 

To declare a column unique, use the xs:unique keyword. Declare the name of the constraint and indicate a selector and field for the constraint. The selector is the name of the DataTable in which the DataColumn resides. The field is the name of the DataColumn . These declarations appear in the schema after the xs:complexType , which specified the table name and columns in the table. For example, this XML snippet declares a unique constraint named Constraint1 , in which the PhoneNumber DataColumn in the Phone Contact DataTable is declared unique:

 <xs:unique name="Constraint1">  <xs:selector xpath=".//Phone_x0020_Contacts" />  <xs:field xpath="PhoneNumber" /> </xs:unique> 

BE CAREFUL WHEN IGNORING SCHEMA

Notice that information about such things as autoincremented fields, constraints, and so on is held in the schema section of the XML. The actual rows of data hold no such information. If you load a DataSet and ignore the schema that comes with it, the resulting populated DataSet will lack the constraints, autoincremented fields, and so on that the original creator of the DataSet intended.

The entire XML representation of a DataSet with an autoincremented field and a unique constraint appears as follows in Listing 8.2.

Listing 8.2 XML representation of a DataSet with an autoincremented field and a unique constraint

 <NewDataSet>  <xs:schema id="NewDataSet" xmlns=""          xmlns:xs="http://www.w3.org/2001/XMLSchema"          xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="NewDataSet" msdata:IsDataSet="true">    <xs:complexType>     <xs:choice maxOccurs="unbounded">      <xs:element name="Phone_x0020_Contacts">       <xs:complexType>        <xs:sequence>         <xs:element name="Name" type="xs:string" />         <xs:element name="PhoneNumber" type="xs:string" minOccurs="0" />         <xs:element name="ContactID" msdata:AutoIncrement="true"                 msdata:AutoIncrementSeed="10"                 msdata:AutoIncrementStep="5" type="xs:int" minOccurs="0" />        </xs:sequence>       </xs:complexType>      </xs:element>     </xs:choice>    </xs:complexType>    <xs:unique name="Constraint1">     <xs:selector xpath=".//Phone_x0020_Contacts" />     <xs:field xpath="PhoneNumber" />    </xs:unique>   </xs:element>  </xs:schema>  <Phone_x0020_Contacts>   <Name>George Washington</Name>   <PhoneNumber>340-1776</PhoneNumber>   <ContactID>10</ContactID>  </Phone_x0020_Contacts>  <Phone_x0020_Contacts>   <Name>Ben Franklin</Name>   <PhoneNumber>336-3211</PhoneNumber>   <ContactID>15</ContactID>  </Phone_x0020_Contacts>  <Phone_x0020_Contacts>   <Name>Alexander Hamilton</Name>   <PhoneNumber>756-3211</PhoneNumber>   <ContactID>20</ContactID>  </Phone_x0020_Contacts> </NewDataSet> 

Describing Multiple Tables, ForeignKeyConstraints , DataRelation s, and Computed Fields with XML Schema

Declaring multiple tables is simply an extension of declaring a DataSet with a single table. In the schema section of the XML, tables are declared one after the other. All of the table declarations occur inside of an <xs:choice> node. Each table is started with an <xs:element> tag that also holds the name of the table.

DataRelations and ForeignKeyConstraints are described with <xs:keyref> . For example, this XML code, C# code, and Visual Basic code all describe the same thing:

 XML <xs:keyref name="MainToCholesterolFKConstraint" refer="Constraint1"         msdata:ConstraintOnly="true" msdata:AcceptRejectRule="Cascade">  <xs:selector xpath=".//BloodPressure" />  <xs:field xpath="ContactID" /> </xs:keyref> C# ForeignKeyConstraint l_ForeignKC = new ForeignKeyConstraint         ("MainToCholesterolFKConstraint",         l_DataSet.Tables["PhoneContactsMainTable"].Columns["ContactID"],         l_DataSet.Tables["BloodPressure"].Columns["ContactID"]); VB Dim l_ForeignKC as ForeignKeyConstraint = new ForeignKeyConstraint         ("MainToCholesterolFKConstraint",         l_DataSet.Tables("PhoneContactsMainTable").Columns("ContactID"),         l_DataSet.Tables("BloodPressure").Columns("ContactID")); 

Computed columns are described as part of the declaration for the column. When the column is first declared, the expression for computing the column is included. For example, the following XML code, C# code, and Visual Basic code all describe the same thing:

 XML <xs:element name="AverageReading" msdata:ReadOnly="true" msdata:Expression=         "(Reading1 + Reading2 + Reading3) / 3"         type="xs:decimal" minOccurs="0" /> C# // Make the "AverageReading" column a computed column by using an expression l_newTable.Columns["AverageReading"].Expression =         "(Reading1 + Reading2 + Reading3) / 3"; VB ' Make the "AverageReading" column a computed column by using an expression l_newTable.Columns("AverageReading").Expression =         "(Reading1 + Reading2 + Reading3) / 3" 

Listing 8.3 shows the XML code that completely describes a DataSet . It demonstrates how to describe multiple tables, ForeignKeyConstraints , and computed fields:

Listing 8.3 XML for a completely described DataSet

 <NewDataSet>  <xs:schema id="NewDataSet" xmlns=""          xmlns:xs="http://www.w3.org/2001/XMLSchema"          xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="NewDataSet" msdata:IsDataSet="true">    <xs:complexType>     <xs:choice maxOccurs="unbounded">      <xs:element name="PhoneContactsMainTable">       <xs:complexType>        <xs:sequence>         <xs:element name="ContactID" msdata:AutoIncrement="true"                 msdata:AutoIncrementSeed="10"                 msdata:AutoIncrementStep="5" type="xs:int" minOccurs="0" />         <xs:element name="FirstName" type="xs:string" minOccurs="0" />         <xs:element name="LastName" type="xs:string" minOccurs="0" />         <xs:element name="FullName" msdata:ReadOnly="true"                 msdata:Expression="FirstName + ' ' +                 LastName" type="xs:string" minOccurs="0" />        </xs:sequence>       </xs:complexType>      </xs:element>      <xs:element name="Cholesterol">       <xs:complexType>        <xs:sequence>         <xs:element name="ContactID" type="xs:int" minOccurs="0" />         <xs:element name="Reading1" type="xs:decimal" minOccurs="0" />         <xs:element name="Reading2" type="xs:decimal" minOccurs="0" />         <xs:element name="Reading3" type="xs:decimal" minOccurs="0" />         <xs:element name="AverageReading" msdata:ReadOnly="true"                 msdata:Expression="(Reading1 +                 Reading2 + Reading3) / 3" type="xs:decimal" minOccurs="0" />     </xs:sequence>    </xs:complexType>    </xs:element>    <xs:element name="BloodPressure">    <xs:complexType>     <xs:sequence>     <xs:element name="ContactID" type="xs:int" minOccurs="0" />     <xs:element name="Reading1" type="xs:decimal" minOccurs="0" />     <xs:element name="Reading2" type="xs:decimal" minOccurs="0" />     <xs:element name="Reading3" type="xs:decimal" minOccurs="0" />     <xs:element name="AverageReading" msdata:ReadOnly="true"             msdata:Expression="(Reading1 +             Reading2 + Reading3) / 3" type="xs:decimal" minOccurs="0" />     </xs:sequence>    </xs:complexType>    </xs:element>   </xs:choice>   </xs:complexType>   <xs:unique name="Constraint1">   <xs:selector xpath=".//PhoneContactsMainTable" />   <xs:field xpath="ContactID" />   </xs:unique>   <xs:keyref name="MainToCholesterolFKConstraint" refer="Constraint1"           msdata:ConstraintOnly="true"           msdata:AcceptRejectRule="Cascade">   <xs:selector xpath=".//BloodPressure" /><xs:field xpath="ContactID" />   </xs:keyref>   </xs:element>  </xs:schema>  <PhoneContactsMainTable>   <ContactID>10</ContactID>   <FirstName>George</FirstName>   <LastName>Washington</LastName>   <FullName>George Washington</FullName>  </PhoneContactsMainTable>  <PhoneContactsMainTable>   <ContactID>15</ContactID>   <FirstName>Ben</FirstName>   <LastName>Franklin</LastName>   <FullName>Ben Franklin</FullName>  </PhoneContactsMainTable>  <PhoneContactsMainTable>   <ContactID>20</ContactID>   <FirstName>Alexander</FirstName>   <LastName>Hamilton</LastName>   <FullName>Alexander Hamilton</FullName>  </PhoneContactsMainTable>  <Cholesterol>   <ContactID>10</ContactID>   <Reading1>200</Reading1>   <Reading2>300</Reading2>   <Reading3>500</Reading3>   <AverageReading>333.33333333333333333333333333</AverageReading>  </Cholesterol> </NewDataSet> 

Creating XML Data with the XML_Creator Utility

The XML_Creator utility is included as a convenience for novice users who want to explore XML schema creation with DataSet s. This utility is located in the \SampleApplications\ Chapter8 folder, and there are C# and Visual Basic versions.

XML_Creator does not demonstrate any new concepts related to the DataSet . Instead, it is a convenient framework by which to create DataSet s with various relational data structures and save them in XML format. The XML_Creator was used to create all of the XML examples shown previously in this section.

To use the XML_Creator, launch it and then click the GO button. The application acquires a series of populated DataSet s through a variety of method calls and saves each DataSet in XML format.

DESIGNING XML CONSUMABLE BY THE .NET COMPACT FRAMEWORK

Using a utility like the XML_Creator to experiment with DataSet s and the resulting XML is very useful because you know that the .NET Compact Framework DataSet will be able to consume the XML data that will be passed around. If you use a desktop application to design your XML DataSet representations, or if you do it by hand, always check it against the .NET Compact Framework's implementation of the DataSet .