| If you have worked with older VB data access technologies, you will find that ADO has features that allow you to manipulate data in ways that were never before possible. For example, in most traditional applications a recordset in your program is always linked to the underlying database. As long as the recordset is loaded into memory, you are maintaining an active connection to the database. However, the rise of the Internet and "loosely connected" applications have brought about the concept of a disconnected recordset. This term means that you can have a recordset in memory that is not attached to a database. Disconnected recordsets can be manipulated independently of the database, saved to disk, and synchronized to the database later if necessary. This works great in heavily used Internet applications. Consider a Web server that has 100,000 users trying to display product lists at the same time. By creating a business object that returns a disconnected recordset from the database server to the Web server, you can minimize the connection time and resource drain on the database. Disconnecting a Recordset from the Database The key to creating a disconnected recordset is setting the CursorLocation property. This property determines whether a cursor in the database will be associated with the data in the recordset. To create a disconnected recordset (or "client-side cursor"), perform the following steps: -
Create the new Recordset object. -
Set the CursorLocation property to adUseClient. -
Populate the Recordset object with data. -
Set the ActiveConnection property to Nothing.
Recall the example from Listing 21.2, which displayed information in a list box. By modifying the code in the New subroutine, you can easily change it to use a disconnected recordset: Dim cn As New ADODB.Connection() rs = New ADODB.Recordset() rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient rs.Open("Select * from Person",_ "Server=localhost;UID=sa;pwd=;Database=BrianTest;Provider=SQLOLEDB") rs.ActiveConnection = Nothing DisplayCurrentRecord() The only changes from Listing 21.2 were to add two lines of code. You set the CursorLocation property so that records are stored on the client, and you disconnect the recordset by destroying its ActiveConnection object. If skeptics in the audience need even more evidence that the recordset is indeed disconnected from the database, run the sample program then stop the SQL database server. You will find that the program still functions normally. However, you cannot start the program a second time unless the database is accessible. Creating a Recordset with Code A disconnected recordset is a useful structure for passing data around in an application, especially because it contains built-in methods for filtering and sorting. As you just saw, it is easy to populate a recordset from an existing database. However, you can also create a recordset "from scratch" using just a few lines of VB code. In order to do this, you simply create a new Recordset object, create a Fields collection, and finally add records using the methods described earlier. Listing 21.5 shows how to create a Recordset object that contains fields for names and phone numbers. Listing 21.5 ADOTEST.ZIP Creating an ADO Recordset with Code Dim rs As New ADODB.Recordset() 'Create the structure rs.Fields.Append("Name", ADODB.DataTypeEnum.adVarChar, 30) rs.Fields.Append("Phone", ADODB.DataTypeEnum.adVarChar, 18,_ ADODB.FieldAttributeEnum.adFldIsNullable) 'Add a record rs.Open() rs.AddNew() rs.Fields("Name").Value = "George Kaplan" rs.Fields("Phone").Value = "901-555-1212" rs.Update() Recordsets can also be saved to disk with a single line of code: rs.Save("c:\temp.xml", ADODB.PersistFormatEnum.adPersistXML) The previous line of code saves the recordset structure in data in XML format. Disconnected recordsets can be loaded from a saved file just as easily: Dim rs As New ADODB.Recordset() rs.Open("C:\temp.xml") Notice that you use the Open method, but instead of specifying a SQL query or Command object, you supply the desired file path. Updating the Database Earlier, you learned how records are locked to prevent multiple users from fighting over the same record. Record locking takes on a different meaning when you use disconnected recordsets. If you are not connected to a database, then you are, in a sense, checking out the records, like you would check out books from a library. If you edit or change the data in the recordset, the changes are not reflected until you check in the records. For some types of recordsets, such as read-only data, you may never want or need to apply changes back to the database. However, if you do need to update the database you have two options: Let ADO perform a batch update, or manually process each record in the disconnected recordset by calling a stored procedure to update the associated database record. Updating a Batch of Records Batch updates allow you to make multiple changes to a recordset and apply them to the underlying database at once with the UpdateBatch method. You can even make a change to a disconnected recordset, reestablish the connection, and update the database. First, to use UpdateBatch, you must change the Recordset object's LockType property to use the adLockBatchOptimistic record-locking method: rs.LockType = adLockBatchOptimistic After you retrieve a disconnected recordset, as described in the preceding section, change one or more of the records: rs.Fields("zipcode").Value = "00000" rs.Update() rs.MoveNext() rs.Delete() Next, re-establish the connection to the database by setting the recordset's ActiveConnection property to an open Connection object, as shown here: rs.ActiveConnection = cn Finally, execute the code that actually performs the database update: rs.MarshalOptions = adMarshalModifiedOnly rs.UpdateBatch I hope you were watching closely because I just introduced a new property, MarshalOptions. This property determines whether the whole recordset is returned for updating or just the changed records, for the most efficient use of resources over a slow network connection. The second line of code actually performs the update with the UpdateBatch method. In the preceding sample, the UpdateBatch would change the ZipCode field for one record and delete another record. Updating Records Manually In some cases, you might want more control than the default handling of UpdateBatch can provide. In this case, you can use the Status property of a Recordset object to determine what type of update was done to a particular record: While Not rs.EOF Select Case rs.Status Case ADODB.RecordStatusEnum.adRecNew 'THIS IS A NEW RECORD, EXECUTE INSERT STATEMENT Case ADODB.RecordStatusEnum.adRecModified 'THIS IS A CHANGED RECORD, EXECUTE UPDATE STATEMENT End Select rs.MoveNext End While If you set the MarshalOptions property to return all records, you will also get back the original records, which will have a status of adRecUnModified. Using Remote Data Services (RDS) Remote Data Services (RDS) is a part of ADO, which acts as a link between Internet Information Server and your custom classes. It is often used to pass disconnected recordsets across the Internet using ADO. If you need to create a COM object remotely over the Internet, RDS provides an easy way to do it. However, keep in mind that the DLLs you create with Visual Studio .NET are not COM DLLs, so the information in this section is only applicable if you are calling a Visual Basic 6.0 DLL from a .NET program. Remotely creating objects in the .NET world was discussed in Chapter 9, "Creating Code Components." Note In Visual Basic .NET, RDS has been replaced by the concept of remoting, as described in Chapter 9. The key to using RDS is its DataSpace object. You can create an instance of the RDS Data Space on the Internet client, as follows: Set objRDS = CreateObject("RDS.DataSpace") You can then use the RDS Data Space to create remote objects on a Web server: Set objTest = objRDS.CreateObject("MyDLL.MyClass", _ "http://server.somewhere.com") Notice the difference in the two calls to CreateObject. The first line of code creates a local object. The second line of code creates a remote object, objTest. This process is somewhat similar (but not exactly like) using DCOM to create an instance of an object on a remote machine, but then using the features of that object on a local machine. The sample object objTest is an instance of a DLL that runs on the Web server. After you create this instance, you can make method calls and pass data to and from it. The ADOR object library mentioned earlier is a minimal implementation of an ADOR Recordset (the R stands for remote) designed for this type of use. You can create functions in your ActiveX DLL to pass ADOR recordsets to the Internet client, which can then return the changed records to the server. Note To set RDS up on the server side, registry entries (ADCLaunch) and IIS permissions may need to be modified. For more details, search the Microsoft On-Line Knowledge Base for RDS. |