Related Articles
This article presents an overview of the motivation behind new techniques that decompose problems into independent pieces for optimal use of parallel programming.
By David Callahan (October 2008)
We take a look at planned support for parallel programming for both managed and native code in the next version of Visual Studio.
By Stephen Toub and Hazim Shafi (October 2008)
Here we describe some of the more common challenges to concurrent programming and present advice for coping with them in your software.
By Joe Duffy (October 2008)
Here is an ASP.NET AJAX data-driven Web application that takes the best features from server- and client-side programming to deliver an efficient, user-friendly experience.
By Bertrand Le Roy (October 2008)
More ...
Articles by this Author
Here John Papa demonstrates how to build a Silverlight 2 user interface that communicates through WCF to interact with business entities and a database.
By John Papa (September 2008)
Here we build a solution that fits the Entity Framework into an n-tier architecture that uses WCF and WPF and the MVP pattern.
By John Papa (July 2008)
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.
By John Papa (May 2008)
LINQ to Objects and LINQ to Entities have an arsenal of Standard Query Operators that operate on sequences to perform a wide variety of operations.
By John Papa (March 2008)
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.
By John Papa (February 2008)
This month John Papa takes a look at developing a mobile application that can access data on your application server.
By John Papa (January 2008)
WPF is one of the most important new technologies in the .NET Framework 3.0. This month John Papa introduces its data binding capabilities.
By John Papa (December 2007)
In T-SQL, views and derived tables serve similar purposes and have their advantages. But when working with SQL Server 2005,there's a third option--using Common Table Expressions (CTEs). John Papa explains.
By John Papa (October 2007)
More ...
Popular Articles
Systems that handle failure without losing data are elusive. Learn how to achieve systems that are both scalable and robust.
By Udi Dahan (July 2008)
We introduce you to the benefits of building composite applications with the Composite Application Guidance for WPF from Microsoft patterns & practices.
By Glenn Block (September 2008)
In this article, author John Torjo presents a guide to his C++ GUI library called eGUI++ and explains how it makes user interface programming easier.
By John Torjo (June 2008)
If you're unfamiliar with Windows Presentation Foundation (WPF), building that first Silverlight custom control can be a daunting experience. This article walks through the process.
By Jeff Prosise (August 2008)
More ...
Read the Blog
Well designed code keeps things that have to change together as close together in the code as possible and allows unrelated things in the code to change independently, while minimizing duplication in the code. In the October 2008 issue of MSDN Magazine, Jeremy Miller shows you some design ... Read more!
The process for ink capture and analysis on the Tablet PC is straightforward in managed code. To the uninitiated developer, however, creating unmanaged Tablet PC applications can be rather daunting. In the October 2008 issue of MSDN Magazine, Gus Class a quick introduction to the Tablet PC ... Read more!
Multicore systems are becoming increasingly prevalent, but the majority of software today will not automatically take advantage of this additional processing ability. And multithreaded programming, for anything but the most trivial of systems, is incredibly difficult and error prone today. In the October 2008 issue of MSDN ... Read more!
Concurrent programming is notoriously difficult, even for experts. You have all of the correctness and security challenges of sequential programs plus all of the difficulties of parallelism and concurrent access to shared resources. In the October 2008 issue of MSDN Magazine, David Callahan describes ... Read more!
A major advantage of AJAX and Silverlight applications is that they can transparently and continuously interact with a back-end service. The problem is that they run over HTTP, which wasn't designed with security in mind. In the September 2008 issue of MSDN Magazine, Dino Esposito shows you ... Read more!
Unhandled exception processing shouldn't be a mystery. It's actually quite useful since it gives a crashing application an opportunity to perform last-minute diagnostic logging about what went wrong. In the September 2008 issue of MSDN Magazine, Gaurav Khanna discusses how ... Read more!
|
From the January 2002 issue of MSDN Magazine .gif) .gif) | | Using the ADO.NET DataSet for Multitiered Apps | | Johnny Papa | Download the code for this article: Data0201.exe (116KB)
Browse the code for this article at Code Center: ADO.NET DataSet App
| he vast majority of applications built today in-volve data manipulation in some way—whether it be retrieval, storage, change, translation, verification, or transportation. For an application to be scalable and allow other apps to interact with it, the app will need a common mechanism to pass the data around. Ideally, the vehicle that transports the data should contain the base data, any related data and metadata, and should be able to track changes to the data. Here's where the ADO.NET DataSet steps in.
The ADO.NET DataSet, represented in Figure 1, is a data construct that can contain several relational rowsets, the relations that link those rowsets, and the metadata for each rowset. The DataSet also tracks which fields have changed, their new values and their original values, and can store custom information in its Extended Properties collection. The DataSet can be exported to XML or created from an XML document, thus enabling increased interoperability between applications.
.gif)
Figure 1 ADO.NET DataSet
One of the key points to remember about the DataSet is that it doesn't care where it originated. Unlike the ADO 2.x Recordset, the DataSet doesn't track which database or XML document its data came from. In this way, the DataSet is a standalone data store that can be passed from tier to tier in an n-tiered architecture.
In the previous installment of the Data Points column (November 2001), I began a series dedicated to demonstrating how to effectively write applications using the DataSet and other features of ADO.NET. This month, I'll continue by showing you how to use the DataSet in a .NET-based application to retrieve data, track modifications made by the user, and send the data back to the database. Using Visual Basic® .NET, I'll show how to retrieve products from the Northwind database from a business services application and load the information in the presentation tier, all using .NET. I'll walk through examples of how to use the DataSet to save several rows to the database at one time. The code will demonstrate how to send new rows, changed rows, and deleted rows in one trip to the business services tier and then on to the database by using a DataSet.
The example I'll present has three entities.
- Presentation Tier: The source code for a Windows® Form that interacts both with the user and the business services
- Business Tier: The source code for our own business services
- Data Tier: The Northwind database that comes with Microsoft® Access or SQL Server™
In the November 2001 column, I demonstrated the source code in both Visual Basic .NET and C#. I'll continue in that vein this month to demonstrate that the language you choose for your app has become a less important issue in .NET.
The application I developed in this article can be broken into two distinct projects: a Windows-based application project to present the data to the user and a Class Library project to handle the business rules and issue the data manipulation calls. I'll start off by examining the Windows Form which allows the user to interact with the application. Then, I'll show how the business services (written in C# or Visual Basic .NET) can be assigned the tasks of retrieving the data and making database modifications.
The Windows Form Let's start by looking at a Windows Form that contains the presentation of my application. The code frmMain.vb contains a DataGrid control that will be filled with customer data from the Northwind database (see Figure 2). There you'll see a DataGrid to display the data to the user and a series of toolbar buttons to load the data and save it to and from the database.
.gif)
Figure 2 The DataGrid Windows Form
The DataGrid is a server-side control that is easily manipulated to render a rowset by binding itself to a DataSet. OK, so data binding used to be a bad term. But in the .NET world, things are quite different. When a DataSet is bound to a DataGrid, the data from the DataSet is copied into the DataGrid. The changes made to the DataGrid's data are then cascaded to the DataSet so it reflects the changes.
Here is where data binding differs from its previous incarnations. The Visual Basic 6.0 and ADO 2.x data binding techniques involve binding data in a grid directly to a data control and, in turn, to the database. This holds the connection open to the database and limits the scalability of an app as the presentation layer is now bound to the data layer. With .NET, the grid is still bound to the DataSet, but the DataSet has no connection to the database. In fact, the DataSet doesn't even know or care where its data came from. The DataSet can be filled with data from a variety of sources or filled manually with no data source.
When the user changes data in the Windows Form's DataGrid, the modifications are sent to the DataSet automatically. Any new rows are added to the DataSet and any modified rows are updated in the DataSet. Rows deleted from the DataGrid are removed from the DataSet by flagging the row as deleted. The DataSet tracks all changes to the data by storing the original and current values of each row and column. For example, assume a DataGrid contains 100 rows of data and the user changes a field in 3 rows, adds a new row, and deletes 2 others. The affected rows can be extracted from the bound DataSet into another DataSet so that the new DataSet only contains the 3 changed rows, 2 deleted rows, and the 1 new row. The code for the Windows Form shown in Figure 2 calls the business services to retrieve the data contained within a DataSet and then binds the data to the DataGrid.
The code in Figure 3 shows the class definition for the Windows Form and the declaration of the class's properties. In the frmMain class, I have defined a variable called m_oDS to represent the DataSet that will store my data. I declare the DataSet property as private so no other class, form, or code can interact with the DataSet.
So how does the data get into the DataGrid? When the user clicks on the Load button, the LoadData method is invoked. This method declares and creates an instance of the business services' Customer class. Then, the DataGrid's data binding setting is cleared so that I can start with a clean slate in the DataGrid. The business services' Customer object is then called upon to retrieve the DataSet into the frmMain class's property m_oDS. Now that the DataSet is local, I bind the DataSet to the DataGrid and dispose of my instance of the Customer object (see Figure 4).
You may notice that the code in Figure 4 refers to the Customer table in the DataSet. The DataSet can actually contain many tables of data all at once. Each of these DataSet tables can be related to one another as well. In this DataSet I only have one table (the Customer table), but I still ask for it by name.
The first thing I've seen most developers do when they delve into ADO.NET is to start looking for the Recordset equivalent. They quickly turn to the DataSet as the logical successor of the Recordset, as it has many similar features. One of the biggest differences, however, is that the DataSet can contain multiple related rowsets of data. The Recordset can only contain a single rowset of data. By storing multiple tables of data, the DataSet can represent a relational data model (such as a relational database structure), or a hierarchical data model (such as an XML data structure).
Once the data is loaded into the DataGrid, the user can interact with it and then save the changes to the database by clicking on the Save button. Figure 5 shows the code that executes when the Save button is clicked. First, the SaveData method declares and creates an instance of the business services' Customer class. Next, the DataSet is checked to see if any changes were made. If no changes were made, I exit the method and avoid the trip to the business services. The HasChanges method of the DataSet returns true if any changes were made to the DataSet. Since the DataSet is bound to the DataGrid if any changes were made to the DataGrid, they are also made to the DataSet. This makes the HasChanges method a solid indicator of whether any changes were made to the data.
Assuming the user has made changes to the data in the DataGrid, the HasChanges method will return a true value and the SaveData method will continue to execute. Next, I set the oDS_Delta DataSet variable to contain the changed rows in the main DataSet. The GetChanges method of the DataSet copies the changed rows from the DataSet and puts them into a new DataSet, oDS_Delta. Here, the GetChanges method retrieves all changed rows and their values. This method also accepts a parameter, which can be any of the DataRowState enumerators. If the DataRowState.Added enumerator value is passed to the GetChanges method, only the newly added rows will be in the DataSet returned by the method.
Since this code is going to send all data changes to the business services in one shot, I'll omit the parameter and only transport the changed rows. This also cuts down on the network traffic. For example, if the user makes changes to six rows of data in a DataSet with 100 rows, I'm only sending a DataSet with six rows to the business services. But if I skip this step and send the entire DataSet, all 100 rows will go to the business services. That's a lot of overhead to send across a network, especially since I am only interested in the changes that have been made.
The user is then prompted to make sure they want to save the changes to the database. If the user chooses to continue, the business services Customer object's SaveData method is invoked, passing in the changed data. If the data is saved successfully, the DataGrid is reloaded. If an error occurs, the try-catch-finally construct will pass control to the catch clause and a custom error message will be displayed to the user.
The Business Services Thus far I've demonstrated how the presentation services can use a DataSet to store data retrieved from a database, bind to a DataGrid, get updated by changes made directly to a DataGrid, filter out its own changes, and then pass the new data to the business services on its way to the database. In this example the DataSet serves as a storage container for the data and its changes as well as being a vehicle for passing data between different architectural tiers. What happens once control is passed to the business services?
In this example, the business services are defined as a .NET class library written in C#. I've put the code for this class library into a single class file called BusinessServices.cs (which can be found at the link at the top of this article) and have declared the namespace BusinessServices. This namespace will reference the System namespace, the System.Data namespace to gain access to the DataSet, and the System.Data.SqlClient namespace to interact with the Northwind database. I use the SQL Server-specific namespace since I am only hitting a SQL Server database (see Figure 6). If I wanted to use non-SQL Server databases, I would have used the OleDbClient namespace.
Next, the Customer class and its private properties are defined (see Figure 7). The private properties represent the connection string arguments, and the various DataSet, DataAdapter, and Connection objects that this class uses.
When the presentation services' Windows Form creates an instance of the Customer class, the Customer class's constructor executes. In Figure 8, notice that the constructor for the Customer class declares four SqlCommand objects. Each of these commands will represent one of the four main types of SQL data manipulation: SELECT, INSERT, UPDATE, or DELETE.
In ADO 2.x, if an application needed to perform data retrieval, inserts, updates, and deletes all in one method, four different ADODB.Command objects would be required. In ADO.NET there are still four commands, but they're all linked to a SqlDataAdapter object. The SqlDataAdapter uses each of the four SqlCommand objects, depending on the action it needs to take. For example, if the app requests that the method fill a DataSet, the SqlDataAdapter would execute the SqlCommand referenced by the SqlDataAdapter's SelectCommand property. If the app passes a DataSet to a method in the business services, expecting the changes contained within it to be applied to a database, the SqlDataAdapter executes its UpdateCommand, InsertCommand, and DeleteCommand properties based on the type of change that was made to each row in the DataSet. The SqlDataAdapter is the bridge between the database and the DataSet. It executes its command properties against a database and keeps the DataSet isolated from the data source.
To set up the SqlDataAdapter, each SqlCommand has to be initialized and set to the corresponding property of the SqlDataAdapter. The SelectCommand simply defines the SELECT statement that should be used to retrieve the data from the database and store in a DataSet at a later time. The other three commands establish the action queries for the SqlDataAdapter to use. Any of these four SqlCommand objects can have parameters that can be linked directly to a source column in a DataSet. That way, when you pass a DataSet to a SqlDataAdapter to have its changed rows applied to the database, the SqlCommand knows how to get the parameter values for its INSERT, UPDATE, or DELETE statement.
Once the SqlDataAdapter is set up in the constructor, the GetData and SaveData methods can use the SqlDataAdapter to get or save the data. The code in Figure 9 shows the definition of the GetData method that returns a DataSet. First, it declares the DataSet and creates an instance of it. Then, the Fill method of the SqlDataAdapter is called, returning the customer data and metadata to the m_oDS DataSet. Finally, the DataSet object is returned to the presentation services.
Figure 10 shows the SaveData method of the Customer class, which accepts a DataSet as its parameter. The DataSet contains all of the changed rows that were passed in from the presentation services' Windows Form. Only the changed rows are passed to this method; these changes are then applied to the database by the SqlDataAdapter to reflect the updates.
The SaveData method uses the try-catch-finally construct to apply the data changes to the database. First, it sets up and opens the connection to the database. Then the SqlDataAdapter object's Update method is executed, using the DataSet as its parameter. The table name is also passed into the Update method. This tells the SqlDataAdapter that only this table (which is a DataSet table) should be applied to the database. The Update method then returns the number of rows that have been affected by the InsertCommand, UpdateCommand, and DeleteCommand properties of the SqlDataAdapter.
Each row of the DataSet is traversed in the order that it was sent to the SaveData method. Assuming six rows were changed, the corresponding SqlCommand would be issued in the order that the rows appear in the DataSet.
Row 1: Updated the row
Row 2: New row
Row 3: Deleted the row
Row 4: Updated the row
Row 5: New row
Row 6: Updated the row
Wrapping Up This example shows how to create a business service (the Class Library) and a presentation service (the Windows-based application with the Windows Form) using both the Visual Basic .NET and C#.
Next time, I'll build on this application so the user can save several rows of changes for customers, orders, and order information all in one shot using ADO.NET.
All of the code for this column is at the link at the top of this article.
Send questions and comments for Johnny to mmdata@microsoft.com. | Johnny Papa is VP of Information Technology at MJM Investigations in Raleigh, NC. He is the author of Professional ADO 2.5 RDS Programming with ASP 3.0 and contributing author of Professional XML Databases (both Wrox, 2000). Reach him at john@lancelotWeb.com.
| |
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.
|
|