Defining Database Relationships

From a developer's perspective, .NET is a framework. There are marketing aspects to .NET too (which have led to a bit of confusion), but as developers we care about what the framework means and does for us. The .NET Framework is an object-oriented framework that ultimately conceals the underlying operating system from us. (There is even rampant speculation about porting .NET to some other operating system, like Linux.)

One of the many things that have been captured as classes is the notion of a relationship between data entities. This class is the DataRelation class, defined in System.Data . The DataRelation class is provider- agnostic ; you use the same DataRelation class for SQL Server 7.0 that you use for IBM's UDB. The basic idea is that you read tables of data into a DataSet object and create instances of DataRelation objects that express the relationships between the entities. In this way you have both homogeneous tables in the DataSet and the ability to display these homogeneous tables in new heterogeneous ways.

Again, due to the general familiarity with the Northwind database, I have elected to use it for demonstration purposes. (Refer to the Tip below for several other excellent example databases that include .NET applications for you to experiment with and learn from.) Listing 11.6 demonstrates how to read two DataTable objects, express a DataRelation , and display this information in a DataGrid using a view.

Listing 11.6 Defining a Master “Detail Relationship by Using a DataRelation Object

 Private Sub InitializeGridWithView()   Const Customers As String = "Customers"   Const Orders As String = "Orders"   Dim Connection As OleDbConnection = _     New OleDbConnection(Database.ConnectionString)   Dim Adapter As OleDbDataAdapter = _     New OleDbDataAdapter("SELECT * FROM CUSTOMERS", Connection)   Dim DataSet As DataSet = New DataSet("CustomerOrders")   Adapter.Fill(DataSet, Customers)   Dim Adapter2 As OleDbDataAdapter = _     New OleDbDataAdapter("SELECT * FROM ORDERS", Connection)   Adapter2.Fill(DataSet, Orders)  Dim Relation As DataRelation = _   New DataRelation("CustomerOrders", _   DataSet.Tables(Customers).Columns("CustomerID"), _   DataSet.Tables(Orders).Columns("CustomerID"))   DataSet.Relations.Add(Relation)  ' DataGrid Web Control shows the first table   ' DataGrid for Windows shows all tables   DataGrid1.DataSource = DataSet   DataGrid1.DataBind() End Sub 

The example code repeats the connection, adapter, and DataSet usage, placing the Orders and Customers tables into the DataSet . The code in bold font in Listing 11.6 demonstrates how to create a DataRelation object explicitly from two columns, one from each table. The relationship is that "customers have orders," which is expressed through the primary key Customers.CustomerID and the foreign key Orders.CustomerID . The last step is to add the DataRelation to the DataSet Relations collection.

You can express multiple relationships for each table in the DataSet . In the example the Web control DataGrid1 shows the first table. If the grid in the listing were a Windows control, the code in Listing 11.6 would display links allowing a user to navigate through the rows, taking advantage of the expressed DataRelation .

TIP

You have several good options for sample Web and database applications for .NET. You can install the Fitch & Mather Stocks, Duwamish Books, or the IBuySpy portal applications. Fitch & Mather and Duwamish ship with Visual Studio .NET Enterprise Architect, and IBuySpy is available for download from Microsoft.