Prepared Property Example (VB)

Prepared Property Example (VB)

This example demonstrates the Prepared property by opening two Command objects—one prepared and one not prepared.

Public Sub PreparedX()

    Dim cnn1 As ADODB.Connection
    Dim cmd1 As ADODB.Command
    Dim cmd2 As ADODB.Command
    Dim strCnn As String
    Dim strCmd As String
    Dim sngStart As Single
    Dim sngEnd As Single
    Dim sngNotPrepared As Single
    Dim sngPrepared As Single
    Dim intLoop As Integer

    ' Open a connection.
    strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
    Set cnn1 = New ADODB.Connection
    cnn1.Open strCnn

    ' Create two command objects for the same
    ' command - one prepared and one not prepared.
    strCmd = "SELECT title, type FROM Titles ORDER BY type"
    
    Set cmd1 = New ADODB.Command
    Set cmd1.ActiveConnection = cnn1
    cmd1.CommandText = strCmd
        
    Set cmd2 = New ADODB.Command
    Set cmd2.ActiveConnection = cnn1
    cmd2.CommandText = strCmd
    cmd2.Prepared = True
    
    ' Set a timer, then execute the unprepared
    ' command 20 times.
    sngStart = Timer
    For intLoop = 1 To 20
        cmd1.Execute
    Next intLoop
    sngEnd = Timer
    sngNotPrepared = sngEnd - sngStart
    
    ' Reset the timer, then execute the prepared
    ' command 20 times.
    sngStart = Timer
    For intLoop = 1 To 20
        cmd2.Execute
    Next intLoop
    sngEnd = Timer
    sngPrepared = sngEnd - sngStart

    ' Display performance results.
    MsgBox "Performance Results:" & vbCr & _
        "    Not Prepared: " & Format(sngNotPrepared, _
        "##0.000") & " seconds" & vbCr & _
        "    Prepared: " & Format(sngPrepared, _
        "##0.000") & " seconds"
        
    cnn1.Close

End Sub