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:

<!-- #Include File = "XoiseRecordset.inc" -->
<%
' Create database connection object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open = "Connection String Here"
 
' Create my recordset object
' Was: Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS = New XoiseRecordset
 
' Set property data
objRS.Conn = objConn
objRS.SQL = "SELECT Col1, Col2, 3+3 AS Col3 FROM Tbl;"
 
' Iterate through the data
' Note: I DID implement paging, if you want to use that!
While Not objRS.EOF
	strCol1 = objRS("Col1")
	strCol2 = objRS("Col2")
	strCol3 = objRS.Row(2) ' Index starts from 0
	objRS.MoveNext()
Wend
 
' Destroy recordset object
' No need for .Close!
Set objRS = Nothing
 
'Destroy database connection
objConn.Close()
Set objConn = Nothing
%>

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()

Bookmark this page

Close Bookmark and Share This Page

Save to Browser Favorites / Bookmarks

Email This to a Friend

Copy HTML:

If you like this then please subscribe to the RSS Feed or Email Feed.

This entry was posted on Thursday, July 31st, 2008 at 9:24pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Classic ASP: More Efficient Database Recordset, GetRows() XoiseRecordset”

Falk Says:
October 3rd, 2008 at 3:21am
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
Dan Says:
October 3rd, 2008 at 10:51am
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.

Random Web Tools

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

2 Responses to “Classic ASP: More Efficient Database Recordset, GetRows() XoiseRecordset”

Leave a Reply