AirlineBrokers Database | Application Development Using C# and .NET

for RuBoard

The AirlineBrokers database will be used to study XML data access. This database can be created using the SqlServer Enterprise Manager and the airlinebroker.sql script found in the AcmeDatabaseScripts subdirectory of the case study. The AirlineBrokers database represents another service that the Acme reservation system uses. Acme customers can make airline reservations to the places they wish to go.

The database has several tables:

Airlines: information about the various airlines in the database
PlaneType: the various planes that the airlines use
Flights : information about the various airlines' flights
Customers: information about customers
Reservations: information about the customers' reservations

Although in real life the Airline Broker and the Hotel Broker would not have the same Customers table, for simplicity we use the same table structure, and we use the same component to access it.

DataSet and XML

To illustrate the relationship between the relation model of the DataSet and the XML model we will first fetch some information from the database. The DataSetXml example uses the same commands and techniques we have studied in this chapter to extract the data.

First the connection, DataSet , and the SqlDataAdapters for the various tables are created.

 SqlConnection conn = new SqlConnection(connectString);  DataSet d = new DataSet("AirlineBroker");  SqlDataAdapter  airlinesAdapter = new SqlDataAdapter();  SqlDataAdapter  flightsAdapter = new SqlDataAdapter();  SqlDataAdapter  planetypeAdapter = new SqlDataAdapter();  SqlDataAdapter  customersAdapter = new SqlDataAdapter();  SqlDataAdapter  reservationsAdapter = new SqlDataAdapter();

Then the various select commands to fetch the data are created and the dataset is filled with the data from those tables:

 airlinesAdapter.SelectCommand = new SqlCommand(                            "select * from Airlines", conn);  airlinesAdapter.Fill(d, "Airlines");  flightsAdapter.SelectCommand = new SqlCommand(                             "select * from Flights", conn);  flightsAdapter.Fill(d, "Flights");  planetypeAdapter.SelectCommand = new SqlCommand(                           "select * from PlaneType", conn);  planetypeAdapter.Fill(d, "PlaneType");  customersAdapter.SelectCommand = new SqlCommand(                           "select * from Customers", conn);  customersAdapter.Fill(d, "Customers");  reservationsAdapter.SelectCommand = new SqlCommand(                        "select * from Reservations", conn);  reservationsAdapter.Fill(d, "Reservations");

We now have the data for the Airlines, Flights, PlaneType, Customers, and Reservations tables in the data set.

Next we have the DataSet written out as an XML schema, the schema it infers from the data. Then the DataSet writes out the data as XML.

 d.WriteXmlSchema("Airlines.xsd");  d.WriteXml("Airlines.xml");

Here are some of the data that were written to the file Airlines.xml. The main element is Airline Broker, which was the name of the DataSet . Elements at the next lower level correspond to the various tables that were added to the database: Airlines , Flights , PlaneType , and Customers . There were no reservations in the database. There is one set for each row in the table. The elements under each of these tables correspond to the fields for that particular row.

 <AirlineBroker>    <Airlines>      <Name>America West</Name>      <Abbreviation>AW</Abbreviation>      <WebSite>www.americawest.com</WebSite>      <ReservationNumber>555-555-1212</ReservationNumber>    </Airlines>    <Airlines>      <Name>Delta</Name>      <Abbreviation>DL</Abbreviation>      <WebSite>www.delta.com</WebSite>       <ReservationNumber>800-456-7890</ReservationNumber>    </Airlines>  ...    <Flights>      <Airline>DL</Airline>      <FlightNumber>987</FlightNumber>      <StartCity>Atlanta</StartCity>      <EndCity>New Orleans</EndCity>      <Departure>2001-10-05T20:15:00.0000000-04:00                </Departure>      <Arrival>2001-10-05T22:30:00.0000000-04:00</Arrival>      <PlaneType>737</PlaneType>      <FirstCost>1300</FirstCost>      <BusinessCost>0</BusinessCost>      <EconomyCost>450</EconomyCost>    </Flights>  ...    <Flights>    <PlaneType>      <PlaneType>737</PlaneType>      <FirstClass>10</FirstClass>      <BusinessClass>0</BusinessClass>      <EconomyClass>200</EconomyClass>    </PlaneType>  ...    <Customers>      <LastName>Adams</LastName>      <FirstName>John</FirstName>      <EmailAddress>adams@presidents.org</EmailAddress>      <CustomerId>1</CustomerId>   </Customers>  </AirlineBroker>

From the structure of the data, the DataSet deduces a schema that was written to Airlines.xsd. We discuss here an excerpt from that file. There are no relationships or primary keys defined between any of the tables such as Airlines and Flights as in the database, because none were defined in the DataSet . If you look at the actual generated file, you will see that schema information was inferred for Reservations even though there were no data in the table.

The schema preamble in the first line, reproduced here, defines the name of the schema as AirlineBroker, and we are using two namespaces in this schema document. One, abbreviated xsd , contains the XML Schema standard definitions. The other, abbreviated msdata , contains Microsoft definitions.

 ...  <xsd:schema id="AirlineBroker" targetNamespace="" xmlns=""        xmlns:xsd=http://www.w3.org/2001/XMLSchema        xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

The next line defines an element called AirlineBroker which has an attribute that indicates this schema came from a DataSet . That is a Microsoft defined attribute, not one defined by the W3C Schema namespace. This element AirlineBroker is a complex type. which means it is a structure composed of other types. This structure can have an unlimited number of any (or even none) of the types defined in the rest of the schema.

 <xsd:element name="AirlineBroker" msdata:IsDataSet="true">    <xsd:complexType>    <xsd:choice maxOccurs="unbounded">

The Airlines element is defined next. It, too, is a structure, or complex type, whose elements, if present, appear in the structure in the order in which they were defined. Those elements, which correspond to the columns in the database table, are all defined to be strings that are optional. No primary keys were defined, and these strings are certainly not optional in the database, but that was what the DataSet deduced from the set of tables, constraints and relationships currently defined in the DataSet .

 <xsd:element name="Airlines">    <xsd:complexType>    <xsd:sequence>      <xsd:element name="Name" type="xsd:string"                                 minOccurs="0" />      <xsd:element name="Abbreviation"               type="xsd:string" minOccurs="0" />      <xsd:element name="WebSite" type="xsd:string"                                    minOccurs="0" />      <xsd:element name="ReservationNumber"                  type="xsd:string" minOccurs="0" />    </xsd:sequence>    </xsd:complexType>  </xsd:element>

The table, Flights, is defined similarly to Airlines. In addition to there being no primary key here, there is no foreign key defined for Airline or PlaneType.

 <xsd:element name="Flights">    <xsd:complexType>    <xsd:sequence>      <xsd:element name="Airline" type="xsd:string"                                     minOccurs="0" />      <xsd:element name="FlightNumber" type="xsd:int"                                     minOccurs="0" />      <xsd:element name="StartCity" type="xsd:string"                                     minOccurs="0" />      <xsd:element name="EndCity" type="xsd:string"                                     minOccurs="0" />       <xsd:element name="Departure" type="xsd:dateTime"                                             minOccurs="0" />           <xsd:element name="Arrival" type="xsd:dateTime"                                            minOccurs="0" />           <xsd:element name="PlaneType" type="xsd:string"                                            minOccurs="0" />           <xsd:element name="FirstCost" type="xsd:decimal"                                            minOccurs="0" />           <xsd:element name="BusinessCost"                         type="xsd:decimal" minOccurs="0" />           <xsd:element name="EconomyCost"                         type="xsd:decimal" minOccurs="0" />         </xsd:sequence>         </xsd:complexType>       </xsd:element>  ...    </xsd:choice>    </xsd:complexType>  </xsd:element>  </xsd:schema>

We will come back to this schema definition, but for the moment let us continue to work with this example.

Creating an XML Doc from a Dataset

We create a new XML document from the DataSet . Using an XPath query to get the top of the document, we set up an XmlNodeReader to read through it. We can then print out the contents of the document to the console. The XmlNodeReader class knows how to navigate through the document.

 XmlDataDocument xmlDataDoc = new XmlDataDocument(d);  XmlNodeReader xmlNodeReader = null;  try  {    XmlNode node = xmlDataDoc.SelectSingleNode("/");    xmlNodeReader = new XmlNodeReader (node);    FormatXml (xmlNodeReader);  }  catch (Exception e)  {    Console.WriteLine ("Exception: {0}", e.ToString());  }  finally  {    if (xmlNodeReader != null)      xmlNodeReader.Close();  }  ...   private static void FormatXml (XmlReader reader)  {  while (reader.Read())  {    switch (reader.NodeType)   {      ...      case XmlNodeType.Element:        Format (reader, "Element");        while(reader.MoveToNextAttribute())          Format (reader, "Attribute");        break;      case XmlNodeType.Text:        Format (reader, "Text");        break;  ...  static      string lastNodeType = "";  private static void Format(XmlReader reader, string                                             nodeType)  {    if (nodeType == "Element")    {      if (lastNodeType == "Element")      {        Console.WriteLine();      }      for (int i=0; i < reader.Depth; i++)      {        Console.Write("  ");      }      Console.Write(reader.Name);    }    else if (nodeType == "Text")      Console.WriteLine("={0}", reader.Value);    else    {      Console.Write(nodeType + "<" + reader.Name + ">" +                                           reader.Value);      Console.WriteLine();    }    lastNodeType = nodeType;  }

The results resemble the XML that the DataSet wrote to a file.

 AirlineBroker    Airlines      Name=America West       Abbreviation=AW      WebSite=www.americawest.com      ReservationNumber=555-555-1212    Airlines      Name=Delta      Abbreviation=DL      WebSite=www.delta.com      ReservationNumber=800-456-7890    Airlines      Name=Northwest      Abbreviation=NW      WebSite=www.northwest.com      ReservationNumber=888-111-2222    Airlines      Name=Piedmont      Abbreviation=P      WebSite=www.piedmont.com      ReservationNumber=888-222-333    Airlines      Name=Southwest      Abbreviation=S      WebSite=www.southwest.com      ReservationNumber=1-800-111-222    Airlines      Name=United      Abbreviation=UAL      WebSite=www.ual.com      ReservationNumber=800-123-4568    Flights      Airline=DL      FlightNumber=987      StartCity=Atlanta      EndCity=New Orleans      Departure=2001-10-05T20:15:00.0000000-04:00      Arrival=2001-10-05T22:30:00.0000000-04:00      FirstCost=1300      PlaneType=737      BusinessCost=0      EconomyCost=450    Flights      Airline=UAL      FlightNumber=54      EndCity=Los Angeles      StartCity=Boston      Departure=2001-10-01T10:00:00.0000000-04:00      Arrival=2001-10-01T13:00:00.0000000-04:00      PlaneType=767      FirstCost=1500      BusinessCost=1000      EconomyCost=300     PlaneType      PlaneType=737      FirstClass=10      BusinessClass=0      EconomyClass=200    PlaneType      PlaneType=767      FirstClass=10      BusinessClass=30      EconomyClass=300    Customers      LastName=Adams      FirstName=John      EmailAddress=adams@presidents.org      CustomerId=1

for RuBoard