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

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
TABLE OF CONTENTS:
  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
SEARCH DEVARTICLES

Developing a VBScript Class for an Extremely Lightweight Recordset Alternative - How Much is That Recordset in the Window?
(Page 3 of 5 )

Consider how many properties even the simplest Recordset contains. It’s instructive to see what the darn thing has packed inside it by running this simple code:

Dim conn, rs, SQL
SQL = "SELECT * FROM MyTable"
Set conn = Get_Connection() 'Use your own favorite
                             'connection-making routine here
Set rs = conn.Execute(SQL)
Dim prop, i
Response.Write "Recordset properties<hr>"
Response.Write "<pre>"
For each prop in rs.Properties
  i = i + 1
  Response.Write i & ": " & vbTab & prop.name & vbcrlf
Next
Response.Write "</pre>"
rs.close
Set rs = Nothing
conn.close
Set conn = Nothing

On my ADO 2.6 machine, pulling data from an unremarkable table on a SQL Server 2000 database, I discover my humble Recordset has eighty-five properties. How many of these have any meaning to me? A scant few. Do I typically use the "Maximum BLOB Length" or "Rowset Fetch Position Change Notification" or "Re-entrant Events" properties? Not that I can tell, no. And running the same kind of For-Each routine on individual fields, I find another 16 properties attached to each – mostly useless to me.

In VB 6.0 perhaps I can find exciting uses for these – but in the variant-only, event-free world of classic ASP, I had to ask myself: "How much am I paying for all this extra stuff?" All I want to do (mostly) is run once through the Recordset and paint out my HTML – so what does this cost me, maintaining it’s hyper-propertied existence throughout its life cycle – in terms of system resources?

And could I do better?

My goals

What I wanted, then, was to build a container for my data that would have just the features I needed – all of them, not some pre-ordained mix-and-match subset – and none of the features I didn’t want. I wanted it to be as fast as the ADO Recordset at delivering up my data when I needed it. And for ease in porting old code (and to simplify training team-mates in its use), I wanted it to have as similar an object structure as the ADO Recordset, so the syntax of its usage would be familiar.

And because, by and large, my primary use for ADO Recordsets are to present HTML to the user, I'd expect to use this VBScript object only for "small" Recordsets. What does "small" mean, then? I rarely need to build a 1000-row table, or put 1000 items into a dropdown list…so I'll assume 1000 records as an upper limit of it's usefulness. (I won't design it to fail beyond 1000 records; I'll just know it's probably not winning me much benefit at that point.)

And as I started, I realized I could give it all the properties I wish every Recordset has but doesn’t. Like .RecordCount. What a great property! (Why do we have to buy the deluxe ADO Recordset just to get our hands on .RecordCount ?) In my perfect world, I’d always have a .RecordCount property…so I decided to build it in.

One annoyance I ran into right off: We really can’t avoid ADO Recordsets altogether. We need to get data out of the database. Clever as (I hope) I am, I can’t write a VBScript object that fires the low-level OLE-DB API calls to pull data without using a Recordset as the mailman. So let me be a little clearer on this point. Let's distinguish the two purposes that an ADO Recordset serves for us…

It’s the delivery system that brings data from the database to our code, and…
It contains and manages that data while our code steps through its contents…
…then we know we can’t replace purpose #1 above. If we want a chunk of data that we can mingle into our ASP code, that data must come wrapped in a Recordset. But purpose #2 above is up for grabs. A VBScript class can easily handle the lifecycle work – the delivering up of each field’s data, the stepping through records, the rendering of the precious .RecordCount value, and so on.

So our generalized plan becomes:

1. Create an ADO Recordset
2. Make it deliver up its data to our VBScript object
3. Close and Nothing-ize the Recordset as soon as possible
4. Carry on, using our VBScript object.

The Recordset will live the brief life of a skittish mayfly with a heart condition, leaving only its data for us to remember it by. I wanted, in essence, to do this:

‘Instantiate my ArraySet object
Dim objArraySet
Set objArraySet = New C_ArraySet
‘Create my ADO Recordset
Dim conn, rs, SQL
SQL = "SELECT * FROM MyTable"
Set conn = Get_Connection()  'Use your own favorite
                              'connection-making routine here
Set rs = conn.Execute(SQL)
objArraySet.LoadData rs
rs.Close
Set rs = Nothing
conn.close
Set conn = Nothing
Response.Write "<table>"
Response.Write "<tr>"
Response.Write "<td colspan=5>Records: " & objArraySet.RecordCount & "</td>"
Response.Write "<tr>"
Do While Not objArraySet.EOF  ‘It has an .EOF property as well
  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>"
objArraySet.MoveNext
Loop
Response.Write "</table>"
Set objArraySet = Nothing

Elements of an "ArraySet"
To meet my goals, my ArraySet object would need three key elements:

A data storage mechanism

A row-traversal mechanism (the familiar .MovePrevious, .MoveNext, etc. methods)
A data-field addressing mechanism (akin to the rs.fields("myField").value commands)
Data storage mechanism
My choice for data storage was unremarkable: a two-dimensional array. Since all typical (non-shaped) ADO Recordsets are essentially two-dimensional entities – with rows as one dimension, fields as the other – it made sense to use a two-dimensional array as the heart of the VBScript object. Because all ADO Recordsets provide the handy .GetRows() method to render their data as an array, this was doubly attractive. And because all Recordsets provide the .GetRows() method and create equivalent arrays, it occurred to me I could make my object (which I call an ArraySet) as robust and feature-packed as I liked from even the skimpiest Recordset source. For example, I could include my beloved .RecordCount property even if the original Recordset lacked it.

So let’s look at the initial stub of this VBScript class. We'll include just enough now to instantiate it, and load it with data. (Remember that for a VBScript class, all this code must be contained with the Class C_ArraySet and End Class commands.)

  'Primitive variables for various purposes
  Private bBOF    'Indicates if we are at beginning of record array
  Private bEOF    'Boolean to indicate if we are at end of record array
  Private intRecordCount  'The number of records/rows in the array
  Private intFieldCount  'The count of fields/columns in the array
  Private intCursorLocation  'The ordinal of the current record/row

  'Array variables for containing the data
  Private arDataset    'The array containing the records
  Private arFieldNames  'An array containing the names of the fields

  Private Sub Class_Initialize()
    'Set initial values for all
    bBOF = True
    bEOF = True
    intRecordCount = -1   
    intFieldCount = -1
    intCursorLocation = -1
    bReadyForUse = False

    arDataset = Array()  'Load these with empty arrays
    arFieldNames = Array()   
  End Sub

  Public Sub Load(ByRef rs)
    'Method to load the data from the Recordset
    'First check that we actually received a Recordset
    If TypeName(rs) <> "Recordset" Then
      Err.Raise vbObjectError + 99999, "C_ArraySet:Load()", "Load method
requires a Recordset object."
    End If
    On Error Resume Next
    arDataset = rs.GetRows()    'Harvest the Recordset's data
    'Make sure some data was contained actually in the
    'Recordset.  If not, the .GetRows() method above returns
    'an error.  If so, we know there was no data, and set
    'our variables to suit.
 
    If Err.Number <> 0 Then
      '.GetRows failed
      intRecordCount = -1
      intFieldCount = 0
      bBOF = True
      bEOF = True
      intCursorLocation = -1
      bReadyForUse = False
      bRsLoaded = False
      Exit Sub
    End If
    On Error GoTo 0

    'At this point we know we have some data
    bBOF = False
    bEOF = False
    intCursorLocation = 0
    bRsLoaded = True
   
    intRecordCount = UBound(arDataset, 2)  'Get the # of records
    intFieldCount = rs.Fields.Count    'Get the # of fields
    Dim i, arTmpFields()
    ReDim arTmpFields(intFieldCount)
   
    For i = 0 to intFieldCount-1
      arTmpFields(i) = rs.fields(i).Name
    Next
   
    arFieldNames = arTmpFields
    bReadyForUse = True
  End Sub

The .Load method is where the fun begins. It's fairly simple: It accepts a Recordset object, double-checks that it is a Recordset, and if so uses the .GetRows command to save the data to arDataset. Note the use of On Error Resume Next just before the .GetRows command. As the comment explains, this lets us handle empty Recordsets properly. Assuming no errors occur, we then examine this array to determine how many rows and fields it has, storing these in variables for later use.

As a last step we load a one-dimensional array with the names of the fields. Why? Because we'll need an internal "lookup table" of field names later, so that when a user issues commands like varFN = objArraySet.fields("FirstName") we can translate the string "FirstName" to the correct ordinal value. This comes up in the section below on the data-field addressing mechanism.

Row-traversal mechanism

To recreate the familiar .MoveXXXX methods, we need to recreate the "cursor" concept. We do this with the intCursorLocation variable, using it to track which record is the current record. Each of these following methods…

. MoveNext
. MovePrevious
. MoveFirst
. MoveLast
. Move( [record-number] )
…will work simply by updating the value of this variable. The code for them follows.

Public Sub MoveNext()
 
  'Move from the current record to the next one.
 
  If intArrayUBound = -1 Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:MoveNext" , "ArraySet contains
no data"
  End If
 
  If intCursorLocation+1 > intArrayUBound Then
    bEOF = True
    Exit Sub
  End If
 
  intCursorLocation = intCursorLocation + 1
End Sub

The logic here is unremarkable. Each time .MoveNext is called, we increment the value of our cursor by 1 – while making sure we don't exceed the maximum range of the array. (We also do some error checking here, as elsewhere, to be sure our ArraySet actually contains some data!) The logic for the other row-navigation methods (below) follows the same basic logic.

The boundaries pose a concern for us, of course: How do we know when we have issued the last possible .MoveNext command and reached the end of the data? We do this the same way as in an ADO Recordset. The cursor can be traversing the Recordset, but it can also be in either the BOF (beginning) and EOF (end) state. We emulate these as well with Booleans, bBOF and bEOF, which get set to True when .MoveNext or .MovePrevious are issued one time too many. The cursor remains on the first (or last) record, but the

If you've grasped of the code above, then the code for .MovePrevious, .MoveFirst and .MoveLast should be fairly plain. I'll include it here for completeness.

Public Sub MovePrevious()
  'Move from the current record to the previous one.
  If intArrayUBound = -1 Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:MovePrevious", "ArraySet contains no data"
  End If

  If intCursorLocation < 1 Then
    bBOF = True
    Exit Sub
  End If
 
  intCursorLocation = intCursorLocation - 1
End Sub

Public Sub MoveFirst()
  'Move from the current record to the first record.
  If intArrayUBound = -1 Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:MoveFirst " , "ArraySet
contains no data"
  End If
  intCursorLocation = 0
  bBOF = False
  bEOF = False
End Sub

Public Sub MoveLast()
  'Move from the current record to the last record.
  If intArrayUBound = -1 Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:MoveLast " , "ArraySet contains
no data"
  End If
  intCursorLocation = intArrayUBound
  bBOF = False
  bEOF = False
End Sub

The .Move method is a little different, but not terribly so. With this method, we can jump directly to the desired row in the data. We just need to check that the desired row isn't out of bounds. If it's greater than the number of available rows, we point the cursor on the last row but set bEOF to True. If it's less than zero we point the cursor on the first row but set bBOF to True.

Public Sub Move(ByVal intRecordNumber)
  'Move from the current record to the record
  'at the intRecordNumber position in the array.
  'intRecordNumber is always treated as an absolute
  'value, not a relative value.
 
  If intArrayUBound = -1 Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Move " , "ArraySet contains no data"
  End If

  If Not IsNumeric(intRecordNumber) Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Move " , " Move method requires an integer"
  End If

  If CInt(intRecordNumber) > intArrayUBound Then
    intCursorLocation =   intArrayUBound
    bEOF = True
  ElseIf CInt(intRecordNumber) < 0 Then
    intCursorLocation =   0
    bBOF = True
  Else
    intCursorLocation = CInt(intRecordNumber)
  End If
End Sub

And because we have the bEOF and bBOF Boolean variables, we can quickly implement our own .BOF and .EOF properties:

Public Property Get EOF()
  EOF = bEOF
End Property

Public Property Get BOF()
  BOF = bBOF
End Property

Data-field addressing mechanism

So far, we have created two of our three fundamental elements. We have a data-storage system (an array) and a row-traversal system (our cursor). These let us put the object's "attention" on the row we want. Now we need a way to pluck the specific field we want from that row. This is the data-field addressing mechanism.

This posed an interesting challenge for me. I wanted to be able to use either the name of the field or its ordinal position, just like an ADO Recordset. For example, for a query based on SELECT LastName, FirstName FROM Patients, the following two commands must return the same result:

LN = objArraySet.fields("LastName")
LN = objArraySet.fields(0)

Let’s start with the second method, the use of ordinal values.

Think of each piece of data in our array as occupying a specific X-Y coordinate in a grid. Each piece of data exists in one discrete row, under one discrete field. We've already set our Y-value by using the .MoveNext method. We can use the field ordinal now, to specify our X-value. In keeping with the syntax of the ADO Recordset object, we'll call our method .Fields for consistency. (The astute – or bored – among you will notice this is technically a misnomer in our case. In an ADO Recordset, .Fields is a collection of objects – while here it is a method. We could have also made it a collection of VBScript field-objects, but we would have needed to create them as a separate class…and the benefit of all that work would be minimal.)

Public Function Fields(ByVal intSelectedField)
  If intArrayUBound = -1 Then Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Fields" , "ArraySet contains no
data"
  End If

  'Make sure we have allowed for the BOF and EOF conditions
  If bBOF Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Fields",  "Cursor is at BOF.
Use MoveFirst method to access fields."
    Exit Function
  End If

  If bEOF Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Fields" , "Cursor is at EOF.
Use MoveFirst method to access fields."
    Exit Function
  End If
  'Make sure the input is numeric…
  If Not IsNumeric(intSelectedField) Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Fields",  "Fields input is non-
numeric.  Expected an integer."
    Exit Function
  End If
 
  intSelectedField = Cint(intSelectedField)

  'Make sure the numeric input is within the range of
  'zero thru the # of fields

  If (intSelectedField > intFieldCount-1) OR (intSelectedField < 0)  Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Fields",  "Field #" &
intSelectedField & " doesn't exist."
    Exit Function
  End If
  'We seem to have a valid field number, so
  'return the field data
  Fields = arDataset(intField, intCursorLocation)
End Function

This works fine for ordinals. What about field names, then?

The issue now becomes one of translation. For example, for this SQL statement: "SELECT FirstName, LastName FROM MyTable"– we would need to translate the string "LastName" into the ordinal value of zero. The technique we choose has to allow for arbitrary field names – there’s no telling what field names might be used, and any alphanumeric field name should be valid. But we can assume that no two fields will have the same name though. Knowing this, the problem quickly resolves itself to a key/item issue, and various options present themselves.

You will probably be tempted to rush to our trusty Dictionary object. So was I. It’s built specifically for key/item pairs, and does a fine job of it. However, I felt it was backsliding a bit to use it, given my goals. Consider, the key benefit of this technique is to "throw away the wrapper"– to drop the compiled ADO Recordset object – as soon as possible. That benefit is somewhat lost if we have to maintain a Dictionary object instead throughout our ArraySet's lifetime. And there likely won’t be that many fields anyway – in my work it’s rare that I have even 25 fields in one Recordset – so less sexy techniques will suffice.

Recall that we created a one-dimensional array of field-names during the .Load method? Here's where it comes in handy. Let's rewrite part of our .Fields method above to handle string input as well as numeric.

Public Function Fields(ByVal intSelectedField)
  If intArrayUBound = -1 Then Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Fields" , "ArraySet contains no
data"
  End If

  'Make sure we have allowed for the BOF and EOF conditions
  If bBOF Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Fields",  "Cursor is at BOF.
Use MoveFirst method to access fields."
    Exit Function
  End If

  If bEOF Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Fields" , "Cursor is at EOF.
Use MoveFirst method to access fields."
    Exit Function
  End If

  Dim intOrdinal

'Determine if the input is numeric…

If IsNumeric(intSelectedField) Then
    'It IS numeric, so if its within the
    'upper and lower ranges, return the data
    intOrdinal = Cint(intSelectedField)
    'Make sure the numeric input is within the range of
    'zero thru the # of fields
    If (intOrdinal > intFieldCount-1) OR (intOrdinal < 0)  Then
      Err.Raise vbObjectError + 99999, "C_ArraySet:Fields",  "Field #" &
intOrdinal & " doesn't exist."
      Exit Function
    End If
  Else
    'It's NOT numeric, so treat it as a string and
    'cycle through the field-name array looking
    'for a match.

    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
    Next
  End If
  'Check to see if any matching ordinal value was found…
  If intOrdinal = -1 Then
    Err.Raise vbObjectError + 99999, "C_ArraySet:Fields", "No field with the
name '" & intSelectedField & "' exists."
    Exit Function
  End If
  'We seem to have a valid field number, so
  'return the field data
  Fields = arDataset(intOrdinal, intCursorLocation)
End Function

What we have added is the necessary code to translate the intSelectedField parameter into the ordinal integer, if it exists – while of course coughing up the proper exception if it doesn't. It does this by looping through the available field names with each run, comparing the trimmed, lowercase input with the values in the field array.


blog comments powered by Disqus
ASP ARTICLES

- 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 
Support 

Developer Shed Affiliates

 




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