perfectxml.com
 Basic Search  Advanced Search   
Topics Resources Free Library Software XML News About Us
home » Free Library » Wrox Press » Beginning Visual Basic .NET Databases Sunday, 15 July 2007
Reproduced with kind permission of Wrox Press
Chapter 12: ADO.NET and XML
ISBN: 1861005555
Author(s): Matthew Reynolds, Denise Gosnell, Bill Forgey
What does this book cover?
  • The basic principles of relational database design
  • Microsoft SQL Server Desktop Engine
  • Querying the database with the T-SQL language
  • Visual Studio .NET and the Server Explorer
  • ADO.NET and the DataSet
  • Data binding, updating the database, and conflict resolution
  • XML's role in ADO.NET
  • Accessing data with ASP.NET and Web Services
Page 4 of 7

Go to page: 1   2   3   4   5   6   7   

12

ADO.NET and XML

Relational Data

At this point we know how to load and save XML data directly using the DataSet. We also know how to generate schemas and how to use those schemas for validation. However, at this point, we've only seen what happens when we have a single table. As we've already seen in earlier chapters, the DataGrid control is capable of letting us navigate around different tables pretty easily.

In this section we'll extend what we have so that, when we have an order shown in the DataGrid, we can drill down to see the lines that make up that order.

Try It Out - Relating the "Order Details" Table
  1. If the project is running, close it.
     
  2. Using the code editor, find the code for Form1. Make these changes to btnConnect_Click.

 Private Sub btnConnect_Click(ByVal sender As System.Object, _
                   ByVal e As System.EventArgs) Handles btnConnect.Click

   ' Connect to the database...
   Dim connection As New sqlconnection(DbString)
   connection.Open()

   ' Create a new dataset
   Dim newDataset As New DataSet("Orders")

   ' Create a new table to hold the orders in...
   Dim ordersTable As New DataTable("Order")
   newDataset.Tables.Add(ordersTable)

   ' Load the last two orders from the database...
   Dim command As New SqlCommand("SELECT TOP 2 OrderID, CustomerID, " & _
        "EmployeeID, OrderDate, RequiredDate, ShippedDate, " & _
        "ShipVia, Freight, ShipName, ShipAddress, ShipCity, " & _
        "ShipRegion, ShipPostalCode, ShipCountry FROM Orders " & _
        "ORDER BY OrderID DESC", connection)

   ' Fill the DataSet...
   Dim adapter As New SqlDataAdapter(command)
   adapter.Fill(ordersTable)
 

   ' Create a new table to hold the order details on...
   Dim detailsTable As New DataTable("Detail")
   newDataset.Tables.Add(detailsTable)

   ' Form a SQL string so that we only get the details that are
   ' included in the first table...
   Dim sql As String, row As DataRow
   For Each row In ordersTable.Rows

     ' Create a SQL snippet...
     If sql <> "" Then sql &= " or "
     sql &= "OrderID=" & row("orderid")

   Next

   ' Do we need to bother?
   If sql <> "" Then

     ' Create a new command...
     sql = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " & _
                       "FROM [Order Details] WHERE " & sql
     Dim detailsCommand As New SqlCommand(sql, connection)

     ' Fill the new table...
     Dim detailsAdapter As New SqlDataAdapter(detailsCommand)
     detailsAdapter.Fill(detailsTable)

     ' Create the new relationship...
     newDataset.Relations.Add("Details", _
           ordersTable.Columns("OrderID"), detailsTable.Columns("OrderID"))

   End If


   ' Set the DataSet property...

   Me.DataSet = newDataset

   ' Save the schema...
   newDataset.WriteXmlSchema(SchemaFilename)

   ' Close the database...
   connection.Close()

 End Sub

  1. Run the project and click Connect. You'll be able to use the "plus" buttons to show the Details link.

  1. If you click on one of the Details links, you'll be able to see the related data.

How It Works

None of that should be too new to you, as I'm sure you're comfortable with linking tables with DataRelation objects in this way. (See Chapter 7 for more details.)

It's worth taking a quick look at a portion of the code that we added. When we load a list of orders from the database into ordersTable, we need to load corresponding details in detailsTable. The way we do this is by looping through all the rows in ordersTable and creating a SQL snippet.

   ' Form a SQL string so that we only get the details that are
   ' included in the first table...
   Dim sql As String, row As DataRow
   For Each row In ordersTable.Rows

     ' Create a SQL snippet...
     If sql <> "" Then sql &= " or "
     sql &= "OrderID=" & row("orderid")

   Next

Once we've been through that loop, our sql variable will be set to:

OrderID=11077 or OrderID=11076

We can combine this with the larger SQL statement to get this:

SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details]
  WHERE OrderID=11077 OR OrderID=11076

&and that's precisely what we do next.

   ' Do we need to bother?
   If sql <> "" Then

     ' Create a new command...
     sql = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " & _
                   "FROM [Order Details] WHERE " & sql
     Dim detailsCommand As New SqlCommand(sql, connection)

Once we have the command, we can populate the detailsTable DataTable object as normal:

     ' Fill the new table...
     Dim detailsAdapter As New SqlDataAdapter(detailsCommand)
     detailsAdapter.Fill(detailsTable)

Finally, we create a relationship so that DataGrid knows how to present the data:

     ' Create the new relationship...
     newDataset.Relations.Add("Details", _
           ordersTable.Columns("OrderID"), detailsTable.Columns("OrderID"))

   End If

Now that we've proven we can load relational data, we need to look at what effect this has on our code to write the XML file.

Saving the DataSet

Saving the data isn't even worth a "Try It Out&How It Works"! Run the project, click Connect, and then click Save. Save the file with a different name to the one you used before.

Now find the file in Windows Explorer and open it. I've omitted quite a bit of XML here for brevity.

<Orders>
 <Order>
  <OrderID>11077</OrderID>
  <CustomerID>RATTC</CustomerID>
  <EmployeeID>1</EmployeeID>
  <OrderDate>1998-05-06T00:00:00.0000000+01:00</OrderDate>
  <RequiredDate>1998-06-03T00:00:00.0000000+01:00</RequiredDate>
  <ShipVia>2</ShipVia>
  <Freight>8.53</Freight>
  <ShipName>Rattlesnake Canyon Grocery</ShipName>
  <ShipAddress>2817 Milton Dr.</ShipAddress>
  <ShipCity>Albuquerque</ShipCity>
  <ShipRegion>NM</ShipRegion>
  <ShipPostalCode>87110</ShipPostalCode>
  <ShipCountry>USA</ShipCountry>
 </Order>
 <Order>
  &
 </Order>
 <Detail>
  &
 </Detail>
 <Detail>
  &
 </Detail>
 <Detail>
  &
 </Detail>
 <Detail>
  <OrderID>11077</OrderID>
  <ProductID>2</ProductID>
  <UnitPrice>19</UnitPrice>
  <Quantity>24</Quantity>
  <Discount>0.2</Discount>
 </Detail>
&
</Orders>

First of all, what's important here is noticing that we don't need to change the code that saves the DataSet as an XML file, even though we've changed the structure of the DataSet.

What's also important is that there's no physical link in the XML file between details and orders. It would make sense that the three Detail elements associated with order 11077 actually appeared within the Order element for 11077.

But, for now, just bear in mind that we've changed the structure of the DataSet, yet the data can still be written out as XML without any changes.

Loading the DataSet Again

OK, so when we clicked the Connect button, we made a call to DataSet.WriteXmlSchema and created a new schema. This schema contains the details for the Detail elements and also contains details of the relationship between Order and Detail elements.

If you open the OrdersSchema.xml file again, towards the bottom you'll find this:

<xsd:element name="Detail">
<xsd:complexType>
<xsd:sequence>
 <xsd:element name="OrderID" type="xsd:int" minOccurs="0" />

 <xsd:element name="ProductID" type="xsd:int" minOccurs="0" />
 <xsd:element name="UnitPrice" type="xsd:decimal" minOccurs="0" />
 <xsd:element name="Quantity" type="xsd:short" minOccurs="0" />
 <xsd:element name="Discount" type="xsd:float" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>

Again, nothing complex there, we're just defining an element called Detail and specifying the five elements that it contains. Notice that Detail and Order appear as sibling elements in the document they're at the same level. They'll appear as siblings to each other in the final XML document too.

Beneath, you'll see this:

<xsd:unique name="Constraint1">
 <xsd:selector xpath=".//Order" />
 <xsd:field xpath="OrderID" />
</xsd:unique>

This block is used to create a constraint, specifically one called Constraint1 that's used to specify that the OrderID element contained within the Order element is unique.

Finally, you'll see this:

<xsd:keyref name="Details" refer="Constraint1">
 <xsd:selector xpath=".//Detail" />
 <xsd:field xpath="OrderID" />
</xsd:keyref>

What this tells us is that we have a relationship called Details that links Constraint1 to the OrderID element within Detail elements. By definition, Constraint1 refers to the OrderID element within Order elements and therefore we know that the OrderID in Order links to the OrderID in Detail.

Now, run the project and click Connect and then Load. You'll see this:

The trick here is that we've continued to use ReadXmlSchema as soon as the new DataSet is created. (I've omitted code here for brevity.)

 Private Sub btnLoad_Click(ByVal sender As System.Object, _
               ByVal e As System.EventArgs) Handles btnLoad.Click

   ' Display the dialog...
   dlgOpenFile.Filter = "XML Files (*.xml)|*.xml|All Files (*.*)|*.*||"
   If dlgOpenFile.ShowDialog() = DialogResult.OK Then

     ' Try and load...
     Try
       ' Create a new DataSet...
       Dim newDataset As New DataSet()

       ' Load the schema...

       newDataset.ReadXmlSchema(SchemaFilename)

       ' Create a validating reader...
              &
       ' If we got here, we can load it...
       Me.DataSet = newDataset
     Catch ex As Exception
            &
     End Try
   End If

 End Sub

Because the schema contains details on the relationship between Orders and Order Details, when the data is loaded from XML, the relationship "sticks" and the DataGrid is able to present the information properly.

Remember, because we've created a new OrdersSchema.xml file, you won't be able to load the old XML files that don't contain Detail elements.


Page 4 of 7

Go to page: 1   2   3   4   5   6   7   

12

ADO.NET and XML

  Contact Us |  | Site Guide | About PerfectXML | Advertise ©2004 perfectxml.com. All rights reserved. | Privacy