Resync Command Dynamic Property | Programming MicrosoftВ® ADO.NET 2.0 Core Reference

[Previous] [Next]

In ADO 2.1, the Cursor Engine introduced a new dynamic property called Resync Command to help make updating Recordsets based on joins simpler. In order to use this property, you must also set the Unique Table dynamic property on the Recordset. In the case of the query we've been using, the unique table is the Orders table. To update a join, set the Unique Table property to the unique table in your join. Then set the Resync Command property to the query you want to use to resynchronize the data in your join-based Recordset. Use parameter markers (?) in the WHERE clause to denote the key columns in the unique table.

A couple of examples will help to clarify how useful this feature can be. Let's revisit our customers and orders query and look at how you can use Resync Command to simplify modifying or inserting records in Recordsets based on joins.

 SELECT Customers.CustomerID AS [Customers.CustomerID], Customers.CompanyName, Customers.ContactName, Orders.* FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID

NOTE
In this code, I aliased the CustomerID field from the Customers table, which makes the query string more complex but simplifies the ADO code. See the "Questions That Should Be Asked More Frequently" section at the end of this chapter for a brief explanation.

So, the query string we would use for the Resync Command property is

 SELECT Customers.CustomerID AS [Customers.CustomerID], Customers.CompanyName, Customers.ContactName, Orders.* FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID AND Orders.OrderID = ?

This is simply the original query with the key column for the unique table in a WHERE clause.

We're going to cover two scenarios with our example. The first scenario involves changing the customer referenced by the order and getting the appropriate customer data into our Recordset. The second scenario entails adding a new order and getting the appropriate customer data into our Recordset.

Changing the Customer Referenced by an Order

The problem with changing the customer referenced by an order is that we want to modify only the data in the Orders table. To do that, we can simply modify the value of the CustomerID field (that refers to the Orders table) in our Recordset. Submitting that change to the database will have the desired effect—in our database. The fields in the Recordset object that correspond to the Customers table will not automatically reflect this change.

This is where programmers start to run into problems. They want to see the appropriate customer data in the join. If you requery the Recordset, you will see the appropriate customer data, but that involves reexecuting the entire query, and most programmers don't want to incur that expense.

Calling Resync won't help in this case (yet) because the ADO Cursor Engine will resynchronize each half of the join separately and will resynchronize the fields corresponding to the Customers table based on the old ID field it has for that record. So, calling Resync at this point would simply refetch the old customer information.

The other avenue that programmers pursue is programmatically changing the data in the customer side of the join. ADO interprets this as a change to the actual customer data, and will build an action query to locate that customer and modify that record of the Customers table to reflect the changes you make to the Recordset. Generally, this is not what you want to do.

Here's where the Resync Command property comes in handy. We can use the following code to retrieve the results of a join on the Customers and Orders tables and modify the customer that the order references. Then we can call the Resync method to resynchronize the record in the Recordset based on the query string in the Resync Command property. Once we do that, the appropriate customer data appears in our Recordset.

 'Create the initial query. strSQL = "SELECT Customers.CustomerID AS [Customers.CustomerID], " & _ "Customers.CompanyName, Customers.ContactName, Orders.* " & _ "FROM Customers, Orders " & _ "WHERE Customers.CustomerID = Orders.CustomerID" Set rsCustomersAndOrders = New ADODB.Recordset rsCustomersAndOrders.CursorLocation = adUseClient rsCustomersAndOrders.Open strSQL, cnDatabase, adOpenStatic, _ adLockOptimistic, adCmdText 'Specify values for the Unique Table and Resync Command properties. rsCustomersAndOrders.Properties("Unique Table") = "Orders" strSQL = "SELECT Customers.CustomerID AS [Customers.CustomerID], " & _ "Customers.CompanyName, Customers.ContactName, Orders.* " & _ "FROM Customers, Orders " & _ "WHERE Customers.CustomerID = Orders.CustomerID " & _ "AND Orders.OrderID = ?" rsCustomersAndOrders.Properties("Resync Command") = strSQL 'Update the CustomerID field that corresponds to the Orders table. rsCustomersAndOrders!CustomerID = "ANTON" rsCustomersAndOrders.Update 'Resynchronize the record to retrieve the proper data from the ' Customers table. rsCustomersAndOrders.Resync adResyncCurrent

Generating a New Order and Displaying the Correct Customer Data

Similar to updating records in a Recordset that is based on a join, adding new records to a Recordset based on a join can pose a problem. In this scenario, we want to create a new order, supply a value for the CustomerID field, and ultimately see the appropriate customer data in our Recordset.

NOTE
Using the Northwind database for SQL Server or Access adds a complexity. The OrderID field is an auto-incrementing field generated by the database system. We'll talk more about working with this type of field in Chapter 11. For now, we're going to supply that OrderID value programmatically as if the Orders table did not use an auto-incrementing field.

If we simply supply the order information and leave the fields that correspond to the Customers table blank, we successfully create the new order but we don't get the customer data we're looking for. Calling Requery might not be desirable, since it reexecutes the entire query. Without setting the Resync Command property, the Resync method will actually generate an error since the field that corresponds to the CustomerID field and references the Customers table is still empty.

Once again, the Resync Command property comes to our rescue. We can successfully retrieve the desired customer data by setting the Unique Table and Resync Command properties as follows:

 'Create the initial query. strSQL = "SELECT Customers.CustomerID AS [Customers.CustomerID], " & _ "Customers.CompanyName, Customers.ContactName, Orders.* " & _ "FROM Customers, Orders " & _ "WHERE Customers.CustomerID = Orders.CustomerID" Set rsCustomersAndOrders = New ADODB.Recordset rsCustomersAndOrders.CursorLocation = adUseClient rsCustomersAndOrders.Open strSQL, cnDatabase, adOpenStatic, _ adLockOptimistic, adCmdText 'Specify values for the Unique Table and Resync Command properties. rsCustomersAndOrders.Properties("Unique Table") = "Orders" strSQL = "SELECT Customers.CustomerID AS [Customers.CustomerID], " & _ "Customers.CompanyName, Customers.ContactName, Orders.* " & _ "FROM Customers, Orders " & _ "WHERE Customers.CustomerID = Orders.CustomerID " & _ "AND Orders.OrderID = ?" rsCustomersAndOrders.Properties("Resync Command") = strSQL 'Add a new row to the Recordset and populate the fields ' that correspond to the Orders table. rsCustomersAndOrders.AddNew rsCustomersAndOrders!OrderID = 12345 rsCustomersAndOrders!EmployeeID = 1 rsCustomersAndOrders!OrderDate = Date rsCustomersAndOrders!ShipVia = 1 rsCustomersAndOrders!CustomerID = "ANTON" rsCustomersAndOrders.Update 'Resynchronize the record to retrieve the proper data from the ' Customers table. rsCustomersAndOrders.Resync adResyncCurrent

Compound Keys

In the previous examples, the unique table contains a primary key based on a single field. What if your table uses a compound primary key and you want to use the Resync Command property?

Let's say that the Orders table has a compound primary key that consists of two fields: ID1 and ID2. In that case, our Resync Command property looks like this:

 SELECT Customers.CustomerID AS [Customers.CustomerID], Customers.CompanyName, Customers.ContactName, Orders.* FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID AND Orders.ID1 = ? AND Orders.ID2 = ?

Notice that there's no difference in the parameter markers. You might need to play with the order of the key fields in your initial query string and your Resync Command query. My advice is to make sure the key fields appear in the same order in the Recordset as in the WHERE clause of the query in the Resync Command property.

I'm extremely impressed with how the ADO development team implemented the Resync Command and Unique Table properties to make updating Recordsets based on joins simpler. However, these properties are best used in live (nondisconnected) Recordsets that use simple optimistic locking. These properties aren't very helpful for disconnected Recordsets or for Recordsets that utilize the ADO Cursor Engine's batch updating features.

Comparatively speaking, the ADO Cursor Engine does a great job of handling joins. If you use the same join query against a SQL Server database and use a server-side cursor, you'll receive an error if you try to update any field that corresponds to a field in the Customers table. SQL Server marks those fields as read-only. The Resync Command property can make updating Recordsets based on join queries possible in some cases, but I prefer opening such Recordsets as read-only.