Recall these two lines from the topic Hello World Public Class CHelloWorld:
objHelloData.ConnectionString = ConnectionString
strPhrase = objHelloData.GetPhrase(strLang)
Between these two lines of code, the application executes the code contained in CHelloData.cls. First, ConnectionString is declared as a String variable. Then, the ConnectionString's value is assigned the string "DSN=HelloWorld." The application uses this string to establish the connection to the SQL Server database in which the table of Hello World phrases is located.
Option Explicit
'=== PUBLIC PROPERTIES
' These properties can be modified directly by the caller
Public ConnectionString As String
Private Sub Class_Initialize()
ConnectionString = "DSN=HelloWorld"
End Sub
The next fragment of code executes the GetPhrase method of the HelloData class. First, it takes the language code. Then it declares and instantiates ADO Command and Parameters objects. The ADO objects cross the boundary from the business-services tier into the data-services tier.
Public Function GetPhrase(ByVal LanguageCode As String) As String
Dim oc As MTxAS.ObjectContext
Dim cmd As ADODB.Command
Dim params As ADODB.Parameters
On Error GoTo ErrHandler
Set oc = GetObjectContext
Set cmd = oc.CreateInstance("ADODB.Command")
In order to create the ADODB.Command object within the same Microsoft Transaction Server (MTS) context as the CHelloWorld class, we need to instantiate it using the CreateInstance method of the MTS ObjectContext object. The CreateInstance method ensures that external objects are managed by MTS, not COM alone.
The next lines of code set the properties of the ADO Command object that execute the SQL Server stored procedure, sp_helloworld. The stored procedure executes the actual SQL query that retrieves from the phrases table the string that matches the ISO 639-1 code.
Note In this code, the developer uses the Visual Basic With keyword to eliminate the need to type "cmd" repeatedly. This remains in effect until the End With statement.
With cmd
.ActiveConnection = ConnectionString
.CommandTimeout = 90
.CommandText = "sp_helloworld"
.CommandType = adCmdStoredProc
The stored procedure requires a number of arguments to do its work. The application uses the ADO Parameters object to set them, and then calls the Execute method on the Command object.
'Pass Arguments to stored procedure
Set params = .Parameters
params.Append .CreateParameter("@iso3691", adChar, adParamInput, 8, LanguageCode)
params.Append .CreateParameter("@phrase", adVarChar, adParamOutput, 255)
'Execute the command
.Execute
End With
Successful execution of the stored procedure results in a return value that can begin its trip back to the calling object and ultimately to the client's browser. After the application retrieves the phrase, it destroys the ADO Command object. If everything succeeds, application execution now returns to the HelloWorld object. (Note that we have avoided a Type Mismatch error by concatenating the possibly null return value with an empty string.)
'Return value (add empty string in case of NULL)
GetPhrase = params("@phrase") & ""
'Release the memory
Set cmd = Nothing
Set oc = Nothing
Exit Function
ErrHandler:
' NOTE: This call will cause the component to halt execution
' and return an error to the caller.
Err.Raise Err.Number, Err.Source, Err.Description
End Function
Return now to the previous topic to read about the Code Executed After HelloData Returns.