| An ADO.NET DataSet object is much like an in-memory database. This object can contain DataTable objects, where each of these is a representation of the data that was retrieved via a SELECT statement. (The source of data in the DataTable might include data from multiple tables, but that's not a concern once that data's stored in the DataTable object.) Datasets can also store schema information as well as constraints and relationships between multiple DataTable objects. You may need to represent and work with relationships between DataTable objects in your DataSets. For example, you might want to examine both employees and their orders as well as be able to view orders for any employee on a page without having to take a roundtrip back to the database server in order to retrieve the information for each different employee. Suppose you'd like to create a DataSet a client-side data cache (this time, the page itself is a client to the database server) that contains information on all the employees and all the orders for all employees, related by the EmployeeID field. You'd like to be able to select an employee and see that employee's orders in a grid and then select another employee and see that employee's orders all without requesting more data from the SQL Server! You can accomplish your goal using ADO.NET, but it does require some features you haven't yet investigated. In this section, you'll create a new page and add code that supports these relational features. Along the way, we'll explain how the code works so that you can expand and reuse it in your own applications. Figure 14.2 shows the finished page, which allows you to select an employee and see the orders for that employee. Figure 14.2. The finished Employees page allows you to view orders by employee.
To be able to navigate to the new page from the finished application, you need to add a new hyperlink on Main.aspx to call this new page. To do so, follow these steps: -
Load Main.aspx in the page designer window. -
Drag a new hyperlink control to the Web page just underneath the Products hyperlink. -
Set the properties for the control as shown in Table 14.2. Table 14.2. Use These Property Settings for the New Hyperlink | Property | Value | | ID | hypEmployees | | NavigateURL | Employees.aspx | | Text | Employees | -
Select Project, Add Existing Item. Be sure to change the Files of Type combo box to "All Files (*.*)." -
Add the Employees.* files from the Jumpstart\WorkingWithData folder. There should be three files: Employees.aspx, Employees.aspx.resx, and Employees.aspx.vb. Investigating the Relation-Handling Code In order to modularize the code required to support the relational DataSet, we've separated out two useful, reusable procedures: AddRelation and AddPrimaryKey. Adding Relations The AddRelation procedure allows you to take a DataSet that contains two tables and add a relationship between the tables. You supply a number of values as parameters, including the following: The DataSet. You pass in an open DataSet, filled with the tables you want to relate. The name of the new DataRelation object. By supplying a name, you can refer to the DataRelation object by name, later on. We didn't have the code supply a name in the sample code, so you could only refer to the relation by its position within the collection of DataRelation objects in the DataSet object. ParentTable and ParentField names. The code uses the ParentTable and ParentField names to create the "parent" side of the relationship. ChildTable and ChildField names. The code uses these names to create the "child" side of the relationship. Once the code has run, the DataSet contains a defined relationship between the two DataTable objects. Once you have the data relation, you can use it to iterate through all the parent rows, and for each parent row, retrieve all the associated child rows that's how the Employees page will display orders for a selected employee. Locate the AddRelation code shown in Listing 14.3 to investigate how it works. Listing 14.3 The AddRelation Method Takes Care of Relating Two DataTable Objects Private Sub AddRelation( _ ByVal ds As DataSet, _ ByVal Name As String, _ ByVal ParentTable As String, _ ByVal ParentField As String, _ ByVal ChildTable As String, _ ByVal ChildField As String) Dim dcParent As DataColumn Dim dcChild As DataColumn Dim drn As DataRelation dcParent = ds.Tables(ParentTable).Columns(ParentField) dcChild = ds.Tables(ChildTable).Columns(ChildField) drn = New DataRelation(Name, dcParent, dcChild) ds.Relations.Add(drn) End Sub To create a DataRelation object, ADO.NET requires a name and two DataColumn objects one from each of the DataTables to be related. This code first retrieves the two columns, given the table and column names: dcParent = ds.Tables(ParentTable).Columns(ParentField) dcChild = ds.Tables(ChildTable).Columns(ChildField) Next, the code creates a new DataRelation object, based on the name you supplied, and the two DataColumn objects it just retrieved: drn = New DataRelation(Name, dcParent, dcChild) Finally, the procedure adds the DataRelation object to the DataSet's collection of DataRelation objects: ds.Relations.Add(drn) Adding Primary Keys You'll also need to be able to add primary key information to the DataTable objects within your DataSet many operations require them. A primary key is a set of one or more columns whose values are required to be both unique, and nonempty. By supplying a primary key value for a DataTable object, you have a quick and easy way to locate any specific row, because each row has some unique, existing value that identifies it. The code in Listing 14.4 shows the AddPrimaryKey procedure that makes it easy to add primary keys to your DataTable objects. Listing 14.4 Add Primary Keys to DataTable Objects Private Sub AddPrimaryKey( _ ByVal ds As DataSet, _ ByVal TableName As String, _ ByVal FieldName As String) ' The PrimaryKey property of a DataTable ' object contains an array of DataColumn ' objects. ' Create an array that can contain a single item. ' The index here (0) indicates that the array ' goes from 0 to 0 (containing a single item). Dim dcs(0) As DataColumn ' Given a table within the dataset, ' retrieve a DataColumn object, stuff ' it into the array, and assign that array ' to the PrimaryKey property. Seems roundabout ' but that's how it works! With ds.Tables(TableName) dcs(0) = .Columns(FieldName) .PrimaryKey = dcs End With End Sub The AddPrimaryKey procedure handles the simple case where you provide a DataSet, a table name, and a field name. The procedure adds a primary key to the specified table within the DataSet, adding only the single specified field. (You could modify the procedure to accept multiple field names and add the grouping of all the fields as the primary key, but we'll leave that exercise for your spare time.) The PrimaryKey property of a DataTable object expects to receive an array of DataColumn objects, and even though this procedure will never have more than a single DataColumn to add, it still must work with an array. The procedure starts by setting up an array of DataColumn objects, allowing only a single value (the array is indexed from 0 to 0, allowing just that element in column 0): Dim dcs(0) As DataColumn Next, the code adds the specified DataColumn object to the array: With ds.Tables(TableName) dcs(0) = .Columns(FieldName) ... End With Finally, the code sets the PrimaryKey property of the DataTable object to be the array it just created: With ds.Tables(TableName) ... .PrimaryKey = dcs End With Building the Relational DataSet Now that you've got the two important and complex procedures out of the way, look at the code that builds the relational DataSet. Follow these steps to investigate the code: -
Find the Page_Load procedure, which calls the LoadEmps and LoadOrders procedures: Private Sub Page_Load( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load If Not Page.IsPostBack Then LoadEmps() LoadOrders(CInt(ddlEmps.SelectedItem.Value)) End If End Sub -
Find the LoadEmps procedure. This procedure calls GetRelationalDataSet (which you'll add soon) and then binds the DropDownList control to the Employees table within the DataSet: Private Sub LoadEmps() Dim ds As DataSet ds = GetRelationalDataSet() With ddlEmps .DataTextField = "EmpName" .DataValueField = "EmployeeID" .DataSource = ds.Tables("Employees") .DataBind() End With End Sub -
Now take a look at the GetRelationalDataSet procedure, shown in Listing 14.5. This procedure fills a DataSet with two DataTables (named EmployeeInfo and OrderInfo), sets up a relation between the two tables, and adds primary keys to both DataTable objects. Finally, the procedure stores the DataSet in a Session variable for later in the use of this page. Listing 14.5 Retrieve a Relational DataSet Object Private Function GetRelationalDataSet() As DataSet ' Build a DataSet containing two related tables: ' Employees->Orders, related on EmployeeID. ' This requires calling the Fill method of the ' DataAdapter twice, once for each table. ' This method both returns the filled DataSet, ' and stuffs the DataSet into a Session variable ' for use on postback. Dim ds As DataSet Dim da As OleDbDataAdapter Dim strSQLEmp As String Dim strSQLOrders As String Dim strConn As String ' Build Employee and Orders SQL strSQLEmp = "SELECT EmployeeID, " & _ "LastName + ', ' + FirstName As EmpName " & _ "FROM Employees" strSQLOrders = "SELECT EmployeeID, " & _ "ShipName, ShipCountry, OrderDate " & _ "FROM Orders" strConn = Session("ConnectString").ToString() da = New OleDbDataAdapter(strSQLEmp, strConn) Try ' Add the Employees to the DataSet ds = New DataSet() da.Fill(ds, "EmployeeInfo") ' Change the SQL text to the Orders table ' Add the Orders to the DataSet. You could ' also simply reinstantiate the DataAdapter, ' but this is faster, we assume. da.SelectCommand.CommandText = strSQLOrders da.Fill(ds, "OrderInfo") ' Build a relation, and add it to the DataSet. AddRelation(ds, "EmpOrders", _ "EmployeeInfo", "EmployeeID", _ "OrderInfo", "EmployeeID") ' Set the Customers table primary key field AddPrimaryKey(ds, "EmployeeInfo", "EmployeeID") ' Set the Orders table primary key field AddPrimaryKey(ds, "OrderInfo", "OrderID") ' Store DataSet in Session Variable Session("DS") = ds Return ds Catch Throw End Try End Function The GetRelationalDataSet does a lot of work. It takes these actions as it sets up the two DataTable objects: -
It sets up the two SQL strings it needs in order to retrieve its two DataTable objects: strSQLEmp = "SELECT EmployeeID, " & _ "LastName + ', ' + FirstName As EmpName " & _ "FROM Employees" strSQLOrders = "SELECT EmployeeID, " & _ "ShipName, ShipCountry, OrderDate " & _ "FROM Orders" -
It retrieves the application's connection string and creates a new DataAdapter: strConn = Session("ConnectString").ToString() da = New OleDbDataAdapter(strSQLEmp, strConn) -
It creates the new DataSet and fills the first DataTable (named EmployeeInfo) with information from the Employees table: ' Add the Employees to the DataSet ds = New DataSet() da.Fill(ds, "EmployeeInfo") -
It changes the DataAdapter's CommandText property and then fills the second DataTable (named OrderInfo) with information from the Orders table: da.SelectCommand.CommandText = strSQLOrders da.Fill(ds, "OrderInfo") -
It adds the relationship between the two tables: AddRelation(ds, "EmpOrders", _ "EmployeeInfo", "EmployeeID", _ "OrderInfo", "EmployeeID") -
It adds the two primary keys: AddPrimaryKey(ds, "EmployeeInfo", "EmployeeID") AddPrimaryKey(ds, "OrderInfo", "OrderID") -
It stores the DataSet in a Session variable for later use and returns the DataSet as its return value: Session("DS") = ds Return ds
TIP We haven't talked much about using Session variables to maintain the status of data between pages or postbacks to the same page you'll find this information in Chapter 23, "State Management in ASP.NET." For now, all you need to understand is that we're using a global "bag" in which to store the DataSet so that it's available the next time we need it without having to re-create it.
Filtering the Grid Based on the Relation When you select an employee from the DropDownList control, you need to be able to filter the grid to show only orders taken by that employee. Follow these steps to accomplish that goal: -
Find the ddlEmps_SelectedIndexChanged procedure in the code editor: Private Sub ddlEmps_SelectedIndexChanged( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles ddlEmps.SelectedIndexChanged LoadOrders(CInt(ddlEmps.SelectedItem.Value)) End Sub -
Note that the DropDownList control calls the LoadOrders procedure when you change the selected index: Private Sub LoadOrders(ByVal EmpID As Integer) Dim ds As DataSet Dim dt As DataTable Dim drwEmp As DataRow Dim drwOrder As DataRow Dim drw As DataRow ' Rehydrate the DataSet ds = CType(Session("DS"), DataSet) ' Clone the structure of the old table dt = ds.Tables("OrderInfo").Clone ' Find the row selected drwEmp = ds.Tables("EmployeeInfo").Rows.Find(EmpID) ' Copy all rows from the child relation ' Into a new data table For Each drwOrder In drwEmp.GetChildRows("EmpOrders") dt.ImportRow(drwOrder) Next grdOrders.DataSource = dt grdOrders.DataBind() End Sub The LoadOrders procedure uses a few techniques you've not previously seen. The procedure does its work by executing these actions: -
Besides declaring DataSet and DataTable variables, the LoadOrders procedure declares three DataRow variables to keep track of the various rows of data it will manage: Dim ds As DataSet Dim dt As DataTable Dim drwEmp As DataRow Dim drwOrder As DataRow Dim drw As DataRow -
It retrieves the DataSet object from the Session variable. In an earlier procedure, you stored the saved DataSet object in a Session variable, and now it's time to retrieve it so that the code can display the appropriate rows from the OrderInfo DataTable in the grid. The following code "rehydrates" a DataSet from the value stored in the Session variable. Note that the code must use the CType function to convert from Object (which is what the Session variable returns) to a DataSet type: ' Rehydrate the DataSet ds = CType(Session("DS"), DataSet) -
It clones the structure of the OrderInfo table. Because the code will need to add rows to a new table but will need to use the same schema as the OrderInfo DataTable, the procedure uses the Clone method to retrieve a clone of the schema. You can't simply copy the entire DataTable it contains all the rows, and all you want is a subset that contains just the rows corresponding to the selected employee: ' Clone the structure of the old table dt = ds.Tables("OrderInfo").Clone -
It finds the employee you selected. The code uses the Find method of the Rows property of a DataTable to find a particular row, given the primary key value for the DataTable. The Find method returns a DataRow object: ' Find the row selected drwEmp = ds.Tables("EmployeeInfo").Rows.Find(EmpID) -
It copies the orders for the employee to the new, cloned table. In order to bind the grid, you need to copy the orders for the selected employee to the new DataTable. To make this simple, ADO.NET provides the GetChildRows method of a DataRow object. This method only works if you've set up a relation between DataTables, as you did here. The GetChildRows method returns a collection of DataRow objects, and you can call the ImportRow method of the new DataTable to import each DataRow object you find: For Each drwOrder In drwEmp.GetChildRows("EmpOrders") dt.ImportRow(drwOrder) Next -
It binds the grid to the new DataTable. Because you've just laboriously filled a new DataTable with all the orders for the selected employee, it's simple to bind the grid to the new DataTable and display the selected rows: grdOrders.DataSource = dt grdOrders.DataBind()
At this point, run the sample project and verify the behavior of this page. Although there's a ton of code involved (that's why we didn't have you type it yourself), the sample page does show off some interesting techniques involving relational data. This seems like a lot of code, and it does introduce a large number of new concepts. One thing's for certain: The .NET Framework is incredibly rich, and it provides solutions to most standard programming tasks. We weren't sure, when we started this exercise, exactly how we would accomplish our goals. We approached the challenge by trying one thing after another, digging deep into the methods provided by various objects, until we hit upon what seemed an optimal solution to the problem. It was a fun learning experience. The point here is that you'll learn a lot by picking a particular goal and then spending a few hours trying to accomplish that goal, looking for the best means to get the results you need using methods provided by the .NET Framework. |
