Disconnected recordsets in ADO / classic ASP

Disconnected recordsets can be used to keep the time, in which a database connection is open, down to a minimum.

If for example you need to loop through large amounts of records, perhaps heavily manipulating data before use, the connection to the database can be open for very long. Maybe long enough to be a problem if there are many concurrent hits on the page.

One solution to this is to disconnected recordsets, that keep the database connection open just long enough for the recordset to be build, whereafter the connection is closed. You can then take your time looping through the recordset, without using database resources.

The implementation is based on 4 Guys From Rolla's article Using Disconnected Recordsets.

Function GetRS(strSQL) ' Returns a disconnected recordset
  Const adOpenStatic = 3    
  Const adUseClient = 3
  Const adLockBatchOptimistic = 4
  Dim objCon
  Dim objRS
  Set objCon = Server.CreateObject("ADODB.Connection")
  objCon.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\test.mdb;")
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.CursorLocation = adUseClient
  objRS.Open strSQL, objCon, adOpenStatic, adLockBatchOptimistic
  Set objRS.ActiveConnection = Nothing
  Set GetRS = objRS
  objCon.Close
  Set objCon = Nothing
End Function

Function GetPersonList() ' Returns formatted data from the discon. recordset
  Dim objRS
  Dim strOutput
  Set objRS = GetRS("SELECT fullName, birthDate FROM persons")
  If Not objRS.State = 0 Then
    If Not objRS.EOF Then
      Do While Not objRS.EOF
        strOutput = strOutput & objRS.Fields("birthDate").value & _
          " " & objRS.Fields("fullName").value & "<br />" & vbcrlf
        objRS.MoveNext()
      Loop
    objRS.Close
    Set objRS = Nothing
    Else
      strOutput = "No items found"
    End If
  Else
    strOutput = "No output"
  End If
  GetPersonList = strOutput
End Function

Response.Write(GetPersonList())
Tags: asp, databases
Page last updated 2007-11-14 21:43. Some rights reserved (CC by 3.0)