Tutorials » Using Disconnected Recordsets

Anyone who works with databases will probably at some stage have occasion to work with a particular Recordset over a period of time. However, it would not be practical or desirable to maintain an open connection to the database throughout as this would tie up connection resources. Instead, the user can take advantage of the server's ability to create disconnected Recordsets.

These are, as the name suggests, Recordsets that have been disconnected from the data source, thus allowing the user to work off-line and move freely between records. If a Recordset is created with write permission, the user can also alter and delete records, or add new ones. These changes will be cached locally and not affect the main database. Later a connection can be re-established to the database, which can then be updated with the changes. One thing to bear in mind, however, is the possibility of conflict if one person alters a record while someone else is working with the same record off-line.

One requirement of disconnected Recordsets is that they must (for obvious reasons) be maintained by the database client, rather than the database server. The way this is done is by setting the CursorLocation property of the Recordset object to 'adUseClient'. The LockType property also needs to be set to either 'adLockReadOnly' for a read-only Recordset or 'adLockBatchOptimistic' for a writeable one. Both these ADO constants must be declared before the Recordset is opened, either explicitly in the code module in question, or through the inclusion of the 'adovbs.inc' file. The difference between these two methods is that declaring the constants directly only adds the two lines of code you, as opposed to the two hundred lines minimum that come with the include file.

In the following ASP code example, the two ADO constants are first declared explicitly and assigned the numeric equivalents required by VBScript. Then a query is created to get information from the Employee table, and a connection is established to the WorkforceDB database that holds that table on the MAINSERVER server. Finally the disconnected Recordet is built.

<%
' Declare the LockType and CursorLocation constants
Const adLockBatchOptimistic = 4
Const adUseClient = 3

' Declare and instantiate object variables
Dim cnnDB, rsEmployees, strEmployeeQuery

Set cnnDB = Server.CreateObject("ADODB.Connection")
Set rsEmployees = Server.CreateObject("ADODB.Recordset")
strEmployeeQuery = "SELECT FirstName, LastName FROM Employee"

' Establish a connection
cnnDB.Provider = "SQLOLEDB"
cnnDB.ConnectionString = "User ID=sa;" & _
      "Data Source=MAINSERVER;" & _
      "Initial Catalog=WorkforceDB"
cnnDB.Open

' Build the Recordset
rsEmployees.CursorLocation = adUseClient
rsEmployees.LockType = adLockBatchOptimistic
rsEmployees.Open strEmployeeQuery, cnnDB
%>

Then, once the Recordset has been created, you can disconnect from the data source and close the active connection thus:

<%
' void the Recordset's active connection
Set rsEmployees.ActiveConnection = Nothing
cnnDB.Close
%>

The user now has a Recordset that they are free to navigate and edit in any way. Using the Update method, the user can add or change a record. In the following example, an employee called Emma Jones has her surname changed to Watson.

<%
rsEmployees.MoveFirst
Do While Not rsEmployees.EOF
      If rsEmployees("FirstName") = "Emma" _
                   And rsEmployees("LastName") = "Jones" Then
             rsEmployees("LastName") = "Watson"
             rsEmployees.Update
             Exit Do
      End If
      rsEmployees.MoveNext
Loop
%>

One advantage of disconnected Recordsets in an ASP Web application is that they can be saved as Session objects.  This is particularly useful in applications where users can execute a query (such as a search) and then browse the resultset, which is broken-up over a series of pages, at their leisure. In such an application, a unique, personalized disconnected recordset is created each time a user performs a search.  This Recordset is then stored in the user's Session.  Since the Recordset is a completely self-contained object, there is no need to store the cursor position seperately, as the Recordset "remembers" this information itself.  Code can thus easily be written to allow the user to navigate through the disconnected Recordset without the need to consume a valuable database connection.

In addition, if appropriate measures are implemented to handle conflicting modifications, ASP pages can actually make changes to disconnected Recordset before finally submitting them back to the server to update the main database. In the following example, the user enters a new employee's first and last names into text boxes on a form. When submitted, this form then updates the disconnected Session Recordset with the new record and saves those changes back to the Session object.

<%
Dim rsEmployees
Set rsEmployees = Session("rsEmployees")

' add the new record
rsEmployees.AddNew
rsEmployees("FirstName") = Request.Form("FirstName")
rsEmployees("LastName") = Request.Form("LastName")
rsEmployees.Update

' update the Session object
Set Session("rsEmployees") = rsEmployees
%>

Note that when updating the Session object in this way, you should not close the Recordset, otherwise the following page will not be able to access it. As well as altering a record, the user can also delete one. In the next example the code loops through a Recordset comparing the FirstName and LastName fields with values requested from a form. If a match is found, the record is then deleted.

<%
rsEmployees.MoveFirst
Do While Not rsEmployees.EOF
      If rsEmployees("FirstName") = Request.Form("FirstName") _
                   And rsEmployees("LastName") = _
                   Request.Form("LastName")Then
             rsEmployees.Delete
             Exit Do
      End If
      rsEmployees.MoveNext
Loop
%>

Using the Session object to store a modifiable disconnected Recordset is only useful if all the off-line alterations are made during one session. If you were to do the work over a longer period, the information would be lost as soon as you closed the browser. However, you can save a disconnected Recordset to file (a process known as persisting data) using the Save method. By default, the Save method will not overwrite an existing file of the same name, so you will have to explicitly tell it to do so if you plan to save to file more than once. If you do not, an error message lets you know that the file already exists. The following code first checks for a previously saved version of a Recordset using the FileSystem Object, and if it exists, deletes it. Then the Recordset is saved to the C drive before being closed.

<%
Dim filesys
Set filesys = CreateObject("Scripting.FileSystemObject")
If filesys.FileExists("C:\Recordset.dat") Then
filesys.DeleteFile "C:\Recordset.dat"
rsEmployees.Save "C:\Recordset.dat"
rsEmployees.Close
%>

Later, when you wish to reopen the Recordset, you can do so with the following code:

<%
Dim rsEmployees
Set rsEmployees = Server.CreateObject("ADODB.Recordset")

' get Recordset from file
rsEmployees.Open "C:\Recordset.dat"
%>

Finally, if you decide to allow changes to the disconnected Recordset, when you want to update the main database with all the changes made, you must first re-establish a connection and then add the changes using the UpdateBatch method. (In the following example the Recordset is taken from a Session object.)

<%
Dim cnnDB, rsEmployees
Set cnnDB = Server.CreateObject("ADODB.Connection")
Set rsEmployees = Session("rsEmployees")

cnnDB.Open "WorkforceDB", "sa", ""

' Update the main database
rsEmployees.ActiveConnection = cnnDB
rsEmployees.UpdateBatch

' close the connection and Recordset
cnnDB.Close
rsEmployees.Close
%>

In conclusion, when faced with the need to repeatedly use the same information without altering it, the disconnected Recordset is ideal in that it reduces the number of times you connect to the database and the number of queries requesting the same data. Disconnected Recordsets can also provide a useful way of editing records over a period of time as long as you bear in mind the previously mentioned possibility of data conflict.

Finally, if your component is registered with MTS, you will need to inform MTS that the DLL has changed. To do this, right-click on My Computer in the Transaction Server Explorer and select the Refresh All Components option. Alternatively, you can run mtxrereg from any command prompt, or by selecting Run from the Windows NT Start menu, entering mtxrereg and then clicking the OK button.