Dataset is provided in ASP.NET so that users can work in a disconnected mode. There is an alternative in classic ASP also by which we can work in a disconnected mode by storing the values of recordset object in a two dimensional array.
Generally we open a Recordset object and then using a Do While Not objRS.EOF ...Loop, Iterating through each row in the Recordset. This approach is fine and good,
There is an special Recordset method called GetRows(), which reads the contents of the Recordset into a two-dimensional array; then, to display the Recordset data,We can simply iterate through this array.After storing the data into the two dimensional array we can close the recordset and connection object.
While the syntax involved in using the array-based approach is a bit more confusing that simply looping through the Recordset via a Do While Not objRS.EOF ... Loop, the main motivation behind using GetRows() is performance.
Why LOOP and Movenext? They just slow things down...
Most people write database retrieval code like his:
<% '
Open Database
Do UNTIL rs.eof
city=rs("city")
st=rs("state")
zip=rs("zip")
rs.movenext
...
process and format data
....
LOOP
' Close database
%>
If there are 700 records and 3 columns for example, we have 3,500 database read requests over the wire.
+2,100 ... Each field read is a request
+700 ..... Each .movenext is +1 request
+700 ..... Each .eof test is +1 request
====
3,500 requests
Lots of round-trips. Actually I am fudging a little here.... the recordset object actually will maintain buffers in chunk sizes defined by rstemp.cachesize -- setting it will determine how many rows of data it buffers when 1 record is requested. A cachesize for example of 50 would reduce the previous examples trips to the backend database to 14 since when you asked for the first record you got 50. Until the MOVENEXT triggers record 51-101 being retrieved. But those buffers, and their attendant structures ain't free either.
Before we get started there are three important things to remember when working with GetRows; remembering these three things will save you many headaches and hours debugging, trust me! They are:
- VBScript arrays start at 0 and go to n-1, where n is the count of the records (or count of columns) in the recordset. If you have 30 records, you will have rows numbered from 0 to 29; if you have 5 columns, you will have columns numbered from 0 to 4. In other words, just as if you had done Dim rows(4,29) to declare the array size yourself.
- VBScript arrays are arranged by MyArray(ColumnElement, RowNumber) instead of MyArray(RowNumber, ColumnElement)
- The order of field selection in your SQL statement determines the column subscript used to access that field. If you have 5 fields you will have 0 to 4 column elements.
Imagine that you have the following SELECT SQL statement:
Select fName, lName, Address, City, State, Zip
FROM SomeTable
ORDER BY lName, fName
and that you have already created and opened both Connection and Recordset objects. To read the contents of the Recordset into an array, use:
MyArray = rsMyRecordSet.GetRows()
VBScript sets up MyArray(ColumnCount, RowCount) with ColumnCount being the number of fields selected - 1 (0 based) and RowCount being the number of records returned - 1 (0 based)
To access the elements of an array we need to use integer index values. To get the first column of the first row, we'd use: MyArray(0,0). This approach, however, is very unreadable. To make your code more readable (and hence more maintainable), consider creating constants whose values represent the position in the array for the column and whose name is similar to the column's name. For example, it would be prudent to add:
Const MyFirstNameOrdinal = 0
Const MyLastNameOrdinal = 1
Const MyAddressOrdinal = 2
Const MyCityOrdinal = 3
Const MyZipOrdinal = 4
In this instance you will have five columns in the array numbered 0 through 4
if we want to get the total number of columns or rows, we have to use different UBound statements, as shown below:
Ubound(MyArray,1) 'Returns the Number of Columns
Ubound(MyArray,2) 'Returns the Number of Rows
For our first example I will show how to display the data in some predetermined format, like:
lName, fName : address : city
The code to do this would be a simple loop through each of the rows in the array; then, in the loop body, Response.Write statements would output the proper array values:
For lnLoopCounter = 0 To Ubound(MyArray,2)
Response.Write MyArray(MyLastNameOrdinal, lnLoopCounter) _
& ", " _
& MyArray(MyFirstNameOrdinal, lnLoopCounter) _
& " : " _
& MyArray(MyAddressOrdinal, lnLoopCounter) _
& " : " _
& MyArray(MyCityOrdinal, lnLoopCounter) _
& "
" & vbNewLine
Next
If you were wanting to display the fields in the exact order as they were presented in your SELECT clause you could add an inner loop for the columns. I usually just manually output the columns (as shown above) for simplicity, but either approach will work:
For lnRowCounter = 0 To Ubound(MyArray,2)
For lnColumnCounter = 0 To Ubound(MyArray,1)
Response.Write MyArray(lnColumnCounter, lnRowCounter)
Next
Next
