From the February 2002 issue of MSDN Magazine

  • Article

MSDN Magazine

Establishing Relationships Between Rowsets with ADO.NET
Johnny Papa
Download the code for this article:Data0202.exe (103KB)
X ML has taken the development community by storm. But until the introduction of ADO.NET, the basic tenets of XML have not been fully implemented by a data access toolset. Because ADO.NET has XML at its core, it can handle full-feature XML data structures as well as relational data structures.
      This wasn't always the case. ADO 2.x required the use of either the hierarchical recordset structure (which was very slow and not sufficiently intuitive) or the standard Recordset object with a matrix of columns and rows, in order to manage hierarchical data in an object. The Recordset object in ADO 2.x manages a two-dimensional matrix very well, but it's not great for representing hierarchical data.
      In the previous two installments of the Data Points column I began a series on writing applications using the DataSet and other features of ADO.NET (see the November 2001and January 2002 issues of MSDN® Magazine). In the January column, I demonstrated how you can use a DataSet to retrieve data, track modifications made by the user, and send the data back to the database. This month I will build on this application so that the user can save several rows of changes for customers, orders, and order details all in one shot. Using ADO.NET, this type of relational yet hierarchical structure can be retrieved from and applied to a database in one fell swoop.
      Using Visual Basic® .NET, I'll show you how to use a business services application that's written in C# to retrieve customers, orders, and order details from the Northwind database and plug them into a DataSet. The DataSet will contain three DataTables, which I'll link using DataRelation objects to create a hierarchical data structure. At that point, I'll load the information into the presentation tier. Finally, I'll present examples that use the DataSet to save several rows of data to the database all at once.
      The application built in this article can be broken into two distinct projects: a Windows®-based app to present the data to the user and a class library project to handle the business rules and data manipulation calls. I'll start by examining the Windows Form (written in Visual Basic .NET), which allows the user to interact with the application. Then, I'll show how the business services (written in C#) can retrieve the hierarchical data and make database modifications. In the business services, I'll explore class inheritance as well as the overriding and overloading of methods.

DataRelations

      The key to managing data in ADO.NET is to understand the convergence of hierarchical and relational data structures. An ADO.NET DataTable stores a two-dimensional rowset, which is similar to the way you used the Recordset in ADO 2.x. Currently, if you take two DataTables within the same DataSet object and create a relationship between them using a DataRelation object, the result is a hierarchical structure. A relational database such as Northwind is now represented with its true relationships. In the past, you would have had to combine the customers and their orders into a single rowset, thus producing a flattened data structure by forcing the data into a single two-dimensional matrix. Now, using a DataSet and its child objects, you can maintain the relational and hierarchical structure. Figure 1 shows how the DataSet object can contain multiple DataTable and DataRelation objects.

Figure 1 ADO.NET DataSet
Figure 1ADO.NET DataSet

The DataGrid

      Let's look at a Windows Form that controls the presentation of this application. The form frmMain.vb contains a DataGrid control that will be filled with the customers, their orders, and each order's details. The DataGrid is a server control that is easily manipulated to render a rowset by binding itself to a DataSet. Because the DataSet will have a hierarchical structure, the DataGrid will automatically adjust to that structure by displaying all customers first. All changes made to the DataGrid's data are reflected onto the underlying DataSet. The DataGrid shows the customer rows with a + sign to the left of each row (see Figure 2). This allows the user to drill down into each customer's child records (in this case, the customer's orders).

Figure 2 The Customers
Figure 2The Customers

      The DataGrid allows the user to navigate up and down the parent and child relationships from a customer to their order and onto the order details. The grid allows for changes to be made at each level of the hierarchy. So whether a row is deleted, added, or simply modified, the DataGrid manages the changes. All changes are then reflected in the underlying DataSet. To navigate to the child records, simply click on the link for the DataRelation, called CustomerToOrder (see Figure 2).

Figure 3 The Customer's Orders
Figure 3The Customer's Orders

      After clicking on the CustomerToOrder DataRelation hyperlink in the DataGrid, the customer's orders will be displayed (see Figure 3). The grid can be a useful tool to represent and manage multiple levels of hierarchical data and their relationships.
 
Public Class frmMain Inherits System.Windows.Forms.Form Private m_oDS As
                                        DataSet
      To load the data into the DataGrid, I've declared and instantiated the business services' CustomerOrder class. Then the DataGrid's data binding settings are cleared and the business services' CustomerOrder object is called to retrieve the DataSet into the frmMain class property m_oDS. Now that the DataSet is local, I bind it to the DataGrid, loading three levels of related rowsets into the DataGrid (see Figure 4).
      The user can interact with the data and then save the changes to the database by clicking on the Save button. Figure 5 shows the code in which the SaveData method declares and instantiates the business services' CustomerOrder class. If no changes were made to the DataSet, the method is exited. I check the HasChanges method of the DataSet to see if any changes were made to the data.
      If there are changes to the data in the DataGrid, I set the oDS_Delta DataSet variable to contain the changed rows in the original DataSet. The GetChanges method of the DataSet copies the changed rows from the DataSet and puts them into a new DataSet called oDS_Delta. This reduces the network traffic since I pass only the modified rows to the business services.

The Business Services

      Up to this point the code is the same whether the DataGrid is loaded with a single DataTable or the DataSet contains multiple related DataTables. Where things really begin to change is in the business services. The code in the following examples shows how to define and create instances of the Customer, Order, and OrderDetail classes. These classes will be used to retrieve and save data to and from the database. Since all three classes need to retrieve and save data in the same manner, I have created a base class, Base_BusinessServices, from which all three classes can inherit properties and methods.

Base Classes and Inheritance

      The Base_BusinessServices class has two public methods: GetData and SaveData. It also contains a protected method called InitializeConnection, which defines the connection string. I made this method protected so that it can be accessed by any class that inherits from or implements the Base_BusinessServices class. The GetData method, shown in Figure 6, works the same way whether the data retrieved is customer, order, or order detail data. Therefore, I can use this method in the base class and the Customer, Order, and OrderDetail classes can inherit it.
      I also created a fifth class called CustomerOrder, which creates an instance of each of the Customer, Order, and OrderDetail classes. The CustomerOrder class also inherits from the Base_BusinessServices class since it needs the GetData and SaveData methods. It is not necessary for this class to inherit from the base class, but it provides a means to discuss overriding an inherited method. You may have noticed in Figure 6 that the GetData method was declared with the virtual keyword. This tells any class that inherits from this base class that it may override the method. In this application, I wanted to have the GetData method execute code other than what was in the base class, so I chose to override this method.
      The Base_BusinessServices class also contains a SaveData method, as shown in Figure 7. Since I used the virtual keyword on this method, any class that's derived from this base class will be able to override this method. Notice that the SaveData method uses the try-catch-finally construct to apply the data changes to the database. It opens the connection to the database and executes the SqlDataAdapter object's Update method.
      Each row of the DataSet is traversed in the same order that it was sent to the SaveData method. Assuming six rows were changed (as shown in Figure 7), the corresponding SqlCommand would be issued in the order that the rows appear in the DataSet.

The Inheriting Classes

      The Base_BusinessServices class contains the basic functionality that the Customer, Order, and OrderDetail classes require. Therefore, by having each of these classes inherit from the Base_BusinessServices class, the GetData and SaveData methods do not need to be redefined. Figure 8 shows the Customer class. (The Order and OrderDetail classes are very similar to the Customer class, differing only in their SQL statements.)
      To inherit the Base_BusinessServices class, the Customer class's definition is followed by a colon and the name of the Base_BusinessServices class, as shown in Figure 8. The constructor for the Customer class is used to set up the SqlDataAdapter object. The SqlDataAdapter takes four SqlCommand objects, each of which represents one of the four main types of data manipulation and retrieval in SQL: SELECT, INSERT, UPDATE, and DELETE. First, I created the SqlCommand to represent the query that retrieves the customer data via a SELECT statement. Then I created the SqlCommand to represent the query that updates the customer data via an UPDATE statement, and so on. Finally, I created a SqlDataAdapter object and assigned the corresponding SqlCommand object to each of its command properties.

The CustomerOrder Class

      The CustomerOrder class puts all of the pieces together in the business services. It combines the customer, order, and order detail data and relates them within a single DataSet. The constructor for CustomerOrder defines and creates the instances of each of these classes, as shown in Figure 9.
      The GetData method overrides the base class Base_BusinessServices' GetData method using the override keyword (see Figure 10). Here is where the data from the three classes is combined into a single DataSet. The GetData method of each of the Customer, Order and OrderDetail classes is called and a DataTable is retrieved from each. Then each DataTable is added to a new DataSet. Stopping at this point in the process would yield a DataSet comprised of all three rowsets, but the data would not yet be linked in a hierarchical structure.
      To relate the data, my next step was to create a DataRelation object for each relationship. First, I created the relationship that links the individual customers to their orders. The DataSet contains a Relations collection, which has an Add method. I used this method to create the new DataRelation object and add it to the DataSet. I called the relationship CustomerToOrder and passed to it the columns from the DataSet that defines the relationship. By establishing this relationship, you can traverse the DataSet from a customer to his orders.
      To complete the relationships, I added another DataRelation to link each order with its order details. With both DataRelation objects linking the three DataTables within the DataSet, I can walk the tree from the parent to its child records. At this point I'm ready to return the DataSet to the presentation services (the Windows-based app, in this case).
      Once the user saves the data modifications in the Windows-based app, the delta DataSet is passed to the business services' CustomerOrder class via the SaveData method (shown in Figure 11). The CustomerOrder class's SaveData method overrides the SaveData method from the inherited base class and uses the try-catch-finally construct to save the data to the database.
      First, the connection to the database is defined and opened. Next, a transaction is begun on the connection. Creating the transaction allows the code to use a two-phase commit on the data changes. The first phase prepares the data changes, and the second phase writes the data changes if there are no problems and the data doesn't have to be rolled back.
      Next, each of the Customer, Order, and OrderDetail classes's SaveData methods are executed. If any one of them fails, it will return an error message and an exception will be thrown in the try-catch-finally construct. Then the exception transfers the next execution to the catch clause where the transaction is rolled back. Unlike previous versions of Visual Basic, Visual Basic .NET and C# error handling techniques do not rely on errors bubbling up the call stack. Instead, it is sometimes recommended that your methods return a status that can be evaluated to determine if an error occurred. In the SaveData methods, a return value of a string message means that an error occurred while saving the data. An empty string indicates that the method encountered no difficulties. Assuming that each of the Customer, Order, and OrderDetail class's SaveData methods executed without incident, the transaction is committed and control is passed back to the calling Windows-based app.

Wrapping Up

      This example shows how to create a business service (the Class Library written in C#) and a presentation service (the Windows-based app with the Windows Form written in Visual Basic .NET) using different .NET languages. To execute the application, compile the two projects and make sure that the Windows-based app references the business services assembly. Being able to relate different rowsets so that they resemble a relational database with one-to-many relationships makes the use of XML for data manipulation even more powerful. Using ADO.NET you don't need to combine rowsets through SQL joins or manipulate an XML document directly. Or course, the option of joining rowsets into a single rowset is entirely feasible in ADO.NET. In fact, to retain backward compatibility, you could return a single rowset of customer, orders, and order details to an application.
      In this installment of the Data Points column I showed you how to take advantage of the DataSet by relating rowsets to one another and creating a single interface to modify all related rowsets. I also demonstrated how to use inheritance to derive methods and properties from a base class when there are commonalities that can be reused. All of the code used in this article can be downloaded from the link at the top of this article.

Send questions and comments to Johnny at mmdata@microsoft.com.
Johnny Papa is VP of Information Technology at MJM Investigations in Raleigh, NC and the author of Professional ADO 2.5 RDS Programming with ASP 3.0 (Wrox, 2000) and contributing author of Professional XML Databases (Wrox, 2000). You can reach him at john@lancelotweb.com.