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()
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
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.
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
Yeah, I don’t see any reason you couldn’t.
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
Solved it! Ofc it should be fwDb.Conn = objConn 🙂