|
Related Reading 
Programming Visual Basic .NET |
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.
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:
The return value is the newly created DataRelation object. Example 8-4 ignores the return value.
|
|
Related Reading
|
The DataSet class has several methods for reading and writing data as XML, including:
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.
Sample Chapter 8, ADO.NET: Developing Database Applications, is available free online.
You can also look at the Table of Contents, the Index, and the Full Description of the book.
For more information, or to order the book, click here.
View catalog information for Programming Visual Basic .NET
Return to the .NET DevCenter.
Copyright © 2009 O'Reilly Media, Inc.