The DataSet contains a collection of DataRelation objects named, appropriately enough, DataRelationCollection . This collection stores all DataRelation s for the DataTable s in your DataSet . There's no practical limit to the number of relationships you can add to a DataSet .
Adding a relationship between two DataTable s is straightforward. You can simply add a new DataRelation object to the Relations collection of your DataSet as in Listing 4.4. The first argument to the Add() method is the name of the relationship. The second argument contains the parent DataColumn . The third argument contains the child DataColumn of the relationship.
The parent is defined as the column in the relationship that is a primary key. The other column is said to be a foreign key, and is also referred to as the child. The child column may contain as many values from the parent column as desired (even repeating the same value). Think of it this way: One employee can work on multiple projects; one customer can make multiple orders. In these cases, the employees and customers are the parents and the projects and orders are the children.
dsCompany.Relations.Add(" DataRelationName ", _ dsCompany.Tables(" Table1 ").Columns(" PrimaryKeyColumn "), _ dsCompany.Tables(" Table1 ").Columns(" ForeignKeyColumn "))
For instance, suppose you have two tables in your DataSet :
The first table named Customers contains a list of your customers, with a column named CustomerID set as the table's primary key.
The second table named Orders contains a list of your customers' orders, using a CustomerID column to keep track of which customer made which order. (In other words, the CustomerID in this table will become the foreign key, after the DataRelation is added.)
The primary key column does not need to have the same name as the foreign key column. Often, however, the two do share the same column name.
Adding a DataRelation to this table ensures that the CustomerID field of the Orders table contains only CustomerIDs from the Customers table. If you try to add a CustomerID to the Orders table that is not also present in the CustomerID field in the Customers table, you will receive an error (this applies to both tables in a database and DataTable s in a DataSet ). Listing 4.5 shows how to create a relationship between a Customers DataTable and an Orders DataTable using this example.
dsCompany.Relations.Add(" CustomerOrders ", _ dsCompany.Tables(" Customers ").Columns(" CustomerID "), _ dsCompany.Tables(" Orders ").Columns(" CustomerID "))
After you have added a relationship between the Customers DataTable and the Orders DataTable , you can easily retrieve all orders for a particular CustomerID without writing a SQL query. This is referred to as "navigating" a relationship.
When you have a DataRelation between two DataTable s, you can retrieve all child rows in the table with the foreign key for a particular row in the parent table by using the GetChildRows() method of the DataRow object. Building on the example from the previous section in Listing 4.5, the following code shows how to use the GetChildRows() method:
For the customer in the first row of the Customer DataTable , the preceding code returns any orders they have listed in the Orders table. It does this by using the CustomerOrders DataRelation name passed to it as its only argument. So, if the first customer has a CustomerID of 0, any rows in the Orders table that have a CustomerID of 0 are returned.
You can see a complete example using all of the concepts from this hour in Listing 4.6. This is a very long code listing, but it is also easily broken down into pieces that you have seen before, specifically in Hour 2, "Working with DataSets and DataTables." Because Listing 4.6 is so long, only the Visual Basic .NET code is provided. The C# code is available online at http://www. sams .com.
Listing 4.6 builds a DataSet with two DataTable s, creates a relationship between those two tables, and then steps through each row of the parent DataTable displaying each child row in the child table. In this case, the code will display a list of employees (the parent DataTable ) and a list of the projects each employee is assigned to (the child DataTable ). Keep in mind that the only new code in Listing 4.6 is the code with a grey background.
<% @Page Language="VB" Debug=true%> <%@ Import Namespace="System.Data" %> <HTML> <HEAD> <LINK rel="stylesheet" type="text/css" href="24Hours.css"> <!-- End Style Sheet --> <script language="VB" runat="server" > Sub Page_Load(Source as Object, E as EventArgs ) ' Create Principle Objects Dim dsCompany as new DataSet() ' Create new datatables Dim dtEmployees as DataTable = GenerateCompanyDataTable() Dim dtProjects as DataTable = GenerateProjectDataTable() ' Add new datatables to the dataset dsCompany.Tables.Add( dtEmployees ) dsCompany.Tables.Add( dtProjects ) ' Create Relationships between tables in datasets dsCompany.Relations.Add("EmployeeProjects", _ dsCompany.Tables("Employees").Columns("EmployeeID"), _ dsCompany.Tables("Projects").Columns("EmployeeID")) ' Bind to a datagrid to see DataTable data employees.DataSource = dtEmployees employees.DataBind() ' Bind to a datagrid to see DataTable data projects.DataSource = dtProjects projects.DataBind() Dim strBuilder as new StringBuilder() Dim EmployeeRow as DataRow Dim ProjectRow as DataRow ' Display Employees and the projects they are currently ' Assigned to for each EmployeeRow in dtEmployees.Rows strBuilder.Append("<BR>" + "***************************" + "<BR>") strBuilder.Append("EmployeeID: " + _ EmployeeRow("EmployeeID").ToString() + "<BR>" + _ "EmployeeName: " + EmployeeRow("FirstName").ToString() + _ " " + EmployeeRow("LastName").ToString() + "<BR>") for each projectRow in EmployeeRow.GetChildRows("EmployeeProjects") strBuilder.Append("<b>Currently working on:</b> " + "<BR>" + _ "ProjectID: " + _ projectRow("ProjectID").ToString() + "<BR>" + _ "ProjectName: " + _ projectRow("ProjectName").ToString() + "<BR>") next next lblProjects.Text = strBuilder.ToString() End Sub public Function GenerateCompanyDataTable() as DataTable 'Create Principle Objects Dim dtEmployees as new DataTable("Employees") 'Create Columns dtEmployees.Columns.Add("EmployeeID", Type.GetType("System.Int32")) dtEmployees.Columns.Add("FirstName", Type.GetType("System.String")) dtEmployees.Columns.Add("LastName", Type.GetType("System.String")) 'Make the first column autoincrementing dtEmployees.Columns(0).AutoIncrement = true 'Create column array Dim dcPrimaryKey(2) as DataColumn 'Place EmployeeID in the column array dcPrimaryKey(0) = dtEmployees.Columns("EmployeeID") 'Set the primary key for the table using the column array dtEmployees.PrimaryKey = dcPrimaryKey 'Create new row Dim workRow as DataRow = dtEmployees.NewRow() workRow("FirstName") = "John" workRow("LastName") = "Fruscella" dtEmployees.Rows.Add(workRow) 'Create another row Dim workRow1 as DataRow = dtEmployees.NewRow() workRow1("FirstName") = "Santa" workRow1("LastName") = "Claus" dtEmployees.Rows.Add(workRow1) Return dtEmployees End Function public Function GenerateProjectDataTable() as DataTable 'Create Principle Objects Dim dtProjects as DataTable = new DataTable("Projects") 'Create Columns dtProjects.Columns.Add("ProjectID", Type.GetType("System.Int32")) dtProjects.Columns.Add("EmployeeID", Type.GetType("System.Int32")) dtProjects.Columns.Add("ProjectName", Type.GetType("System.String")) dtProjects.Columns.Add("ProjectDescription", _ Type.GetType("System.String")) 'Make the first column autoincrementing dtProjects.Columns(0).AutoIncrement = true 'Create column array Dim dcPrimaryKey(2) as DataColumn 'Place EmployeeID in the column array dcPrimaryKey(0) = dtProjects.Columns("ProjectID") dcPrimaryKey(1) = dtProjects.Columns("EmployeeID") 'Set the primary key for the table using the column array dtProjects.PrimaryKey = dcPrimaryKey 'Create new row - Assign EmployeeID 2 to this project Dim workRow as DataRow = dtProjects.NewRow() workRow("EmployeeID") = 1 workRow("ProjectName") = "Landslide" workRow("ProjectDescription") = "Super secret Web services project." dtProjects.Rows.Add(workRow) 'Create new row - Assign EmployeeID 1 to this project Dim workRow1 as DataRow = dtProjects.NewRow() workRow1("EmployeeID") = 0 workRow1("ProjectName") = "Avalanche" workRow1("ProjectDescription") = "Super secret user control project." dtProjects.Rows.Add(workRow1) Return dtProjects End Function </script> </HEAD> <BODY> <h1>ADO.NET In 24 Hours Examples</h1> <hr> <form runat="server" id=form1 name=form1> <asp:Label id="lblMessage" runat="server"></asp:Label> <p> <strong>Employees:</strong><br> <asp:DataGrid id="employees" runat="server"></asp:DataGrid> <p> <strong>Projects:</strong><br> <asp:DataGrid id="projects" runat="server"></asp:DataGrid> <p> <asp:Label id="lblProjects" runat="server"></asp:Label> </form> <hr> </BODY> </HTML>
Let's examine this code in depth. Notice that there are two methods , GenerateCompanyDataTable() and GenerateProjectDataTable() , that create and configure the DataTable s for our example. Hour 2 covers the details of all the code in these two methods. Each method returns a DataTable back to the Page_Load event, where the new and interesting code exists.
The Page_Load method outputs a list of employees and their projects in the following sequence:
A new DataSet is created, and two new DataTable s are created from the two methods that generate tables and then added to the DataSet .
A new DataRelation object is added to the Relations collection of the DataSet object. The DataRelation is named "EmployeeProjects" and is set up with the EmployeeID column of the Employees table as the parent and the EmployeeID column of the Projects column as the child.
The two DataTable s are bound to DataGrid Web controls so that we can see the values they contain.
Two for-next loops are used to display all child rows for each parent row. Specifically, this will display each employee along with each assigned project. The StringBuilder object is used to build a string to place into the label Web control to display the information in a Web form.
If you run the code in Listing 4.6, you should see a screen much like the one in Figure 4.8.