ONDotNet.com    
 Published on ONDotNet.com (http://www.ondotnet.com/)
 See this if you're having trouble printing code examples


O'Reilly Book Excerpts: Programming Visual Basic .NET

ADO.NET, Part 3

Related Reading

Programming Visual Basic .NET
By Dave Grundgeiger

by Dave Grundgeiger

This is the third installment from the Programming Visual Basic .NET chapter on ADO.NET, focusing on the relations between DataTables in a DataSet, and the DataSets XML capabilities.

Relations Between DataTables in a DataSet

The DataSet class provides a mechanism for specifying relations between tables in a DataSet. The DataSet class's Relations property contains a RelationsCollection object, which maintains a collection of DataRelation objects. Each DataRelation object represents a parent/child relationship between two tables in the DataSet. For example, there is conceptually a parent/child relationship between a Customers table and an Orders table, because each order must belong to some customer. Modeling this relationship in the DataSet has these benefits:

Example 8-4 loads a Customers table and an Orders table from the Northwind database and then creates a relation between them. The statement that actually creates the relation is shown in bold.

Example 8-4: Creating a DataRelation between DataTables in a DataSet

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data adapter object.
Dim strSql As String = "SELECT * FROM Customers" _
   & " WHERE City = 'Buenos Aires' AND Country = 'Argentina'"
Dim da As SqlDataAdapter = New SqlDataAdapter(strSql, cn)
 
' Load a data set.
Dim ds As DataSet = New DataSet(  )
da.Fill(ds, "Customers")
 
' Set up a new data adapter object.
strSql = "SELECT Orders.*" _
   & " FROM Customers, Orders" _
   & " WHERE (Customers.CustomerID = Orders.CustomerID)" _
   & "    AND (Customers.City = 'Buenos Aires')" _
   & "    AND (Customers.Country = 'Argentina')"
da = New SqlDataAdapter(strSql, cn)
 
' Load the data set.
da.Fill(ds, "Orders")
 
' Close the database connection.
cn.Close(  )
 
' Create a relation.
ds.Relations.Add("CustomerOrders", _
   ds.Tables("Customers").Columns("CustomerID"), _
   ds.Tables("Orders").Columns("CustomerID"))

As shown in Example 8-4, the DataRelationCollection object's Add method creates a new relation between two tables in the DataSet. The Add method is overloaded. The syntax used in Example 8-4 is:

Public Overloads Overridable Function Add( _
   ByVal name As String, _
   ByVal parentColumn As System.Data.DataColumn, _
   ByVal childColumn As System.Data.DataColumn _
) As System.Data.DataRelation

The parameters are:

name
The name to give to the new relation. This name can be used later as an index to the RelationsCollection object.

parentColumn
The DataColumn object representing the parent column.

childColumn
The DataColumn object representing the child column.

The return value is the newly created DataRelation object. Example 8-4 ignores the return value.

The DataSet's XML Capabilities

Related Reading

Programming Visual Basic .NETProgramming Visual Basic .NET
By Dave Grundgeiger
Table of Contents
Index
Sample Chapter
Full Description

The DataSet class has several methods for reading and writing data as XML, including:

GetXml
Returns a string containing an XML representation of the data in the DataSet object.

GetXmlSchema
Returns a string containing the XSD schema for the XML returned by the GetXml method.

WriteXml
Writes the XML representation of the data in the DataSet object to a Stream object, a file, a TextWriter object, or an XmlWriter object. This XML can either include or omit the corresponding XSD schema.

WriteXmlSchema
Writes the XSD schema for the DataSet to a Stream object, a file, a TextWriter object, or an XmlWriter object.

ReadXml
Reads the XML written by the WriteXml method.

ReadXmlSchema
Reads the XSD schema written by the WriteXmlSchema method.

Example 8-5 shows how to write a DataSet to a file as XML using the WriteXml method.

Example 8-5: Saving a DataSet to a file as XML

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data adapter object.
Dim strSql As String = "SELECT * FROM Customers" _
   & " WHERE CustomerID = 'GROSR'"
Dim da As SqlDataAdapter = New SqlDataAdapter(strSql, cn)
 
' Load a data set.
Dim ds As DataSet = New DataSet("MyDataSetName")
da.Fill(ds, "Customers")
 
' Set up a new data adapter object.
strSql = "SELECT Orders.*" _
   & " FROM Customers, Orders" _
   & " WHERE (Customers.CustomerID = Orders.CustomerID)" _
   & "    AND (Customers.CustomerID = 'GROSR')"
da = New SqlDataAdapter(strSql, cn)
 
' Load the data set.
da.Fill(ds, "Orders")
 
' Close the database connection.
cn.Close(  )
 
' Create a relation.
ds.Relations.Add("CustomerOrders", _
   ds.Tables("Customers").Columns("CustomerID"), _
   ds.Tables("Orders").Columns("CustomerID"))
 
' Save as XML.
ds.WriteXml("c:\temp.xml")

The majority of the code in Example 8-5 simply loads the DataSet with data. Actually writing the XML is done with the DataSet's WriteXml method at the end of Example 8-5. The contents of the file thus created are shown in Example 8-6. Some lines in Example 8-6 have been wrapped for printing in this book.

Example 8-6: The file produced by the code in Example 8-5

<?xml version="1.0" standalone="yes"?>
<MyDataSetName>
  <Customers>
    <CustomerID>GROSR</CustomerID>
    <CompanyName>GROSELLA-Restaurante</CompanyName>
    <ContactName>Manuel Pereira</ContactName>
    <ContactTitle>Owner</ContactTitle>
    <Address>5th Ave. Los Palos Grandes</Address>
    <City>Caracas</City>
    <Region>DF</Region>
    <PostalCode>1081</PostalCode>
    <Country>Venezuela</Country>
    <Phone>(2) 283-2951</Phone>
    <Fax>(2) 283-3397</Fax>
  </Customers>
  <Orders>
    <OrderID>10268</OrderID>
    <CustomerID>GROSR</CustomerID>
    <EmployeeID>8</EmployeeID>
    <OrderDate>1996-07-30T00:00:00.0000000-05:00</OrderDate>
    <RequiredDate>1996-08-27T00:00:00.0000000-05:00</RequiredDate>
    <ShippedDate>1996-08-02T00:00:00.0000000-05:00</ShippedDate>
    <ShipVia>3</ShipVia>
    <Freight>66.29</Freight>
    <ShipName>GROSELLA-Restaurante</ShipName>
    <ShipAddress>5th Ave. Los Palos Grandes</ShipAddress>
    <ShipCity>Caracas</ShipCity>
    <ShipRegion>DF</ShipRegion>
    <ShipPostalCode>1081</ShipPostalCode>
    <ShipCountry>Venezuela</ShipCountry>
  </Orders>
  <Orders>
    <OrderID>10785</OrderID>
    <CustomerID>GROSR</CustomerID>
    <EmployeeID>1</EmployeeID>
    <OrderDate>1997-12-18T00:00:00.0000000-06:00</OrderDate>
    <RequiredDate>1998-01-15T00:00:00.0000000-06:00</RequiredDate>
    <ShippedDate>1997-12-24T00:00:00.0000000-06:00</ShippedDate>
    <ShipVia>3</ShipVia>
    <Freight>1.51</Freight>
    <ShipName>GROSELLA-Restaurante</ShipName>
    <ShipAddress>5th Ave. Los Palos Grandes</ShipAddress>
    <ShipCity>Caracas</ShipCity>
    <ShipRegion>DF</ShipRegion>
    <ShipPostalCode>1081</ShipPostalCode>
    <ShipCountry>Venezuela</ShipCountry>
  </Orders>
</MyDataSetName>

The syntax of this overloaded version of the WriteXml function is:

Public Overloads Sub WriteXml(ByVal fileName As String)

The fileName parameter specifies the full path of a file into which to write the XML.

The XML document written by the DataSet class's WriteXml method can be read back into a DataSet object using the ReadXml method. Example 8-7 reads back the file written by the code in Example 8-5.

Example 8-7: Recreating a DataSet object from XML

Dim ds As New DataSet(  )
ds.ReadXml("c:\temp.xml")

The XML created by the WriteXml method contains only data--no schema information. The ReadXml method is able to infer the schema from the data. To explicitly write the schema information, use the WriteXmlSchema method. To read the schema back in, use the ReadXmlSchema method.

The GetXml and GetXmlSchema methods work the same as the WriteXml and WriteXmlSchema methods, except that each returns its result as a string rather than writing it to a file.

The next installment will focus on binding DataSets.


O'Reilly & Associates recently released (December 2002) Programming Visual Basic .NET.


View catalog information for Programming Visual Basic .NET

Return to the .NET DevCenter.

Copyright © 2009 O'Reilly Media, Inc.