Classic ASP: More Efficient Database Recordset, GetRows() XoiseRecordset

I’ve created what I believe to be a more efficient recordset object for traversing rows returned from a database query. I implemented all methods/properties that I feel make sense to implement. There’s definite room for improvement, but it’s a good start. My reasoning for creating this is that I like the functionality of using recordsets (via Server.CreateObject("ADODB.Recordset")), but there is a HUGE drawback in that it is horrible on the database. The Server.CreateObject approach makes a database call every time a EOF, BOF, or a traversal method (ie. MoveNext, MovePrevious, etc.) is called.

My fix is to use the GetRows() method to return it as a multidimensional array and just implement my own class with the same (or similar) functionality. There is also an advantage to using this Recordset over just plain using the GetRows() method. I went ahead and implemented a way to extract the column names from the query, which allows you to grab the information using the column name, instead of the index of the array (ie. objRS("Column_Name")). You may also access the data using the column index if you prefer. Note that I haven’t implemented support for "SELECT * FROM Tbl…" syntax, so you’ll HAVE to use the index for this.

There is one main usage difference to note. Rather than opening the recordset with .Open SQL_String, Connection_Object, you must individually update the .Conn and .SQL properties. I’ll most likely change this if there is enough interest and I create a newer version. If I do that, I’ll most likely provide cache support.

Download: XoiseRecordset.zip (1.29KB)

Usage:

Retrieve Data:

  • objRS("Column_Name")
  • objRS.Item("ColumnName")
  • objRS.Row(0)

Implemented Methods/Properties:

  • Conn
  • SQL
  • EOF()
  • BOF()
  • MoveNext()
  • MoveFirst()
  • MoveFirst()
  • MoveLast()
  • Move()
  • GetRows()
  • RecordCount()
  • PageCount()
  • PageSize()
  • AbsolutePage()
  • AbsolutePosition()

6 thoughts on “Classic ASP: More Efficient Database Recordset, GetRows() XoiseRecordset”

  1. Hi there,

    after some minutes of reading an testing: This class don’t support statements like “Select * FROM tblname” instead of “Select col1, col2 FROM tblname”. I know, the second way is more efficent, but sometime…for a lazy developer;)

    This class dosen’t support sqloledb as dataprovider? I’ll get an error at XoiseRecordset.inc, line 176 “not allowed, wenn object is closed…” but it works with dsn-connectionstring. Maybe i have to deal with the cursolocation or so…

    I wrote a class like this a few months ago, but diden’t implement all the methods and properties like you. Thanks, that was a good idea.

    greetings,
    Falk

  2. Yeah, I’m aware of some of the things the class is lacking. As an alternative fix for the “SELECT * FROM tblname”, you can use the column indexes with the .Row() method. However, I didn’t test anything with the SQLOLEDB.

    Thanks for your comments.

  3. Dan,

    Have I got this right, you are caching a recordset first, then allowing a query against that cached recordset? Does this mean I can run sub queries against the original recordset?

    Regards
    Pete

  4. Hello!
    Having trouble understanding how to get this to work. I’ve tried to called Conn() and SQL() but I get an error telling me that “Object doesn’t support this property or method: ‘Conn'”. I guess I’m doing it wrong somehow and was wondering if you could help me figure it out :-)

    Set fwDb = New XoiseRecordset
    fwDb.Conn(objConn)
    fwDb.SQL(“SELECT userName FROM users WHERE ID = 1″)

    is the code I’m trying to execute. Don’t know if this is something thats active, but i thought it could not hurt to ask :-)

    /Johan from Sweden

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>