O'Reilly Book Excerpts: Programming Visual Basic .NET
ADO.NET, Part 3
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"))
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 name to give to the new relation. This name can be used later as an index to the RelationsCollection object.
- The DataColumn object representing the parent column.
- 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