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:

• The DataSet can enforce relational integrity.
• The DataSet can propagate key updates and row deletions.
• Data-bound controls can provide a visual representation of the relation.

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.

Pages: 1, 2

Next Page