ADO - ADO GetRows Method for Array-Based Data Retrieval

The GetRows method in ActiveX Data Objects (ADO) is used to retrieve multiple rows from a Recordset and place their values into a two-dimensional array. Instead of processing each record individually, an application can use GetRows to obtain a group of records at once. This can make data processing simpler when the application needs to work with a collection of database values in memory.

Purpose of the GetRows Method

Normally, an ADO Recordset can be processed one record at a time by moving through its rows using methods such as MoveNext. This approach is useful when each record requires individual processing. However, when an application needs to retrieve several records and perform operations on them together, repeatedly accessing the Recordset may be inconvenient.

The GetRows method provides an alternative by copying the requested records from the Recordset into an array. The basic syntax is:

array = recordset.GetRows(rows)

Here, recordset represents the ADO Recordset and rows specifies the number of rows to retrieve. If the number of rows is omitted, GetRows attempts to retrieve all remaining records.

How the Returned Array Is Organized

One important characteristic of GetRows is that the resulting array is arranged by fields first and rows second. This is different from the way database tables are normally visualized.

For example, suppose a Recordset contains:

ID	Name	Department
101	Arun	Sales
102	Meera	Finance
103	Ravi	IT

When these records are retrieved using GetRows, the resulting array conceptually looks like this:

Array(0,0) = 101
Array(0,1) = 102
Array(0,2) = 103

Array(1,0) = "Arun"
Array(1,1) = "Meera"
Array(1,2) = "Ravi"

Array(2,0) = "Sales"
Array(2,1) = "Finance"
Array(2,2) = "IT"

The first index represents the field, while the second index represents the row. Therefore, Array(1,2) refers to the Name field of the third retrieved record.

Example Using GetRows

The following example demonstrates the basic use of GetRows:

Dim rs
Dim data
Dim i

Set rs = CreateObject("ADODB.Recordset")

rs.Open "SELECT ID, Name, Department FROM Employees", _
        connectionObject

data = rs.GetRows()

For i = 0 To UBound(data, 2)
    WScript.Echo data(0, i) & " - " & _
                 data(1, i) & " - " & _
                 data(2, i)
Next

rs.Close
Set rs = Nothing

In this example, the query returns employee information. GetRows() retrieves the available records and stores them in the data array. UBound(data, 2) determines the highest index of the second dimension, which represents the retrieved records.

The loop then accesses each field for every record.

Retrieving a Specific Number of Rows

GetRows can also be used when only a particular number of records is required.

data = rs.GetRows(10)

This requests up to 10 rows from the current position of the Recordset. If fewer than 10 rows remain, only the available rows are returned.

This is useful when an application does not need to load the entire Recordset into memory. For example, an application may retrieve a limited group of records for temporary processing.

Effect on the Recordset Position

GetRows does more than simply copy data. When rows are successfully retrieved, the current position of the Recordset advances past the rows that were returned. Consequently, calling GetRows again can retrieve the next group of records.

For example:

firstBatch = rs.GetRows(20)
secondBatch = rs.GetRows(20)

The first call retrieves up to 20 records beginning at the current position. The second call then retrieves the next available group.

This behavior can be useful for processing a Recordset in batches.

Handling an Empty Recordset

Applications should consider the possibility that the Recordset contains no records. If there are no rows available, GetRows cannot provide a normal collection of records for processing.

A common approach is to check the Recordset before calling the method:

If Not rs.EOF Then
    data = rs.GetRows()
End If

The exact handling can depend on the ADO environment and the type of Recordset being used.

Advantages of GetRows

The main advantage of GetRows is that it allows several records to be transferred from the Recordset into an array in a single operation. This can make subsequent processing straightforward because the application can work with ordinary array elements instead of repeatedly accessing Recordset fields.

It can also be useful when the retrieved information needs to be passed to another part of an application that works with arrays. For example, a presentation layer could receive the array and format the information without maintaining direct access to the database Recordset.

Another benefit is its suitability for batch-style processing. An application can retrieve a fixed number of rows, process them, and then retrieve another group.

Limitations and Considerations

GetRows should not automatically be considered faster or more memory-efficient in every situation. If a very large Recordset is converted into an array, the application may consume considerable memory because the retrieved values are held in memory.

Another consideration is that the returned array uses field-first indexing. Developers who are accustomed to representing data as row, column may initially find the structure less intuitive.

It is also important to remember that GetRows retrieves the values available from the Recordset; it does not replace database-side filtering or query optimization. If an application needs only specific records or fields, it is generally better to request only those records and fields from the database rather than retrieving unnecessary information and discarding it afterward.

GetRows vs. Processing Records Individually

There are two common approaches to processing Recordset data.

With individual processing:

Recordset
   |
   +-- Read record 1
   +-- Read record 2
   +-- Read record 3
   +-- Continue...

With GetRows:

Recordset
   |
   +-- GetRows()
          |
          +-- Array containing multiple records
                    |
                    +-- Process array

Individual Recordset processing is appropriate when each record requires immediate interaction with the Recordset. GetRows is more convenient when the application wants a collection of values that can be processed together.

Conclusion

The ADO GetRows method provides a convenient way to extract multiple records from a Recordset into a two-dimensional array. The first dimension represents fields and the second represents records, allowing applications to access retrieved values directly through array indexes. It can be particularly useful for batch processing, temporary in-memory data manipulation, and situations where an application needs to separate data retrieval from subsequent processing. However, developers should consider the size of the data being retrieved and avoid loading unnecessarily large Recordsets into memory.