Retrieving Newly Generated Autoincrement Values

Isolating and Reintegrating Changes

Say you're building a multi-tiered application with a Windows client user interface that accesses your database via a Web service. The Web service returns DataSet objects that contain the requested information to clients. The client application allows the user to modify the contents of the DataSet. After modifying the data, the user can click a button and the client will submit the changes to the database via the Web service. The simplest way to submit these changes is to send the DataSet back to the Web service and have the Web service use DataAdapter objects to submit the changes.

To get the best possible performance out of your application, you'll want to make the best possible use of your bandwidth. The less data you pass back and forth between the client application and the Web service, the faster your application will run.

Limiting the amount of data that the Web service returns is fairly simple and intuitive. Don't design your Web service to return the entire contents of your tables if they might contain thousands or millions or rows; if you do, the performance of your application will suffer. Also make sure that the Web service returns only the data that the client application needs.

What about limiting the amount of data that the client application passes back to the Web service? You can pass a DataSet back to the Web service to have the Web service submit changes to your database. But if that's the goal of calling to the Web service, you probably don't want to pass the entireDataSet. If the DataSet contains a few hundred rows and the user has modified only a handful of those rows, passing the entire DataSet back to the Web service will be extremely inefficient. How can we improve upon this process?

Saving Bandwidth Using the GetChanges Method

The DataSet and DataTable objects each expose an overloaded GetChanges method. When you call a DataSet object's GetChanges method, you receive a new DataSet object that has the same structure as the original DataSet but contains only the modified rows from the original DataSet.

If you're calling your Web service to submit changes to your database, you might gain a significant improvement in performance by first calling the GetChanges method on your DataSet and sending the results to your Web service.

	When I said that the DataSet object's GetChanges method returns a new DataSet object that contains only the modified rows, I bent the truth a tiny bit. The new DataSet that the GetChanges method returns has the same structure as the original DataSet and contains the modified rows that you requested, but it might also contain other rows needed to maintain referential integrity. For example, say you have a DataSet that contains DataTable objects for customer and order information with a DataRelation defined between the two DataTable objects. If you add some new orders and then call the DataSet object's GetChanges method, the DataSet that the method returns will contain those new rows as well as the corresponding rows from the customers DataTable. Otherwise, the new DataSet would violate the constraint associated with the DataRelation.

Earlier in the chapter, I talked about enforcing optimistic concurrency by using timestamp columns in your updates. This process gets a little more complicated in a multi-tiered application. Let's say you're using timestamp concurrency checks in the DataAdapter object's updating logic in your Web service. You're also retrieving the new timestamp values using one of the options we discussed earlier in the chapter. But what happened to those new timestamp values?

You retrieved the new values and stored them in the DataSet in the Web service. But that DataSet is separate from the one in your client application. How can you get the new timestamp values into the DataSet in your client application?

You could simply have the Web service return a new DataSet that contains all the same data as the client application. But even though this approach will ensure that the client application has more up-to-date data, it might not be the best use of your bandwidth.

A more economical solution, shown in Figure 11-6, is to have the Web service return the DataSet it received, with the new timestamp values included.

Figure 11-6

Returning new server-generated values via a Web service

But that solves only part of the problem. The client application now has the new timestamp values, but how can you integrate the DataSet that the Web service returns into the client application's DataSet?

The Merge Method of the DataSet Object

The simple answer is to use the Merge method of the DataSet object. The DataSet object's Merge method lets you merge the contents of a DataSet, a DataTable, or an array of DataRow objects into an existing DataSet. Figure 11-7 shows a basic example of this functionality.

Figure 11-7

Basic example of the results of the DataSet object's Merge method

Each DataSet initially contains a single DataTable with the same name. After you call the Merge method on the main DataSet and supply the second DataSet, the main DataSet will contain all its original columns plus those from the second DataSet. The main DataSet will also contain the rows from the second DataSet.

This example is not terribly useful, however. Few developers will combine two DataSet objects that contain DataTable objects with the same name but that have completely different structures. Figure 11-8 shows a more typical example. The two DataSet objects contain DataTable objects with similar structures. In each DataTable, the ID column is the primary key.

Figure 11-8

A more typical example of the results of the DataSet object's Merge method

After the call to the Merge method, the main DataSet will contain an additional column from the second DataSet. The contents of the main DataSet will also have changed as a result of the call to the Merge method. In the previous example, the Merge method simply appended existing rows to the main DataSet. In this example, the Merge method combines the contents of the two DataSet objects.

The difference in this example is the primary key. If ADO.NET encounters rows that have the same primary key values while it is merging data, it combines the contents into a single row. In this example, both DataSet objects have rows that have primary key values of 1 and 2. Each DataSet also has an additional row that has no counterpart in the other DataSet.

Notice that in the results of the Merge method, the data from the DataSet that's being merged in takes precedence. The values for Column A in the second DataSet replace the corresponding values from the main DataSet when ADO.NET combines rows. In the diagram, these are rows whose ID value is 1 or 2.

Now that you're armed with a better understanding of how the Merge method works, let's recap the scenario.

Our client application retrieves customer information from a Web service. The user modifies the contents of that DataSet. The client application uses the GetChanges method to create a new DataSet that contains only the modified rows and sends this smaller DataSet back to the Web service.

The Web service submits the changes to the database using timestamp values in the updating logic to enforce optimistic concurrency. The Web service also retrieves the new timestamp values for the modified rows (using one of the techniques we discussed earlier in the chapter) and stores that information in its DataSet. After the Web service completes this operation, it returns the DataSet with those new timestamp values included. The client application receives this DataSet and merges it into the main DataSet, as shown in the following code snippet, to integrate the new timestamp values in the main DataSet, as shown in Figure 11-9.

Figure 11-9

Merging newly fetched data into an existing DataSet

Visual Basic .NET

Dim objWebService As New WebServiceClass() Dim dsMain As DataSet = objWebService.GetDataSet() ModifyDataSetContents(dsMain) Dim dsChanges As DataSet = dsMain.GetChanges() dsChanges = objWebService.SubmitChanges(dsChanges) dsMain.Merge(dsChanges)

Visual C# .NET

WebServiceClass objWebService = new WebServiceClass(); DataSet dsMain = objWebService.GetDataSet(); ModifyDataSetContents(dsMain); DataSet dsChanges = dsMain.GetChanges(); dsChanges = objWebService.SubmitChanges(dsChanges); dsMain.Merge(dsChanges);

The Merge Method and the RowState Property

We're almost done, but not quite. If you check the contents of the rows that we originally modified in the main DataSet, you'll see that they do have the new timestamp values. However, those rows still have a RowState of Modified.

If the user clicks a button in the application to submit changes to the database, the DataSet that the GetChanges method returns will still contain the rows that the user previously modified. When the Web service receives this DataSet and tries to submit the changes, the update attempt will generate an exception because the database already contains those changes.

We know that we've submitted those changes to the database, but ADO.NET doesn't understand this. When the Web service submits the changes, ADO.NET changes the RowState of the modified rows from Modified to Unmodified. But that change occurs in the Web service's DataSet. ADO.NET does not change the RowState of the modified rows in the client application's main DataSet because there is no link between these two DataSet objects.

Merging the DataSet that the Web service returns will not change the RowState property of the modified rows in the main DataSet. That's definitely what we want to have happen, but ADO.NET will not do this for us automatically. However, because we know that we successfully submitted the changes that currently reside in the main DataSet, we can change the RowState of the modified rows back to Unmodified by calling the AcceptChanges method on the main DataSet after we call Merge, as shown in the following code:

Visual Basic .NET

Dim objWebService As New WebServiceClass() Dim dsMain As DataSet = objWebService.GetDataSet() ModifyDataSetContents(dsMain) Dim dsChanges As DataSet = dsMain.GetChanges() dsChanges = objWebService.SubmitChanges(dsChanges) dsMain.Merge(dsChanges) dsMain.AcceptChanges()

Visual C# .NET

WebServiceClass objWebService = new WebServiceClass(); DataSet dsMain = objWebService.GetDataSet(); ModifyDataSetContents(dsMain); DataSet dsChanges = dsMain.GetChanges(); dsChanges = objWebService.SubmitChanges(dsChanges); dsMain.Merge(dsChanges); dsMain.AcceptChanges();

The Merge Method and Autoincrement Values

Let's change the example slightly. Instead of working with customer information, let's work with order information. In this example, the database table that contains the order information will use an autoincrement column as its primary key, like the Orders table in the Northwind database does.

As in the previous example, the client application will communicate with the database through a Web service. Let's say the user retrieves two orders for an existing customer, adds two new orders for the customer, and then submits those new orders to the database. You already know how to use the GetChanges method to pass just the modified rows to the Web service, as shown in Figure 11-10.

Figure 11-10

Using the GetChanges method to submit just the pending new orders to a Web service

You also know how to retrieve new autoincrement values into the DataSet that the Web service uses to submit new orders to the database. These values are included in the DataSet that the Web service returns after you submit the modified orders to the database. However, if we merge this DataSet into our main DataSet, we won't get the desired behavior.

Instead, we'll get the results shown in Figure 11-11. The main DataSet will contain the original pending orders with the "dummy" values for the OrderID column as well as the orders returned by the Web service with the actual values for the OrderID column. What's going wrong?

Figure 11-11

The results of merging the DataSet returned by the Web service into the main DataSet

This Merge method relies on the DataTable object's primary key in order to match up the rows from the different DataSet objects. The rows that we want the Merge method to combine do not have the same values for their primary key columns. The Merge method does not realize that the orders in the DataSet that the Web service returns match existing pending orders in the main DataSet. As a result, the Merge method simply adds the rows from the Web service's DataSet to the main DataSet.

Obviously, this is not the behavior we want, but a couple of solutions are available to us.

Purge before Merge

Look again at the results of the call to the Merge method in Figure 11-11. Our goal was to combine the contents of the two DataSet objects and pull the new OrderID values into the existing DataSet. The results really aren't that far off. We have the new OrderID values, but we also have copies of those new orders with the "dummy" OrderID values.

We can achieve the desired results by removing the new orders from the main DataSet just before we merge in the DataSet returned by the Web service. The following code snippet uses the Select method of the DataTable object to loop through the rows whose RowState property is Added and removes those rows from the DataSet before merging in the DataSet that the Web service returns.

Visual Basic .NET

Dim objWebService As New WebServiceClass() Dim dsMain As DataSet = objWebService.GetDataSet() ModifyDataSetContents(dsMain) Dim dsChanges As DataSet = dsMain.GetChanges() dsChanges = objWebService.SubmitChanges(dsChanges) 'Remove the pending new orders from the main DataSet 'before merging the orders returned by the Web service. Dim tbl As DataTable = dsMain.Tables("Orders") Dim row As DataRow For Each row in tbl.Select("", "", DataRowViewState.Added)     tbl.Rows.Remove(row) Next row dsMain.Merge(dsChanges) dsMain.AcceptChanges()

Visual C# .NET

WebServiceClass objWebService = new WebServiceClass(); DataSet dsMain = objWebService.GetDataSet(); ModifyDataSetContents(dsMain); DataSet dsChanges = dsMain.GetChanges(); dsChanges = objWebService.SubmitChanges(dsChanges); //Remove the pending new orders from the main DataSet //before merging the orders returned by the Web service. DataTable tbl = dsMain.Tables["Orders"]; foreach(DataRow row in tbl.Select("", "", DataRowViewState.Added))     tbl.Rows.Remove(row); dsMain.Merge(dsChanges); dsMain.AcceptChanges();

Looping through the main DataSet and removing the pending inserts isn't terribly elegant, but it definitely solves the problems.

Changing the primary keys in your DataSet objects

Another solution is available, but it's not for the weak of code. You now understand how the Merge method works and why it doesn't combine the DataSet objects from our example in the way we want. The rows that we want to combine do not have the same primary key values.

What if we change the primary key? Just before merging the two DataSet objects, we can change the primary key of each DataTable to a different column. If the corresponding rows in the DataSet have the same values in this column, we'll get the desired results when we merge the DataSet objects. After merging, we can reset the primary keys to their original values.

Let's add a new column to the original DataTable and call it PseudoKey. It's just an arbitrary column. It does not map back to an actual column in the database. It's just there to help force the results of the Merge method to suit our needs. Figure 11-12 shows an example.

Figure 11-12

Adding a pseudokey to the DataSet to merge in new autoincrement values

How can we programmatically change the primary key on each table just before merging and reset the primary key afterwards? The solution isn't terribly elegant, but it's not terribly complex, either. The following sample code accomplishes the task nicely:

Visual Basic .NET

Dim objWebService As New WebServiceClass() Dim dsMain As DataSet = objWebService.GetDataSet() ModifyDataSetContents(dsMain) Dim dsChanges As DataSet = dsMain.GetChanges() dsChanges = objWebService.SubmitChanges(dsChanges) 'Change the primary key in each table to be the pseudokey. Dim tblMain As DataTable = ds.Tables("Orders") Dim pkOriginal As DataColumn() = tblMain.PrimaryKey tblMain.PrimaryKey = New DataColumn() {tblMain.Columns("PseudoKey")} Dim tblChanges As DataTable = dsChanges.Tables("Orders") tblChanges.PrimaryKey = New DataColumn() {tblChanges.Columns("PseudoKey")} dsMain.Merge(dsChanges) 'Set the primary key in the main table back to its original value. tblMain.PrimaryKey = pkOriginal dsMain.AcceptChanges()

Visual C# .NET

WebServiceClass objWebService = new WebServiceClass(); DataSet dsMain = objWebService.GetDataSet(); ModifyDataSetContents(dsMain); DataSet dsChanges = dsMain.GetChanges(); dsChanges = objWebService.SubmitChanges(dsChanges); //Change the primary key in each table to be the pseudokey. DataTable tblMain = ds.Tables["Orders"]; DataColumn[] pkOriginal = tblMain.PrimaryKey; tblMain.PrimaryKey = new DataColumn[] {tblMain.Columns["PseudoKey"]}; DataTable tblChanges = dsChanges.Tables["Orders"]; tblChanges.PrimaryKey = new DataColumn[] {tblChanges.Columns["PseudoKey"]}; dsMain.Merge(dsChanges); //Set the primary key in the main table back to its original value. tblMain.PrimaryKey = pkOriginal; dsMain.AcceptChanges();

But what about the contents of the PseudoKey column? How can we generate unique values for this column when it does not correspond to a column in the database? We can use...another autoincrement column.

Reviewing your options

Personally, I don't like either solution I've presented. They're inelegant at best. The solution that involves changing the primary key can become overly complex, especially if the table in question has related child DataTable objects in the DataSet. If I had to pick one solution, I'd choose the one that removes the pending inserts from the original DataSet before calling Merge. However, I will say this: even having these inelegant solutions is a major step forward from ADO, in which there were no solutions to this scenario.

You'll find a sample application called ComplexHierarchy on the companion CD that demonstrates the problem and various solutions. The sample modifies the contents of a hierarchy that contains orders and order details. Like the Northwind database, the table that contains order information uses an autoincrement column as its primary key.

The sample lets you submit the changes from the DataSet to the database using DataAdapter objects and the DataTable object's Select method (as shown in Figure 11-13). To ensure that the update attempts succeed, the code submits new orders before new order details and deletes existing order details before deleting existing orders. You can submit the updates from the DataSet directly to the database, or you can isolate the changes into a new DataSet by calling GetChanges and then merging in the submitted changes afterwards. Alternatively, you can include a pseudokey in each DataTable and use this pseudokey to merge the two DataSet objects, as described earlier.

Figure 11-13

The ComplexHierarchy sample application

One final option is to avoid the problem altogether by structuring your data so that you know the primary key values for your new rows before you submit them to the database. A growing number of developers are using globally unique identifiers (GUIDs) in their databases. Although you might not want to use a GUID column as the primary key for your database table, you can use it as the primary key for your DataTable and avoid the problem altogether.