Posts Tagged ‘recordset’

» posted on Thursday, July 31st, 2008 at 9:24pm by Dan

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: