Refreshing a Row After Submitting an Update | Microsoft ADO.NET (Core Reference) (PRO-Developer)

Submitting Hierarchical Changes

When you modify data in multiple levels of a hierarchical DataSet, you will face two challenges when you submit those changes to your database. Let's take a look at these scenarios.

Submitting Pending Insertions and Deletions

Say you're dealing with a hierarchy that contains customers and orders. The application you've built is an order entry system. The user has made a number of changes to the data and now wants to submit those changes to your database. The modified data in the DataSet contains new customers and new orders. The DataSet also contains customers and orders that are marked for deletion.

The challenge is to submit these changes in the proper order to comply with the referential integrity constraints in your database. The Northwind database contains referential integrity constraints that require all orders to refer back to customers in the database.

If the DataSet contains new customers and new orders for those customers, we must submit those new customers before submitting the new orders for them. As a general rule, you should submit new rows in a top-down approach.

The opposite is true for deleted rows, however. You can't delete customers in the Northwind database that have orders. You must delete the customers' orders first.


	This example is a simplification of a general problem. The Northwind database will not let you delete an order that has corresponding rows in the Order Details table.

The following code will not work because it will attempt to delete customers that still have pending orders.

Visual Basic .NET

CustomersAdapter.Update(MyDataSet.Tables("Customers")) OrdersAdapter.Update(MyDataSet.Tables("Orders"))

Visual C# .NET

CustomersAdapter.Update(MyDataSet.Tables["Customers"]); OrdersAdapter.Update(MyDataSet.Tables["Orders"]);

But if we reverse the order of the updates, the first update attempt will fail because the DataAdapter for the Orders table will attempt to submit new orders for customers that do not yet exist in the database.

Visual Basic .NET

OrdersAdapter.Update(MyDataSet.Tables("Orders")) CustomersAdapter.Update(MyDataSet.Tables("Customers"))

Visual C# .NET

OrdersAdapter.Update(MyDataSet.Tables["Orders"]); CustomersAdapter.Update(MyDataSet.Tables["Customers"]);

What's a poor programmer to do? We need a way to control the order of updates in a hierarchical DataSet to submit the changes in the following order:

Submit new customers.
Submit new orders.
Submit modified customers.
Submit modified orders.
Submit deleted orders.
Submit deleted customers.

Using the DataTable Object's Select Method to Submit Hierarchical Changes

In Chapter 7, I discussed the Select method of the DataTable object as a way to locate DataRow objects that satisfy the desired criteria. For example, the following line of code returns an array of the pending new DataRow objects whose City column contains Seattle. The DataRow objects are sorted based on the value of the ContactName column.

tbl.Select("City = 'Seattle'", "ContactName", DataViewRowState.Added)

The Select method returns an array of DataRow objects. And one of the overloaded DataAdapter Update methods accepts an array of DataRow objects. What a pleasant coincidence.

The following code snippet uses the Select method to isolate just the desired changes and submit them to the database in the desired order:

Visual Basic .NET

Dim ds As DataSet = CreateDataSet() Dim tblCustomers As DataTable = ds.Tables("Customers") Dim tblOrders As DataTable = ds.Tables("Orders") Dim daCustomers As OleDbDataAdapter = CreateCustomersAdapter() Dim daOrders As OleDbDataAdapter = CreateOrdersAdapter() FillDataSetAndModifyItsContents(ds) 'Submit the new customers and then the new orders. daCustomers.Update(tblCustomers.Select("", "", DataViewRowState.Added)) daOrders.Update(tblOrders.Select("", "", DataViewRowState.Added)) 'Submit the modified customers and then the modified orders. daCustomers.Update(tblCustomers.Select("", "", _                                        DataViewRowState.ModifiedCurrent)) daOrders.Update(tblOrders.Select("", "", DataViewRowState.ModifiedCurrent)) 'Submit the deleted orders and then the deleted customers. daOrders.Update(tblOrders.Select("", "", DataViewRowState.Deleted)) daCustomers.Update(tblCustomers.Select("", "", DataViewRowState.Deleted))

Visual C# .NET

DataSet ds = CreateDataSet(); DataTable tblCustomers = ds.Tables["Customers"]; DataTable tblOrders = ds.Tables["Orders"]; OleDbDataAdapter daCustomers = CreateCustomersAdapter(); OleDbDataAdapter daOrders = CreateOrdersAdapter(); FillDataSetAndModifyItsContents(ds); //Submit the new customers and then the new orders. daCustomers.Update(tblCustomers.Select("", "", DataViewRowState.Added)); daOrders.Update(tblOrders.Select("", "", DataViewRowState.Added)); //Submit the modified customers and then the modified orders. daCustomers.Update(tblCustomers.Select("", "",                                         DataViewRowState.ModifiedCurrent)); daOrders.Update(tblOrders.Select("", "",                                   DataViewRowState.ModifiedCurrent)); //Submit the deleted orders and then the deleted customers. daOrders.Update(tblOrders.Select("", "", DataViewRowState.Deleted)); daCustomers.Update(tblCustomers.Select("", "", DataViewRowState.Deleted));

Using the GetChanges Method to Submit Hierarchical Changes

You can also use the GetChanges method of the DataSet or DataTable to control the order of updates. The following code snippet creates a new DataTable that contains just the pending new rows in the initial DataTable:

tblNewCustomers = tblCustomers.GetChanges(DataRowState.Added) daCustomers.Update(tblNewCustomers) tblNewOrders = tblOrders.GetChanges(DataRowState.Added) daOrders.Update(tblNewOrders)

I find this code easier to read and write than the approach that uses the Select method. However, I don't recommend using this approach.

When you use the GetChanges method of the DataSet or DataTable object, you're creating a new and separate object. The previous code snippet submits new rows to the Customers and Orders tables in the database. In the Northwind database, the Orders table has an autoincrement column: OrderID. If the DataAdapter that submits the changes to the Orders table includes logic to fetch the newly generated values for the OrderID column, the values will be inserted into the DataTable used in the Update method—tblNewOrders. However, this DataTable object is separate from the main tblOrders DataTable, so those new OrderID values will not appear in the main DataTable.

This scenario will make more sense when we discuss isolating and reintegrating changes later in the chapter.

If you use the Select method to submit modified rows, the changes returned by the DataAdapter will be applied to your main DataTable because the Select method returns an array of DataRow objects. The DataRow objects in the array are actually pointers to the DataRow objects in the DataTable. Changes you make to the contents of the array will be visible in the main DataTable.

Working with Autoincrement Values and Relational Data

Let's shift the focus of our hierarchical DataSet slightly. We'll use a DataSet that contains data from the Northwind Orders and Order Details tables. The application that uses the DataSet will still be an order entry application. In this example, the user will enter two new orders for a customer as well as details for each order.

In Chapter 6, I recommended that you set the AutoIncrementSeed and AutoIncrementStep properties of the DataColumn object to -1 when you work with autoincrement columns. If you follow that recommendation and add new orders and details to your hierarchy, your DataSet will look something like the depiction in Figure 11-4 before you submit the new orders to your database.

In order to successfully submit the new orders and the line items for each new order, we need to submit the new orders, retrieve the new autoincrement values for the new orders, apply those values to the appropriate line items, and then submit the new line items to the database. This process sounds complicated, but it's actually fairly simple.

Figure 11-4

A DataSet with pending new orders and details

You already know how to submit the new orders. If you need to submit only new orders, you use the Select method of the DataTable that contains order information, as we discussed earlier in the chapter. You can also fetch your new autoincrement values using any of the options we discussed earlier in the chapter.

But how do you apply the new autoincrement values to the pending new line items? Actually, you don't. You let ADO.NET do the work for you through the DataRelation. By default, the DataRelation object will cascade changes through your DataSet. If you've set up a DataRelation between the orders and order details DataTable objects in your DataSet, as soon as you've submitted the new orders, the DataRelation will cascade the new value to the order details DataTable, as shown in Figure 11-5.

Figure 11-5

Cascading the autoincrement values into the child table in your hierarchy

Once the new rows in the order details DataTable contain the appropriate values for the OrderID column, you can successfully submit the pending new rows to your database. Thanks to the functionality in the DataRelation object, cascading the new autoincrement values throughout your hierarchy is the simplest part of the process.