Related Articles
Cobra, a descendant of Python, offers a combined dynamic and statically-typed programming model, built-in unit test facilities, scripting capabilities, and much more. Feel the power here. Ted Neward MSDN Magazine June 2009 ... Read more!
Developers frequently face issues related to scalability bottlenecks when they have applications that need to handle a lot of load. This article describes the role of a distributed cache in building scalable applications. Iqbal Khan MSDN Magazine July 2009 ... Read more!
Memory usage can have a direct impact on how fast an application executes and thus is important to optimize. In this article we discuss the basics of memory optimization for .NET programs. Subramanian Ramaswamy and Vance Morrison MSDN Magazine June 2009 ... Read more!
This column shows you how to secure the .NET Services Bus and also provides some helper classes and utilities to automate many of the details. Juval Lowy MSDN Magazine July 2009 ... Read more!
We demonstrate creating a peer-to-peer processing platform where multiple players function together for a common purpose: getting your work done. Matt Neely MSDN Magazine June 2009 ... Read more!
Also by this Author
John Papa tackles questions about calling services from Silverlight 2 applications. John Papa MSDN Magazine November 2008 ... Read more!
The Entity Framework is a hot topic, but how do you know when to use EntityClient, Object Services, Entity SQL, or LINQ? Find out here. John Papa MSDN Magazine May 2008 ... Read more!
This month John Papa takes a look at developing a mobile application that can access data on your application server. John Papa MSDN Magazine January 2008 ... Read more!
Silverlight 2 applications are restricted to running inside a browser. However, Silverlight 3 applications can run inside the browser or out. Here we build a social networking app as a standalone Silverlight 3 application. John Papa MSDN Magazine June 2009 ... Read more!
With the Entity Framework, developers are given more flexibility by being able to design around a conceptual model rather than a relational data model. To get started, you need to know how to design an Entity Data Model. John Papa walks you through that process. John Papa MSDN Magazine February 2008 ... Read more!
Popular Articles
Here we present techniques for programmatic and declarative data binding and display with Windows Presentation Foundation. Josh Smith MSDN Magazine July 2008 ... Read more!
This article introduces 10 development tools that can increase your productivity, give you a better understanding of .NET, and maybe even change the way that you develop applications. The tools covered include NUnit to write unit tests, Reflector to examine assemblies, FxCop to police your code, Regulator to build regular expressions, NDoc to create code documentation and five more. James Avery MSDN Magazine July 2004 ... Read more!
Now you can perform efficient, sophisticated text analysis using regular expressions in SQL Server 2005. David Banister MSDN Magazine February 2007 ... Read more!
Jeff Prosise explains when it's better to use UpdatePanel and when it's better to use asynchronous calls to WebMethods or page methods instead. Jeff Prosise MSDN Magazine June 2007 ... Read more!
Chris Tavares explains how the ASP.NET MVC Framework's Model View Controller pattern helps you build flexible, easily tested Web applications. Chris Tavares MSDN Magazine March 2008 ... Read more!
|
From the February 2002 issue of MSDN Magazine .gif) .gif) | | 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 2001 and 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.
.gif)
Figure 1 ADO.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).
.gif)
Figure 2 The 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).
.gif)
Figure 3 The 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.
| | |
|