Questions That Should Be Asked More Frequently
Working with DataRelation Objects in Code
You can navigate through multiple tables of data, validate data, aggregate data, and cascade changes using your own code, but you can perform all of these functions quickly and easily with the help of the ADO.NET DataRelation object. Let's look at how to create and use DataRelation objects in code.
Creating DataRelation Objects
The DataRelation object has a few important properties, which you can set in its constructors. When you create a DataRelation, you should provide a name so that you can locate the object in its collection as well as specify the parent and child columns on which to base the relationship. To simplify creation, the DataRelation has separate constructors that accept single DataColumn objects and arrays of DataColumn objects.
The standard example of creating a relationship relies on DataTable objects that contain customer and order information, such as that shown in Figure 7-3. You can use the following code snippet to create that DataRelation:
Visual Basic .NET
'Create a new DataSet and add DataTable and DataColumn objects. Dim ds As New DataSet()'Add a DataRelation between the two tables. Dim rel As DataRelation rel = New DataRelation("CustomersOrders", _ ds.Tables("Customers").Columns("CustomerID"), _ ds.Tables("Orders").Columns("CustomerID")) ds.Relationships.Add(rel)
Visual C# .NET
//Create a new DataSet and add DataTable and DataColumn objects. DataSet ds = new DataSet();
Figure 7-3
Displaying related information
If you want to define a relationship that's based on multiple columns, you can use a constructor of the DataRelation that accepts arrays of DataColumn objects, as shown in the following code snippet:
Visual Basic .NET
'Create a new DataSet and add DataTable and DataColumn objects. Dim ds As New DataSet()
Visual C# .NET
//Create a new DataSet and add DataTable and DataColumn objects. DataSet ds = new DataSet();
The DataRelation also has a pair of constructors whose signatures match the ones we just examined but also expose a fourth parameter to indicate whether to create constraints to enforce referential integrity based on the new relation. By default, creating a new DataRelation adds constraints to your DataTable objects if such constraints do not already exist. We'll take a closer look at this functionality shortly.
Once you've created a new DataRelation, you should append it to the Relations collection of your DataSet. As with creating new DataTable objects and DataColumn objects, you can create a new DataRelation and append it to a DataSet object's Relations collection in a single call using code such as the following:
Visual Basic .NET
'Create a new DataSet and add DataTable and DataColumn objects. Dim ds As New DataSet()
Visual C# .NET
//Create a new DataSet and add DataTable and DataColumn objects. DataSet ds = new DataSet();
Locating Related Data
One of the main uses of the DataRelation object is to locate related data in different DataTable objects. However, the DataRelation object does not handle this task, at least not directly. This functionality is actually available through the DataRow object's GetChildRows, GetParentRow, and GetParentRows methods. How does the DataRelation enter into the equation? When you call any of these methods on the DataRow object, you specify a DataRelation as a parameter of the method. Let's take a closer look at these methods and how to use them.
The DataRow Object's GetChildRows Method
Locating a row's related child rows in another DataTable is rather straightforward. You simply call the GetChildRows method of your DataRow and supply the name of the DataRelation object that defines the relationship between your DataTable objects. You can also supply the actual DataRelation object instead of the object's name. The GetChildRows method returns the related data as an array of DataRow objects.
The following code snippet calls the GetChildRows method and loops through the data it returns:
Visual Basic .NET
'Loop through the customers. Dim rowCustomer, rowOrder As DataRow For Each rowCustomer In ds.Tables("Customers").Rows Console.WriteLine(rowCustomer("CustomerID") & " - " & _ rowCustomer("CompanyName")) 'Loop through the related orders. For Each rowOrder In rowCustomer.GetChildRows("RelationName") Console.WriteLine(vbTab & rowOrder("OrderID") & " - " & _ rowOrder("OrderDate")) Next rowOrder Next rowCustomer
Visual C# .NET
//Loop through the customers. foreach (DataRow rowCustomer in ds.Tables["Customers"].Rows) { Console.WriteLine(rowCustomer["CustomerID"] + " - " + rowCustomer["CompanyName"]); //Loop through the related orders. foreach (DataRow rowOrder in rowCustomer.GetChildRows("RelationName")) Console.WriteLine("\t" + rowOrder["OrderID"] + " - " + rowOrder["OrderDate"]); }
The DataRow Object's GetParentRow Method
DataRelation objects let you not only drill down through a hierarchy but also travel upstream. The DataRow object exposes a GetParentRow method that you can call to locate that row's parent row based on a DataRelation within the DataSet. Like the GetChildRows method, GetParentRow accepts a DataRelation object or a string that contains the name of the DataRelation you want to use, as shown in the following code:
Visual Basic .NET
Dim rowCustomer, rowOrder As DataRow 'Loop through the orders. For Each rowOrder In ds.Tables("Orders").Rows Console.Write(rowOrder("OrderID") & vbTab & rowOrder("OrderDate")) 'Locate the related parent row. rowCustomer = rowOrder.GetParentRow("CustomersOrders") Console.WriteLine(vbTab & rowCustomer("CompanyName")) Next rowOrder
Visual C# .NET
DataRow rowCustomer; //Loop through the orders. foreach (DataRow rowOrder in ds.Tables["Orders"].Rows) { Console.Write(rowOrder["OrderID"] + "\t" + rowOrder["OrderDate"]); //Locate the related parent row. rowCustomer = rowOrder.GetParentRow("CustomersOrders"); Console.WriteLine("\t" + rowCustomer["CompanyName"]); }
The DataRow Object's GetParentRows Method
If the relationship that you're working with is a many-to-many relationship and you want to examine all parent rows for a particular DataRow, you can use the DataRow object's GetParentRows method. The signatures for this method are identical to that of the GetChildRows method, as shown in the following code:
Visual Basic .NET
'Loop through the customers. Dim rowChild, rowParent As DataRow For Each rowChild In ds.Tables("ChildTable").Rows 'Loop through the parent rows. Console.WriteLine(rowChild("ChildName")) For Each rowParent In rowChild.GetParentRows("RelationName") Console.WriteLine(vbTab & rowParent("ParentName")) Next rowParent Next rowChild
Visual C# .NET
//Loop through the customers. foreach (DataRow rowChild in ds.Tables["ChildTable"].Rows) { Console.WriteLine(rowChild["ChildName"]); //Loop through the related orders. foreach (DataRow rowParent in rowChild.GetChildRows("RelationName")) Console.WriteLine("\t" + rowParent["ParentName"]); }
 |  |
 | Few, if any, relationships in a database are truly many-to-many. You'll see why later in the chapter. |
 |  |
Choosing the Version of Data to View
Imagine that you've already built an application that allows users to retrieve data from your database and modify that data. But the employees who'll use that application aren't terribly reliable; they're prone to making mistakes. So the application uses some functionality exposed by the DataSet to store changes in a file rather than submit them to the database. (We'll discuss this feature further in Chapter 12.)
As a result, you must create a second application that allows supervisors to review the pending changes entered by employees using the first application. This auditing application will display both the original and proposed values for the modified rows in your DataSet.
In Chapter 6, you learned that you could use the DataRow object's Item method to examine either the original or the current version of the data in a particular column of that row. The DataRow object's GetChildRows, GetParentRow, and GetParentRows methods also let you supply a value from the DataRowVersion enumeration to indicate which version of the data you want to access.
So, if you want to loop through the customers and display the original values for each order that the customer has placed, you can use code that looks something like this:
Visual Basic .NET
'Loop through the customers. Dim rowCustomer, rowOrder As DataRow For Each rowCustomer In ds.Tables("Customers").Rows Console.WriteLine(rowCustomer("CustomerID") & " - " & _ rowCustomer("CompanyName")) 'Display the original state of the related child orders. For Each rowOrder In rowCustomer.GetChildRows("RelationName", _ DataRowVersion.Original) Console.WriteLine(vbTab & rowOrder("OrderID") & " - " & _ rowOrder("OrderDate")) Next rowOrder Next rowCustomer
Visual C# .NET
//Loop through the customers. foreach (DataRow rowCustomer in ds.Tables["Customers"].Rows) { Console.WriteLine(rowCustomer["CustomerID"] + " - " + rowCustomer["CompanyName"]); //Display the original state of the related child orders. foreach (DataRow rowOrder in rowCustomer.GetChildRows("RelationName", DataRowVersion.Original)) Console.WriteLine("\t" + rowOrder["OrderID"] + " - " + rowOrder["OrderDate"]); }
| |
| If you use the methods that do not take a value from DataRowVersion, you'll view the current version of the data. |
| |
Using DataRelation Objects to Validate Your Data
Now that you've learned how to use DataRelation objects to navigate through data from related DataTable objects, let's look at one of the other major functions of the DataRelation object—validating data.
When you define a relationship between two DataTable objects, you generally want to make sure that you don't allow "orphaned" data in the child DataTable—that is, you want to prevent users from entering a row into the Orders table that does not correspond to a row in the Customers table. You can use a DataRelation object to enforce constraints on the related DataTable objects.
Creating Constraints
By default, when you create a DataRelation, you ensure that there is a unique constraint on the parent DataTable and a foreign key constraint on the child DataTable. The following code snippet creates a unique constraint on the CustomerID column in the Customers DataTable and a foreign key constraint on the CustomerID column in the Orders DataTable.
Visual Basic .NET
Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() strSQL = "SELECT CustomerID, CompanyName, ContactName FROM Customers" Dim daCustomers As New OleDbDataAdapter(strSQL, cn) strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders" Dim daOrders As New OleDbDataAdapter(strSQL, cn) Dim ds As New DataSet() daCustomers.Fill(ds, "Customers") daOrders.Fill(ds, "Orders") cn.Close() ds.Relationships.Add("CustomersOrders", _ ds.Tables("Customers").Columns("CustomerID"), _ ds.Tables("Orders").Columns("CustomerID")) Console.WriteLine("The parent DataTable now contains " & _ ds.Tables("Customers").Constraints.Count & _ " constraints") Console.WriteLine("The child DataTable now contains " & _ ds.Tables("Orders").Constraints.Count & _ " constraints")
Visual C# .NET
string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); strSQL = "SELECT CustomerID, CompanyName, ContactName FROM Customers"; OleDbDataAdapter daCustomers = new OleDbDataAdapter(strSQL, cn); strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"; OleDbDataAdapter daOrders = new OleDbDataAdapter(strSQL, cn); DataSet ds = new DataSet(); daCustomers.Fill(ds, "Customers"); daOrders.Fill(ds, "Orders"); cn.Close(); ds.Relationships.Add("CustomersOrders", ds.Tables["Customers"].Columns["CustomerID"], ds.Tables["Orders"].Columns["CustomerID"]); Console.WriteLine("The parent DataTable now contains " + ds.Tables["Customers"].Constraints.Count + " constraints"); Console.WriteLine("The child DataTable now contains " + ds.Tables["Orders"].Constraints.Count + " constraints");
Using Existing Constraints
We can also define our constraints ahead of time; the new DataRelation will use the existing constraints rather than create new ones, as shown in the following code:
Visual Basic .NET
Visual C# .NET
Foreign Key Constraints and Null Values
You might be surprised by what you learn in the course of writing a book. For example, I had no idea that even with a foreign key constraint defined, you can have "orphaned" data both in your database and in your DataSet.
You don't believe me? You can run the following query against your favorite Northwind database. (Don't run queries like this against your production database while you're building applications or learning about ADO.NET.)
UPDATE Orders SET CustomerID = NULL WHERE CustomerID = 'ALFKI'
The query will succeed, and you'll have orders in your Orders table that don't belong to a customer in the Customers table. To set the rows back to the appropriate customer, run the following query:
UPDATE Orders SET CustomerID = 'ALFKI' WHERE CustomerID IS NULL
To prove that there is a foreign key constraint on the Orders table, run the following query, which will fail if your Customers table has no row with a CustomerID of ZZZZZ:
UPDATE Orders SET CustomerID = 'ZZZZZ' WHERE CustomerID = 'ANTON'
Rows that contain Null values in at least one of the columns defined in the foreign key constraint are exempt from the constraint. Keep this in mind when you define the schema for your database and DataSet.
Look, Ma! No Constraints!
You've learned that when you create a DataRelation, ADO.NET will by default ensure that your DataSet contains a UniqueConstraint and a ForeignKeyConstraint whose signatures match that of your new DataRelation. If such constraints already exist within the DataSet, the new DataRelation will reference them. Otherwise, ADO.NET will create the new constraints implicitly.
However, there's another option. Earlier, when we discussed the DataRelation class's constructors, I mentioned that there are constructors that let you specify that you do not want ADO.NET to create constraints for your DataRelation. You can use these constructors when you want to use a DataRelation but do not want the corresponding constraints in your DataSet.
Self-Referencing DataRelationship Objects
Sometimes the parent and child tables in a relationship are one and the same. Take the Employees table in the Northwind database. This table has an EmployeeID column that contains the employee's ID and a ReportsTo column that contains the ID of the employee's manager. The Employees table also has a foreign key constraint defined on the ReportsTo column to ensure that it accepts values from only the EmployeeID column.
The following sample code retrieves data from the Employees table into a DataSet and creates a self-referencing DataRelation:
Visual Basic .NET
Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT EmployeeID, ReportsTo, " & "LastName + ', ' + FirstName AS EmployeeName FROM Employees" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, "Employees") Dim tbl As DataTable = ds.Tables("Employees") ds.Relations.Add("SelfReferencing", tbl.Columns("EmployeeID"), _ tblColumns("ReportsTo"), False)
Visual C# .NET
string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT EmployeeID, ReportsTo, " + "LastName + ', ' + FirstName AS EmployeeName FROM Employees"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, "Employees"); DataTable tbl = ds.Tables["Employees"]; ds.Relations.Add("SelfReferencing", tbl.Columns["EmployeeID"], tbl.Columns["ReportsTo"], false); Creating the DataRelation is only half the battle. The actual goal is to display all employees as a tree, as shown in Figure 7-4. Traversing the hierarchy to display the employees according to their manager is a bit of a challenge, especially if you've never written recursive code. If that's the case, you'd be best served by searching the documentation for your language of choice for information on recursion because that topic is beyond the scope of this book.
Figure 7-4
Displaying the contents of the Employees table using a self-referencing DataRelation
The code snippet that follows loops through the contents of the DataTable to locate and display the highest-level employee. In the Employees table, this is the row whose ReportsTo column is set to Null. For more information on Null values and foreign key constraints, see the sidebar "Foreign Key Constraints and Null Values" earlier in the chapter.
After locating the highest-ranking employee, the code displays that employee's direct reports, printing a tab character before each employee's name. After displaying an employee's name, it displays that employee's direct reports. The process continues until there are no more direct reports to display.
Enough explaining—bring on the code!
Visual Basic .NET
Dim row As DataRow For Each row In tbl.Rows If row.IsNull("ReportsTo") Then DisplayRow(row, "") End If Next row Public Sub DisplayRow(DataRow row, string strIndent) Console.WriteLine(strIndent & row("EmployeeName")) Dim rowChild As DataRow For Each rowChild in row.GetChildRows("SelfReferencing") DisplayRow(rowChild, strIndent & vbTab) Next rowChild End Sub
Visual C# .NET
foreach (DataRow row in tbl.Rows) if (row.IsNull("ReportsTo")) DisplayRow(row, ""); static void DisplayRow(DataRow row, string strIndent) { Console.WriteLine(strIndent + row["EmployeeName"]); foreach (DataRow rowChild in row.GetChildRows("SelfReferencing")) DisplayRow(rowChild, strIndent + "\t"); }
| |
| The highest-ranking employee in the Employees table has a value of Null in the ReportsTo column. Another option is to have the employee report to himself or herself. You would need to change the sample code slightly if you're working with a table that handles self-referencing relationships in that fashion. |
| |
Many-to-Many Relationships
Most database relationships are one-to-many. A customer can have multiple orders. An order can have multiple order details. An employee can have multiple direct reports. Many-to-many relationships exist, but they're a little more difficult to define in a database. To understand why, let's take a look at the authors and titles tables in the SQL Server pubs database.
The relationship between the data in these two tables can be considered many-to-many because an author might have written multiple titles and a title might have multiple authors. However, these two tables are not directly related through a foreign key constraint because a foreign key constraint requires a unique key. That prevents a child row from having multiple parent rows in the related table, which means we don't have a direct many-to-many relationship.
The pubs database includes another table called titleauthor (shown in Figure 7-5) that helps create an indirect many-to-many relationship. The titleauthor table has a compound primary key made up of the au_id and title_id columns—the primary key columns of the authors and titles tables, respectively.
Figure 7-5
The authors, titles, and titleauthor tables in the SQL Server pubs database
Let's say that a title has two coauthors. The titleauthors table will contain two rows for that title, one for each author. So, you can use the titleauthors table to find the primary key values for all the coauthors of a particular title. Similarly, you can also use the table to locate the primary key values for all titles that an author has written or coauthored.
The following code retrieves data from all three tables. It adds DataRelation objects between the authors and titleauthor tables and the titles and titleauthor tables. It then loops through the rows in the authors DataTable, displaying each author and then using the two DataRelation objects to display each author's titles.
Visual Basic .NET
Dim strConn, strSQL as string strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() Dim daAuthors, daTitles, daTitleAuthor As OleDbDataAdapter strSQL = "SELECT au_id, au_lname, au_fname FROM authors" daAuthors = New OleDbDataAdapter(strSQL, cn) strSQL = "SELECT title_id, title FROM titles" daTitles = New OleDbDataAdapter(strSQL, cn) strSQL = "SELECT au_id, title_id FROM titleauthor" daTitleAuthor = New OleDbDataAdapter(strSQL, cn) Dim ds As New DataSet() daAuthors.Fill(ds, "authors") daTitles.Fill(ds, "titles") daTitleAuthor.Fill(ds, "titleauthor") cn.Close() ds.Relations.Add("authors_titleauthor", _ ds.Tables("authors").Columns("au_id"), _ ds.Tables("titleauthor").Columns("au_id"), _ False) ds.Relations.Add("titles_titleauthor", _ ds.Tables("titles").Columns("title_id"), _ ds.Tables("titleauthor").Columns("title_id"), _ False) Dim rowAuthor, rowTitle, rowTitleAuthor As DataRow For Each rowAuthor In ds.Tables("authors").Rows Console.WriteLine(rowAuthor("au_lname") & ", " & rowAuthor("au_fname")) For Each rowTitleAuthor In _ rowAuthor.GetChildRows("authors_titleauthor") rowTitle = rowTitleAuthor.GetParentRow("titles_titleauthor") Console.WriteLine(vbTab & rowTitle("title")) Next rowTitleAuthor Next rowAuthor
Visual C# .NET
string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); OleDbDataAdapter dsAuthors, dsTitles, dsTitleAuthor; strSQL = "SELECT au_id, au_lname, au_fname FROM authors"; daAuthors = new OleDbDataAdapter(strSQL, cn) strSQL = "SELECT title_id, title FROM titles"; daTitles = new OleDbDataAdapter(strSQL, cn); strSQL = "SELECT au_id, title_id FROM titleauthor"; daTitleAuthor = new OleDbDataAdapter(strSQL, cn); DataSet ds = new DataSet(); daAuthors.Fill(ds, "authors"); daTitles.Fill(ds, "titles"); daTitleAuthor.Fill(ds, "titleauthor"); cn.Close(); ds.Relations.Add("authors_titleauthor", ds.Tables["authors"].Columns["au_id"], ds.Tables["titleauthor"].Columns["au_id"], false); ds.Relations.Add("titles_titleauthor", ds.Tables["titles"].Columns["title_id"], ds.Tables["titleauthor"].Columns["title_id"], false); foreach (DataRow rowAuthor in ds.Tables["authors"].Rows) { Console.WriteLine(rowAuthor["au_lname"] + ", " + rowAuthor["au_fname"]); foreach (DataRow rowTitleAuthor in rowAuthor.GetChildRows("authors_titleauthor")) { DataRow rowTitle; rowTitle = rowTitleAuthor.GetParentRow("titles_titleauthor"); Console.WriteLine("\t" + rowTitle["title"]); } }
Using DataRelation Objects in Expression-Based DataColumn Objects
In Chapter 6, you learned how to use the DataColumn object's Expression property to create DataColumn objects that display the results of equations such as Quantity * UnitPrice. You can also use expression-based DataColumn objects in conjunction with DataRelation objects to calculate aggregate information such as the number of child rows and the sums and averages of child data.
The following code snippet includes two examples of using a DataRelation in a DataColumn object's Expression property. The code first creates a DataRelation between the Orders and Order Details DataTable objects, and then it adds the calculated ItemTotal DataColumn to the Order Details DataTable, as described earlier. Finally the code adds two expression-based DataColumn objects that rely on the new DataRelation.
The first of these two DataColumn objects returns the number of child rows. In order to gather this information, the code sets the Expression property of the DataColumn to the following:
Count(Child.ProductID)
You can use this syntax to refer to child data if the DataTable has only one related child DataTable. If you have multiple related child DataTable objects, you can use the following syntax:
Count(Child(RelationName).ProductID)
The final expression-based DataColumn that the code creates returns the sum of the ItemTotal DataColumn in the child DataTable. The value of this DataColumn object's Expression property is similar to the previous DataColumn:
Sum(Child.ItemTotal)
Visual Basic .NET
Dim ds As New DataSet()
Visual C# .NET
DataSet ds = new DataSet();
You can also use expression-based DataColumn objects to gather information from the parent DataTable in a relationship. Earlier in the chapter, we looked at a many-to-many relationship between author and title information in the pubs database. The goal of the code snippet was to list the titles that each author wrote or cowrote.
We can simplify that code slightly by adding an expression-based DataColumn to the titleauthor DataTable that uses the DataRelation to the titles DataTable to return the value of the title DataColumn. By adding this column to the titleauthor DataTable, we no longer need to use the GetParentRow method to locate the desired row in the titles DataTable.
Visual Basic .NET
Dim ds As New DataSet()
Visual C# .NET
DataSet ds = new DataSet();
For an exhaustive list of available aggregate functions in the Expression property, see the MSDN documentation.
Cascading Changes
Sometimes the changes you make to one row have, or should have, repercussions on related data. For example, when you delete an order, you probably want to delete the line items associated with the order as well.
Different database systems allow you to handle this situation in various ways. The Order Details table in the SQL Server Northwind database uses a foreign key constraint to prevent users from deleting rows from the Orders table if there are related rows in the Order Details table. SQL Server 2000 introduced support for cascading changes using a foreign key constraint. You can define your foreign key constraints so that when a user updates or deletes a row, the changes automatically cascade to the rows in the related table. Figure 7-6 shows the SQL Server 2000 user interface for setting these properties on a foreign key constraint.
Figure 7-6
Setting a foreign key constraint's cascading update attributes in SQL Server 2000
The ADO.NET ForeignKeyConstraint object has similar features. It exposes DeleteRule and UpdateRule properties that you can set to control what happens when you modify rows of data in the parent table in a foreign key constraint.
The ForeignKeyConstraint Object's DeleteRule and UpdateRule Properties
The DeleteRule and UpdateRule properties accept values from the Rule enumeration in the System.Data namespace. By default, both properties are set to Cascade. This means that when you delete a row in a DataTable, the child rows in the related DataTable will be deleted as well. If you change the value of the column in the parent DataTable on which the foreign key constraint is defined—for example, if you change the value of the CustomerID column in the Customers DataTable—the value of that column will also be updated in the related rows in the child DataTable.
You can also set either property to None, SetDefault, or SetNull. Setting the DeleteRule property to None will prevent the deletion from affecting data in the child DataTable. If you want a change to a parent row to set the constraint's columns in the child table to Null, set the DeleteRule property and/or the UpdateRule property to SetNull. Similarly, setting the properties to SetDefault will reset the constraint's columns in the child table to the value set in the Default property.
Moving Away from Join Queries
Many developers have relied on join queries to retrieve data from multiple tables. You can use a DataTable to store the results of a query that returns data from multiple tables, but I generally wouldn't recommend it. As you'll see in Chapter 10, the DataAdapter object is designed to examine the changes stored in a single DataTable and submit them to a specific table in your database. Thus, if you want to modify the contents of your DataTable objects, you'll want to separate the data into distinct DataTable objects that parallel the tables in your database.
So, what to do with join queries?
The simple answer is to split them up into queries that return data from distinct tables. However, this is often easier said than done. In the snippets of sample code, we've used very simple queries that return all rows from the table in the query. Things get more complex when you're working with a query that uses a filter such as this:
SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers WHERE Country = 'Canada'
If you want to retrieve data from the related Orders table, you'll want to retrieve only the orders that correspond to the customers returned by this query. Because the Orders table does not contain a Country column, you have to use a query that references the Customers table, such as this:
SELECT O.OrderID, O.CustomerID, O.EmployeeID, O.OrderDate FROM Customers C, Orders O WHERE C.CustomerID = O.OrderID AND C.Country = 'Canada'
EAN: 2147483647
Pages: 104