How to use GetRows ADO RecordSet method in ASP

RecordSet.GetRows method, the best way to retrieve data with ADO! GetRows, does this sound familiar? If not, then it’s time to learn how to retrieve/manipulate your data more efficiently with ADO.

The ultimate goal of the software development is to produce efficient, easy to maintain and highly scalable applications. An application can be called scalable if it has the ability to serve a large number of concurrent users without incurring a performance penalty.

One of the most common tasks in programming is the data retrieval and manipulation and as far as my experience goes almost any software project I have worked on has involved databases. Having said that, lets continue with our main subject GetRows method of the ADO RecordSet object. I presume that you are familiar to some degree with ADO and the RecordSet object and you have basic programming skills.

In most cases, when you need to retrieve & display some data from a database you will use code similar to the one listed below:

<%

Set oConnection = Server.CreateObject("ADODB.Connection")
oConnection.Open(sConnectionString)


sSQL = "SELECT FirstName, LastName, Phone FROM tblCustomers"
Set oRecordSet = oConnection.Execute(sSQL)


While Not oRecordSet.EOF
Response.Write(oRecordSet("LastName") & ", " & oRecordSet("FirstName") & " - " & oRecordSet("Phone") & "
")
oRecordSet.MoveNext
Wend

Set oRecordSet = Nothing
oConnection.Close
Set oConnection = Nothing


%>


In short, you connect to the database, execute a SQL statement and get the result set into your RecordSet object. And what do you do then; you start looping through the RecordSet and display the result records one at a time. So what is wrong with looping through a RecordSet? The only problem with this approach is that is very inefficient and resource consuming. Is our little application above scalable? The answer is NO! Lets have a detailed look at our loop:

<%


While Not oRecordSet.EOF
Response.Write(oRecordSet("LastName") & ", " & oRecordSet("FirstName") & " - " & oRecordSet("Phone") & "
")
oRecordSet.MoveNext
Wend

%>
After we have displayed a record using Response.Write, we move to the next one with the MoveNext RecordSet method, until we reach the last record. Every time we access one of the records in our RecordSet we send a read request to the database server. What I’ve just said is not exactly correct, because the RecordSet object caches the retrieved data - when you request record #1 it will actually extract the first 100 records and it won’t connect to the database until your application request record # between 100 and 200. If we deal with huge sets of data, for example 10000 rows with 5 columns each, the retrieval time will be unacceptably high. Don’t forget that each .MoveNext and .EOF add an additional request to the database. Now we know that looping through a RecordSet is expensive in terms of server resources, so how do we improve the performance of our little application? The answer is GetRows. The GetRows method of the RecordSet object will extract the entire result set with only one call to the database and will assign the data to a 2 dimensional array:

<%
arrResultSet = oRecordSet.GetRows()
%>

You can limit the # of rows that you retrieve with GetRows by passing a number to the GetRows method. The example below will get only the first 1000 rows and assign them to the arrResultSet array:

<%
arrResultSet = oRecordSet.GetRows(1000)
%>


You can check the modified ASP script using the GetRows method below:

<%
Set oConnection = Server.CreateObject("ADODB.Connection")
oConnection.Open(sConnectionString)


sSQL = "SELECT FirstName, LastName, Phone FROM tblCustomers"
Set oRecordSet = oConnection.Execute(sSQL)


If Not oRecordSet.EOF
' Gets all the records
arrResultSet = oRecordSet.GetRows()
End If


'Close the connection with the database and free all database resources
Set oRecordSet = Nothing
oConnection.Close
Set oConnection = Nothing


' Retrieve the total # of rows
iRowNumber = ubound(arrResultSet,2)


' Loop through the array holding the result set and display the data
For iCounter= 0 to iRowNumber
Response.Write(arrResultSet(1,iCounter) & ", " & arrResultSet(2,iCounter) & " - " & arrResultSet(0,iCounter) & "
")
Next
%>