What is the most efficient way to access a field value in an ADO recordset?

Question

I have a VB6 application in front of me that accesses Sql databases through ADO.

When retrieving recordsets, the application uses Bang (!) Operators to access fields in recordsets, such as RS! OrderId.

As long as I know this practice, I never used it (except when I was lazy), I also did not use RS ("OrderId"), because I always (or usually) used a fully qualified method (for example, RS.fields ("OrderId "). or even extended it further using the .Item property.)

Both return exactly the same value, one shorter than the other.

The reason I stuck with this method is that once in the distant past I thought I was told that it was more complete in order to fully qualify the field, since the code had to translate each event! operator to his fully qualified sister. But! the operator reduces typing and as such dev time.

I also remind that! for ADO will be obsolete at some point in the future. But still, everything seems to be in the code, I was just wondering which method is considered best practice and which works better on another.

Answer 1

VB6 ADO . FIELD. . (, ).

Dim fMinLongitude As ADODB.Field
Dim fMinLatitude As ADODB.Field
Dim fMaxLongitude As ADODB.Field
Dim fMaxLatitude As ADODB.Field
Dim fStreetCount As ADODB.Field

If RS.RecordCount = 0 Then
    Exit Sub
End If

Set fMinLongitude = RS.Fields.Item("MinLongitude")
Set fMinLatitude = RS.Fields.Item("MinLatitude")
Set fMaxLongitude = RS.Fields.Item("MaxLongitude")
Set fMaxLatitude = RS.Fields.Item("MaxLatitude")
Set fStreetCount = RS.Fields.Item("StreetCount")

While Not RS.EOF
    LineGridCount = LineGridCount + 1
    With LineGrid(LineGridCount)
        .MinLongitude = fMinLongitude.Value
        .MaxLongitude = fMaxLongitude.Value
        .MinLatitude = fMinLatitude.Value
        .MaxLatitude = fMaxLatitude.Value
    End With
    RS.MoveNext

Wend

RS.Close
Set RS = Nothing

, 5 , SQL Server. . RS.MoveNext, .

26 000 1 . , , 0,05 . .

, WITH. , , ( ). , WITH. :

With RS.Fields
  ID = .Item(0).Value
  Name = .Item(1).Value
  EyeColor = .Item(2).Value
End With

, . , VB , .

... "less typing". , . VB6 intellisense .

RS ( " " ) 15 .
: rs (dot) f () ( ) (quote) () (Close Parenthesis) () v. 6 .

, () ( ) (quote) () ( ) () v, 17 .

, , .

. , , , , .

, , :

Private Sub Command1_Click()

    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim Results() As String

    Set DB = New ADODB.Connection
    DB.ConnectionString = "my connection string here"
    DB.CursorLocation = adUseClient
    DB.Open

    Set RS = New ADODB.Recordset
    Call RS.Open("Select * From MapStreetsPoints", DB, adOpenForwardOnly, adLockReadOnly)

    Dim Start As Single
    Dim FeatureId As Long
    Dim PointNumber As Long
    Dim Longitude As Single
    Dim Latitude As Single
    Dim fFeatureId As ADODB.Field
    Dim fPointNumber As ADODB.Field
    Dim fLongitude As ADODB.Field
    Dim fLatitude As ADODB.Field

    ReDim Results(5)

    RS.MoveFirst
    Start = Timer
    Do While Not RS.EOF
        FeatureId = RS!FeatureId
        PointNumber = RS!PointNumber
        Longitude = RS!Longitude
        Latitude = RS!Latitude
        RS.MoveNext
    Loop
    Results(0) = "Bang Method: " & Format(Timer - Start, "0.000")

    RS.MoveFirst
    Start = Timer
    Do While Not RS.EOF
        FeatureId = RS.Fields.Item("FeatureId").Value
        PointNumber = RS.Fields.Item("PointNumber").Value
        Longitude = RS.Fields.Item("Longitude").Value
        Latitude = RS.Fields.Item("Latitude").Value
        RS.MoveNext
    Loop
    Results(1) = "Fully Qualified Name Method: " & Format(Timer - Start, "0.000")

    RS.MoveFirst
    Start = Timer
    Do While Not RS.EOF
        FeatureId = RS.Fields.Item(0).Value
        PointNumber = RS.Fields.Item(1).Value
        Longitude = RS.Fields.Item(2).Value
        Latitude = RS.Fields.Item(3).Value
        RS.MoveNext
    Loop
    Results(2) = "Fully Qualified Ordinal Method: " & Format(Timer - Start, "0.000")

    RS.MoveFirst
    Start = Timer
    With RS.Fields
        Do While Not RS.EOF
            FeatureId = .Item("FeatureId").Value
            PointNumber = .Item("PointNumber").Value
            Longitude = .Item("Longitude").Value
            Latitude = .Item("Latitude").Value
            RS.MoveNext
        Loop
    End With
    Results(3) = "With Block Method: " & Format(Timer - Start, "0.000")

    RS.MoveFirst
    Start = Timer
    With RS.Fields
        Do While Not RS.EOF
            FeatureId = .Item(0).Value
            PointNumber = .Item(1).Value
            Longitude = .Item(2).Value
            Latitude = .Item(3).Value
            RS.MoveNext
        Loop
    End With
    Results(4) = "With Block Ordinal Method: " & Format(Timer - Start, "0.000")

    RS.MoveFirst
    Start = Timer
    Set fFeatureId = RS.Fields.Item("FeatureId")
    Set fPointNumber = RS.Fields.Item("PointNumber")
    Set fLatitude = RS.Fields.Item("Latitude")
    Set fLongitude = RS.Fields.Item("Longitude")
    Do While Not RS.EOF
        FeatureId = fFeatureId.Value
        PointNumber = fPointNumber.Value
        Longitude = fLongitude.Value
        Latitude = fLatitude.Value
        RS.MoveNext
    Loop
    Results(5) = "Field Method: " & Format(Timer - Start, "0.000")

    Text1.Text = "Rows = " & RS.RecordCount & vbCrLf & Join(Results, vbCrLf)

End Sub

:

Rows = 2,775,548

Bang Method: 9.441
Fully Qualified Name Method: 9.367
Fully Qualified Ordinal Method: 5.191
With Block Method: 8.527
With Block Ordinal Method: 5.117
Field Method: 4.316

, . 1/2 . , .

Answer 2

" ADO" . , Alex K, , rs(0) rs(7).

. , :

SELECT CatName, CatType, CatSize from Cats Where...

VB:

Enum enuCatsQuery
    CatName
    CatType
    CatSize
End Enum

:

StrMyName = Rs(enuCatsQuery.CatName)