5
|
|
5 |
|
|
The
LA,
Fox Developer Newsletter
|
|
May 2000
|
|
ADO
J urn psta rt
(Con't from page 4)
Cancel local changes
Marshall local changes to the server
Save (persist) the recordset locally.
You can save (persist) the recordset locally for both later use and, ultimately, for marshalling those persisted changes back to the server.
The first choice is pretty simple to implement, since it takes one line of code:
oRecordset.Cancel Batch
The second choice is also simple to implement. Much of the work in updating multiple records and transactions has already been detailed. This procedure really involves
two
separate steps:
1.
.Re-establish an active connection.
2.Marshal modified records back to the data source.
The following code re-establishes the connection:
With oconnection
.Provider
=
“SQLOLEDB.1”
.ConnectionString
=
“Persist Security lnfoFalse; User ID=salnitlal CatalogNorthwind;Data Source=JVP”
.Open
End With
=
oConnection
Then the code marshals the records by attempting the updates
Local Err,cOldErr cOldErr
=
On(”error” On Error
Err
=
.T.
With oRecordset .ActiveConnection.BeginTrans .UpdateBatch
If Err .ActiveConnection.RollBackTrans
.CancelBatch
Else
.ActiveConnection.Com mitTra ns
Endif
End With
On Error &cOldErr
Often, however, there’s a need to shut things down and then reopen the recordset at another time. To be effective, the recordset must reflect incremental changes. This cycle may repeat any number of times.
To illustrate how to persist a recordset, consider again the following code that modifies records in a Recordset object:
|
oRecordset.MoveFirst
Do
While
!oRecordset.Eof
?oRecordset.Fields(”companyname”).Value
oRecordset.Fields(”companyname”).Value
=
Upper(oRecordset.Flelds(”com pany name”).Value) oRecordset.MoveNext
EndDo
Now you can invoke the Save method to
persist
the recordset:
oRecordset.Save(”c:~tem p~customers.rs”)
At a later time, you can open the persisted recordset:
oRecordset
=
createobject(”adodb.recordset”) oRecordset.Open(”c:~temp~customers.rs”)
After the persisted recordset is reopened, you can use the same code, which establishes a connection to a disconnected recordset, to make additional modifications. You can marshal changes made in the persisted recordset to the underlying data source.
Hierarchical/Shaped
Recordsets
Visual FoxPro not only provides the ability to work with local data, but also the ability to set up relations using the Set Relation command. When you move the record pointer in the parent table, the record pointer automatically moves in any child tables that exist. This makes working with and building interfaces for one to many relationships very simple in Visual FoxPro. Fortunately, the same capability exists in ADO, in the form of hierarchical recordsets, also referred to as shaped recordsets.
There are
two
necessary components when creating and working with hierarchical recordsets:
The Microsoft DataShape OLE DB Provider, MSDataShape The Shape language, a superset of the SQL syntax
The first requirement is fairly easy to fulfill because it only entails setting the Provider property of the ADO Connection object to the proper value:
oconnection.Provider
=
“MSDataShape”
The second requirement, using the Data Shape language, is a bit more challenging. When you first see Data Shape language, it can be fairly intimidating, just as FoxPro may have been when you first worked with it. But like anything else, with a bit of practice and patience, Microsoft Data Shape language will become second nature.
To examine Shape language, consider a parent-child common scenario of customers and orders. For each customer, zero or more orders can exist. In turn, each order
can
contain one or more line items. The following code employs Shape syntax to relate customers and orders in the SQL Server Northwind database:
|
|
Page 5
|
|
|
5 |
|