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())
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())
Page last updated 2007-11-14 21:43. Some rights reserved (CC by 3.0)