Passing Variant Arrays | Ado Examples and Best Practices

Team-Fly

Because ADO also knows how to generate Variant arrays from Recordsets, just as it can generate delimited strings, it might make sense to construct an array containing the rows we need to fetch. However, the results of my (and Edward's) tests were very disappointing. Consider these facts:

Variant arrays return Unicode strings. That is, each string is converted at the server to a 16-bit-per-character form before transmitting it to the client.
Many packets returned to the user are padded for some reason with the text "User."
The number of raw packets returned is almost three times the number returned for a disconnected Recordset containing the same data. Part of this difference is due to the Recordset VarChar fields being marshaled as 8-bit characters instead of as 16-bit characters.

The raw numbers for this test tell the story. The Variant array cost 87 frames and 94,888 bytes, while the Recordset cost 34 frames and 23,406 bytes.

If you decide you have to pass back a Variant array despite the results from our tests, I won't stop you—in fact, I'll show you how. The Recordset GetRows method is used to construct a Variant array from your data rows. However, as with a delimited string, you won't get the field names unless you tack them on to the array or pass them separately. You will get better element handling as the individual array elements (the Recordset fields) are populated with some DDL information that helps Visual Basic understand what each element contains. You can also tell how many rows and fields are in the array by using the UBound and LBound functions against the array—assuming you don't already know.

While Variant arrays initially take less overhead than a Recordset, they also consume more resources than strings or string arrays. Variant arrays do, however, expose string, numeric, and binary fields. This can be useful in cases where you want to figure out what to do with a binary value in an array element. For example, you could try to pass back a picture in a Variant array element, but once it arrived in your Visual Basic program, what would you do with it? The Image and Picture controls don't know how to present anything except file-based pictures (or PropertyBag elements). You'll have to figure out how to get the binary bits saved to a file and go from there. On the other hand, if you're clever, you could figure out what a Data Source control (like the ADO data control) does to solve this problem. It seems to me it's easier to store the picture in a file in the first place.

Another point: When I did the tests to see how many packets were generated by the various data access techniques (Recordsets vs. Variants vs. delimited strings vs. user-defined types vs. PropertyBags), I discovered that Variants consumed almost three times as many network resources as Recordsets. No serious architectural choice should be made without considering network transmission burdens.

The Server-side Code

The following code processes the Recordset generated by a SELECT query. It tests to see whether any rows were returned because you can't use GetRows unless there are rows. The routine passes the Variant array back to the client tier. If no rows are returned, we set the Variant array to Empty.

 If rs.EOF Then     vData = Empty Else     vData = rs.GetRows(−1) End If  GetAuthors = strData

The Client-side Code

This client-side code extracts rows from the Variant array and posts them to a grid. The UBound function is used to see how many rows and columns were returned. Remember that Variant array elements are referenced backwards. That is, the array is indexed: MyVariantArray(column, row). This example code also fills in the column headings manually.

 Sub FillGrid(varParm As Variant) Dim i As Integer, j As Integer With MSHFlexGrid1     .Clear    .Cols = UBound(varParm, 1)     .Rows = 5     .Row = 0     .Col = 0     .Text = "Author"     .Col = 1     .Text = "Year Born"         For i = 1 To UBound(varParm, 2) ' Loop through resultset returned             .Row = i             For j = 0 To UBound(varParm, 1) − 1                 .Col = j                 .Text = "" & varParm(j, i)             Next j         Next i         .Row = 1         .Col = 0         .ColWidth(0) = TextWidth(String(Len(.Text), "M"))     End With End Sub

Team-Fly