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