Home arrow ASP arrow Page 4 - Developing a VBScript Class for an Extremely Lightweight Recordset Alternative

Developing a VBScript Class for an Extremely Lightweight Recordset Alternative

Tom has written this article to demostrate an alternative to using the ADO Recordset Object to display data. Is it possible that doing this impoves functionality? Read more to find out ...

Author Info:
By: Wrox Team
Rating: 5 stars5 stars5 stars5 stars5 stars / 103
March 11, 2003
  1. · Developing a VBScript Class for an Extremely Lightweight Recordset Alternative
  2. · Introduction
  3. · How Much is That Recordset in the Window?
  4. · Feeling Loopy?
  5. · Concluding Thoughts

print this article

Developing a VBScript Class for an Extremely Lightweight Recordset Alternative - Feeling Loopy?
(Page 4 of 5 )

Do you see a bottleneck in this, though? Think about the huge amount of looping that this technique will incur. Imagine a Recordset with five fields (first name, last name, age, height and weight, let’s say) and 50 rows. We want to generate a simple HTML table. We load it to our ArraySet, paint out the "<table>" tag to our liking, and then create our table rows along these familiar lines:

Do Until objArraySet.EOF
  Response.Write "<tr>"
  Response.Write "  <td>" & objArraySet.fields("FirstName") & "</td>"
  Response.Write "  <td>" & objArraySet.fields("LastName") & "</td>"
  Response.Write "  <td>" & objArraySet.fields("Age") & "</td>"
  Response.Write "  <td>" & objArraySet.fields("Height") & "</td>"
  Response.Write "  <td>" & objArraySet.fields("Weight") & "</td>"
  Response.Write "</tr>"

Each time we write out the FirstName field, the internal loop that translates "FirstName" to the ordinal 0 has to run once (finding a match on the first pass). Each time we write out the LastName field, it has to loop twice, and so on. To paint out one row, the loop runs a total of 15 times (1 = 2 + 3 + 4 + 5). For 50 rows, that’s 750 times through the loop. Couldn’t I somehow loop it once for each field called and remember, for instance, that "FirstName" = 0 and "LastName" = 1? Then with each subsequent call for "Weight" I could have my code retrieve the stored value of 5 and carry on without more looping.

For the longest time, I couldn’t come up a suitable method. I considered building a hashing algorithm for the purpose, but with collision-handling routines and all, it would probably out-mass the rest of the ArraySet's code! And the good old Dictionary object was still sitting there, smiling at me, with that come-hither look it its binary eyes. I was about to give in when…

It occurred to me that VBScript’s rarely used, rarely mentioned Execute and Eval statements might save the day.

The Execute statement allows us to create an arbitrary string that looks like a valid VBScript command, and then force the VBScript engine to run it like a valid command.

Using Execute, we can run this command…

Execute "strName = ""Tom Kelleher"""

…and thereafter issue this command in the clear:

Response.Write strName

The output is "Tom Kelleher". The value of strName isn't set overtly, it's set by virtue of the Execute command, but it's set just the same and can be used anywhere else in your code. This means we can create ad hoc variables as we need them, and set the values we want associated with them as we go.

Let's roll back to our .Load method above. The code we used to create our field-name array was this:

 Dim i, arTmpFields()
  ReDim arTmpFields(intFieldCount)
  For i = 0 to intFieldCount-1
    arTmpFields(i) = rs.fields(i).Name
  arFieldNames = arTmpFields

Instead, let's use Execute and see what we can do. Let's create a set of variables using the field name as part of the variable's name. We'll tag on the prefix "fld_" for good measure, too.

  Dim i
  For i = 0 to intFieldCount-1
    Execute "fld_" & rs.fields(i).Name & " = " & i

So for our SQL query of "SELECT FirstName, LastName, Age, Height, Weight FROM MyTable" we are creating these variables and assigning them these values:

fld_FirstName = 0
fld_LastName = 1
fld_Age = 2
fld_Height = 3
fld_Weight = 4

These variables can then be used elsewhere in our code! Rather than looping again and again, we just have need to determine whether we have a variable by that name. Let's replace that looping code in our .Fields method. The code we used before to determine if intSelectedField was a field name was like this…

  Dim i
  intOrdinal = -1
  intSelectedField = Trim(intSelectedField & "")   'Turns Nulls to ""
  For i = 0 To intFieldCount-1
    If LCase(intSelectedField) = LCase( arFieldNames(i) ) Then
      intOrdinal = i
      Exit For
    End If

Let's just take intSelectedField and see if we have a variable by that name! We do this using the Eval command. Like the Execute command, Eval takes a string and treats it like raw VBScript. But rather than executing it, it (can you guess?) evaluates it. Where Execute "xyz = 123" would set the variable xyz to the value 123, Eval "xyz = 123" would return True or False, depending whether xyz already equals 123. The useful part of this for our purposes is that when handed a simple string that might be the name of a variable, Eval returns the value of that variable if it exists, and Empty if it doesn't. So…

  Dim strTest
  strTest = "fld_" & intSelectedField
  If Len( Eval( strTest ) ) = 0 Then
    'No such variable exists, so this is not a valid field name
    Err.Raise vbObjectError + 99999, "C_ArraySet:Fields", "No field with the
name '" & intSelectedField & "' exists."
    Exit Function
    'Such a variable does exist, so we use Eval to tease out its value
    intOrdinal = Eval( strTest )
  End If

Much cleaner, requires no looping, and uses a two peculiar VBScript commands that your peers might not know about. Pretty cool.

Let the fun begin

With our three key elements in place – the data storage, the internal cursor, and the data-field addressing mechanism – we have achieved most of what we set out to do. We have an extremely lightweight VBScript class that provides the most-used functionality of a read-only ADO Recordset. We can code with it using essentially the same object syntax, so porting old code over to it is easy. And because we rely on ADO Recordsets only to deliver the data, not manage its lifecycle, we can Nothing-ize the Recordset immediately and release that bulky object from memory.

But what more can we do? Quite a bit.

My beloved .RecordCount property
Let's get this one out of the way, right away. It's so simple, so obvious. I'll even forego the blank line white space and rejoice in its splendid brevity:

Public Property Get RecordCount()
  RecordCount = intArrayUBound + 1
End Property

Delightful. No matter what ADO Recordset I use to create it, I always have my .RecordCount.

Persisting and DePersisting

If you use a particular Recordset in your ASP application over and over, you've fought off the impulse to persist it to the Session or Application object. (Or you've fallen to temptation and suffered the slings and arrows of outrageous threading issues.) ADO Recordsets persisted to as Session or Application variables can bog down IIS magnificently, because they aren't built for it. In my own work there are dropdown lists I need to build over and over, tables of client data to present, lists of user-parameters to build, and so on…and I have to repeatedly pull this same data with a new ADO Recordset every time. I'd much prefer to pull it once, persist it, and then reuse it as needed.

With our ArraySet object, we can, without threading penalties.

The contents of an ArraySet is held in two variant variables – one is the data array, and the other the field-name array. Unlike ADO Recordsets, which cannot be safely saved at the Session or Application level, these can. We are immediately free to implement our own persisting/depersisting mechanism, and make any ArraySet available to any user of our website. This saves us from needing to perform frequent identical trips to the database, creating identical ADO Recordsets, further reducing the load on the system.

To be 100% clear: the data can be saved at the Session or Application level, not the instance of the C_ArraySet class itself . I have not experimented, but would assume the same single-threading issues arise for VBScript classes as for most other classes – and I don't recommend it. But variants containing arrays can be saved this way, and those fully describe any given ArraySet anyway. Save those, and you've achieved the end.

(And for those of you cranking up your flywheels to rant about the evils of Session variables…I know, I know. Please see the section towards the end called "Anticipatory Objections.")

I knew I'd want to persist an arbitrary number of ArraySets, depending on circumstance. Therefore my persistence mechanism needed to let me assign each a unique name. I would assign this name when persisting, and use it to select one when "reconstituting" it later on, by reloading the persisted data, and finally to depersist it as well. And I knew I'd want the choice of whether to persist the ArraySet to the Session or Application level. So my goal was to write code along these lines:

‘Instantiate my ArraySet object
Dim objArraySet
Set objArraySet = New C_ArraySet
‘Create my ADO Recordset
Dim conn, rs, SQL
Set conn = Get_Connection()  'Use your own favourite
          'connection-making routine here
Set rs = conn.Execute(SQL)
objArraySet.LoadData rs
Set rs = Nothing
Set conn = Nothing
' Persist the data, in this case to the Session object, with
' the unique identifier of "MyTable_Full"
Call objArraySet.Persist("SESSION", " MyTable_Full")
Set objArraySet = Nothing

In the above example, I wanted both the internal arrays saved (somehow) to the Session object, tagged somehow with the identifier "AllMyTable", so that on another page at another time I could reload the persisted data into a new ArraySet with code like so…

‘Instantiate my ArraySet object
Dim objArraySet
Set objArraySet = New C_ArraySet
Call objArraySet.LoadPersisted("SESSION", "AllMyTable")
Do While Not objArraySet.EOF
  ' …perform some task on each record

The solution I wrote for persisting the data follows here:

Public Sub Persist(ByVal strMethod, ByVal strUniqueID)
  'To persist these two arrays, we will create
  'another array, with one-dimension and two elements,
  'and load our two existing arrays into it.  Then
  'we will save this new array as needed.
  Dim ar(1), vArray
  ar(0) = arFieldNames
  ar(1) = arDataset
  vArray = ar
  'Persist vArray as a Session or Application variable,
  'creating a key for it with the structure:
  '   C_ArraySet:[unique-id]

  Select Case strMethod
    Case "SESSION"
      Session("C_ArraySet:" & strUniqueID) = vArray
      Application("C_ArraySet:" & strUniqueID) = vArray
    Case Else
      Err.Raise vbObjectError, "C_ArraySet:Persist", "You must specify either
'Session' or 'Application' as the method."
  End Select
End Sub

The approach above is to create a new one-dimensional array with a UBound of 1 (i.e., two elements), and load the arFieldNames variable to one, and arDataset to the other. Then, depending on whether the calling code requests persistence to the Session or Application, a Select Case statement completes the job., inventing a key name for it based on the strUniqueID parameter. The Session variable in my sample code earlier would therefore create this Session variable: Session("C_ArraySet:MyTable_Full"). (I chose to include the string C_ArraySet into the key name, to minimize the chance of overwriting some other Session or Application variable in the system.)

To reconstitute persisted ArraySets, we would use the .LoadPersisted method – which uses the same two parameters, as shown below:

Public Sub LoadPersisted(ByVal strMethod, ByVal strUniqueID)
  'A new method (incomplete) to load persisted values
  'from file or Session variable to
  strMethod = Trim( UCase(strMethod) )
  Dim ar
  Select Case strMethod
    Case "SESSION"
      ar = Session("C_ArraySet:" & strUniqueID)
      ' pull data from Application variable
      ar = Application("C_ArraySet:" & strUniqueID)
    Case Else
      Err.Raise vbObjectError + 99999, "C_ArraySet:Persist", "You must specify
either 'Session' or 'Application' as the method."
  End Select
  If Not IsArray(ar) Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Persist", "No persisted
ArraySet found with this ID: " & strUniqueID
    Exit Sub
  End If
  'At this point, assume we have successfully acquired a persisted
  'ArraySet variable.  Prepare the other key internal variables.
  arFieldNames = ar(0)
  arDataset = ar(1)       
  bBOF = False
  bEOF = False
  intCursorLocation = 0
  intFieldCount = UBound(arFieldNames)
  intArrayUBound = UBound( arDataset, 2 )
  bRsLoaded = True
  bReadyForUse = True
End Sub

When using .LoadPersisted, we simply backtrack the logic of our .Persist method. We indicate the method (Session or Application) we used to persist it, provide the unique ID we assigned it, and let it try to load that variable. If none such is found, it raises an error. If it is found, it breaks it into it's two components (field-names, and dataset), prepares the usual internal variables, and it's ready for use.

To depersist an ArraySet, we merely do the following:

Public Sub DePersist(ByVal strMethod, ByVal strUniqueID)
  'Destroys the persisted file or variable.
  strMethod = Trim( UCase(strMethod) )
  Select Case strMethod
    Case "SESSION"
      Session("C_ArraySet:" & strUniqueID) = Empty
      Application("C_ArraySet:" & strUniqueID) = Empty
    Case Else
      Err.Raise vbObjectError + 99999, "C_ArraySet:DePersist", "You must specify
either 'Session' or 'Application' as the method."
  End Select
End Sub

This again takes the method and ID used to persist the data, and assigns the value of Empty to the appropriate variable. We don't need to check for the existence of it in this case; the Session and Application objects are very forgiving of assigning Empty to a non-existent variable. Neither causes an error.

By the way, you may have noticed that the .LoadPersisted method catches requests for non-existent Session or Application variables. Knowing it's possible that a user might call for a non-existent variable almost begs for the creation of an .IsPersisted(strMethod, strUniqueID) method. It would simply check to see if any such variable exists by that name. If so, it returns True, of not it returns False, as shown below.

Public Function IsPersisted(ByVal strMethod, ByVal strUniqueID)
  'Determine if such data was persisted
  strMethod = Trim( UCase(strMethod) )

  Select Case strMethod
    Case "SESSION"
      IsPersisted = Not IsEmpty( Session("C_ArraySet:" & strUniqueID) )
      IsPersisted = Not IsEmpty( Application("C_ArraySet:" & strUniqueID) )
    Case Else
      Err.Raise vbObjectError + 99999, "C_ArraySet:IsPersisted ", "You must
specify either 'Session' or 'Application' as the method."
  End Select
End Function

Roll-your-own methods

At this point, my ArraySet object provides me with just about every function I need to get by. However, there are times when the venerable old ADO Recordset's properties (or lack of them) are just plain annoying.

How many times do you paint out HTML tables with alternating colored rows? To do this, do you have to Dim a new variable just to keep track of whether you're on an "even" row or an "odd" row? Something like this, perhaps:

Dim i
Do While Not rs.EOF
  i = i + 1
  If i Mod 2 Then
    ' …make this table row blue
    ' …make this table row white
  End If

Because our internal cursor is so accommodating, we can write a property to simply ask it "Are you on an even-numbered row or an odd-numbered row?" Then we could rewrite the code above like so:

Do While Not objArraySet.EOF
  If objArraySet.EvenRow Then
    ' …make this table row blue
    ' …make this table row white
  End If

The internals of the .EvenRow property are thoroughly mundane. We determine whether the value of intCursorLocation is even or odd with the Mod statement, and return the Boolean:

Public Function EvenRow()
  EvenRow = IIF(intCursorLocation Mod 2, False, True)
End Function

Another thing that aggravates me about the ADO Recordset is the awkward and non-intuitive need to test for two properties – .BOF and .EOF – to determine whether a Recordset has no records. Why? Wouldn't a .IsEmpty property be more to the point?

Public Property Get IsEmpty()
  IsEmpty = (bBOF And bEOF)
End Property

How about the ability to test whether a Recordset includes a particular field?

Public Function FieldExists(ByVal FieldName)
  Dim strTest, intOrdinal
  strTest = "fld_" & FieldName

  If Len( Eval( strTest ) ) = 0 Then
    FieldExists = False
    FieldExists = True
  End If
End Function

It becomes fun, actually, thinking of the ways that the ADO Recordset falls short, and building in the pieces we need.

blog comments powered by Disqus

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials