From the January 2002 issue of MSDN Magazine

MSDN Magazine

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.

Figure 1 ADO.NET DataSet
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.

Figure 2 The DataGrid Windows Form
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

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