Prepared Property Example (VB)
This example demonstrates the Prepared property by opening two Command objects - one prepared and one not prepared.
'BeginPreparedVB
'To integrate this code
'replace the data source and initial catalog values
'in the connection string
Public Sub Main()
On Error GoTo ErrorHandler
Dim Cnxn As ADODB.Connection
Dim cmd1 As ADODB.Command
Dim cmd2 As ADODB.Command
Dim strCnxn 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
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn
' 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 = Cnxn
cmd1.CommandText = strCmd
Set cmd2 = New ADODB.Command
Set cmd2.ActiveConnection = Cnxn
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"
' clean up
Cnxn.Close
Set Cnxn = Nothing
Set cmd1 = Nothing
Set cmd2 = Nothing
Exit Sub
ErrorHandler:
' clean up
Set cmd1 = Nothing
Set cmd2 = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
'EndPreparedVB