The instance of our C_Data_ADO_MTS class will comprise the main object in the ADO data layer. The class has two public methods (ExecuteSQL & CreateLogonParameters). As its name might suggest, the ExecuteSQL method executes SQL statements against a specified OLE DB data source using the specified parameters. The CreateLogonParameters method accepts a parameter that corresponds to each of the properties of the C_LogonParameters class. It creates an instance of the C_LogonParameters class and sets the properties using the parameters passed in.
This class is also responsible for connecting to the data source, managing the MTS interaction, and returning the data in the specified format. This class has an Instancing property of MultiUse and an MTSTransactionMode property of UsesTransactions.
|
Property |
Setting |
Reasoning |
|
Instancing |
MultiUse |
This class will be the main object instantiated
in our DLL, running under MTS. |
|
MTSTransactionMode |
UsesTransactions |
We want to join MTS transactions initiated by the calling program. If the calling program was not part of a MTS transaction, then this class will also not use transactions. |
OK, so without further delay, let's jump into the code for this class.
OK, we start this class off by establishing our public enumerators. Here we have the enumDBProvider enum values, which define the different databases, are supported:
The code has only been fully tested on SQL Server, but we'll take a look a little later at where we would improve the code to handle other database connections.
Option Explicit
'-----------------------------------------------------------------------------
'--- *** You may use this code as long as you keep these comments in place. ***
'--- ORIGINALLY WRITTEN BY: Blue Sand Software, Inc.
'--- DATE WRITTEN: December 1999
'---
'-----------------------------------------------------------------------------
'--- Database provider types
Public Enum enumDBProvider
eDBProviderAccess = 1
eDBProviderSQLServer
eDBProviderOracle
End Enum
'--- Data Stream types
Public Enum enumDataStream
eDataStreamRecordset
eDataStream2DArray
eDataStreamXML
End Enum
We also define the enumDataStream enumerators. These enumerators define the different formats in which we can return the data to the calling application. For example, we may execute a query that returns all of the customers in New York. But we may want this data to be returned as XML. So we can pass one of these enumerators to the ExecuteSQL method (discussed later) and our method will evaluate the parameter and return the data in the specified enumDataStream format.
The greatest part about using enumerators here instead of constants is that we can group our valid values (enumerators) together and define a parameter as one of these valid enumerator types. This means that developers will get a drop down list of these enumerators when they type the method call into their VB code or ASP window within Visual InterDev, since intellisense handles this for us.
In the following section of code we define a constant that holds the name of our class. We'll use this for error handling in our code, as we'll see when we get to the ExecuteSQL method.
Next, since we want to build our code to run under MTS, we need to give MTS a way to communicate with our class. The mechanism to allow MTS to talk to our class is the ObjectControl interface. We use the Implements keyword to indicate that our class will also support the ObjectControl interface and all of its methods. Basically, this gives MTS a way to tell us when the class is activated and deactivated under MTS.
In our code, we are using the VB Type construct to define our class level variables. This is similar to create a Struct in C++.
Then we create a private Type that holds all of our class level variables. We only have one class level variable: oObjCtx. I have a habit of using this technique simply for the intellisense. For example, by putting all class level variables in a Type called m, I can use the intellisense feature to simply see all of my class level variables simply by typing m. This is just my personal style that I adopted from a colleague of mine and in no way is it vital to the success of the ADO data layer.
'--- Name of this object
Private Const msOBJ_NAME As String = "C_Data_ADO_MTS"
'--- MTS Object control interface
Implements ObjectControl
Private Type typeThis
oObjCtx As ObjectContext '--- MTS Object context
End Type
Private m As typeThis
So what's this ObjectContext object we have just declared? Remember how we just discussed how the ObjectControl interface is the mechanism that MTS uses to communicate with our class? Well, that interface is a one-way communication. This means that MTS can only talk to us through it, we can't talk back through the ObjectControl. But we do need to talk to MTS from our class. So to tackle this requirement, we declare the ObjectContext object. From this object, we can tell MTS important things like "Everything worked wonderfully, so save my transaction and deactivate my object instance" (a.k.a. the SetComplete method). Here is a table that describes the four methods of the ObjectContext object that we'll see throughout our code:
|
Method |
Translation |
|
SetComplete |
Everything worked wonderfully, so save my
transaction and deactivate my object instance. |
|
EnableCommit |
Everything worked wonderfully, so save my
transaction but keep my object instance activated for the time being. |
|
SetAbort |
Something did not work out, so abort my
transaction and deactivate my object instance. |
|
DisableCommit |
Something did not work out, so abort my transaction but keep my object instance activated for the time being. |
In the following code, we declare the CreateLogonParameters method. This method creates an instance of the C_LogonParameters class that we encountered earlier and accepts a handful of parameters that we'll use to set the C_LogonParameters class's properties. This method creates the instance of the C_LogonParameters class and returns it to the calling application (in our case, an ASP).
Public Function CreateLogonParameters(ByVal sUserID As String, _
ByVal sPassword As String, _
ByVal lDBProvider As enumDBProvider, _
ByVal sDataSource As String, _
ByVal sDatabaseName As String, _
ByVal lCommandTimeoutSeconds As Long) As C_LogonParameters
Const sPROC_NAME As String = msOBJ_NAME & ".LogonParameters"
On Error GoTo Err
Dim oLogonParameters As C_LogonParameters
'--- Get the logon parameters
Set oLogonParameters = New C_LogonParameters
With oLogonParameters
.DBProvider = lDBProvider
.DataSource = sDataSource
.DatabaseName = sDatabaseName
.CommandTimeoutSeconds = lCommandTimeoutSeconds
.UserID = sUserID
.Password = sPassword
End With
'--- Set return values
Set CreateLogonParameters = oLogonParameters
'--- Clean up
Set oLogonParameters = Nothing
'-------------------------------------------------------------------------
'--- Complete the MTS Transaction
'-------------------------------------------------------------------------
m.oObjCtx.EnableCommit
Exit Function
Err:
'-------------------------------------------------------------------------
'--- Abort the MTS Transaction
'-------------------------------------------------------------------------
m.oObjCtx.DisableCommit
Err.Raise Err, Err.Source & " <- " & sPROC_NAME, Error
End Function
Notice here that we used the DisableCommit method to abort the transaction instead of the SetAbort method. Both of these methods abort the transaction, however DisableCommit does not deactivate the instance of the object. If we used SetAbort instead, then when we go to call the ExecuteSQL method of this same object, an error would be raised because the object no longer exists. So the DisableCommit method is used so we can keep our instance of our object around.
The previous method was short and simple, but now we move on to the heart of the ADO data layer code. In the following code, we declare the ExecuteSQL method, which accepts a handful of parameters. First, we accept an instance of the C_LogonParameters class that we'll use to establish the connection to the underlying data source. It also accepts an sSQL parameter that carries the SQL query to execute (INSERT, UPDATE, DELETE, SELECT, etc.). Both of these parameters are mandatory.
The final parameter, lDataStreamType, is optional and must be one of the enumDataStream enumerators that we defined earlier. This parameter is used to indicate the data format that this method will return from the SQL query specified by the sSQL parameter. The default return type is an ADOR.Recordset. Also, notice that the return value of this method is a variant. We need to do this in order to have the flexibility to pass back either a string (XML), an object (ADOR.Recordset), or a variant (a 2-dimensional array).
The section of code that starts this method is in charge of establishing the connection to the data source. It sets a few of the properties of the Connection object and then opens the Connection object from the return value of the BuildConnectString method (which we will see shortly). This method puts together the connection string that we'll use to open the ADODB.Connection:
.Open BuildConnectString(oLogonParameters)
Once we have opened a connection to our data source, the code generates a Recordset on the open Connection using the sSQL parameter. Then the code disconnects the Recordset from the open Connection, so we can return a client-side Recordset, as indicated by the lDataStream parameter passed in.
Public Function ExecuteSQL(ByVal oLogonParameters As C_LogonParameters, _
ByVal sSQL As String, _
Optional ByVal lDataStreamType As _
enumDataStream = eDataStreamRecordset _
) As Variant
'--- Create a constant that we can use in the error handling routine
'--- to indicate where the error was raised from (if one ocurs).
Const sPROC_NAME As String = msOBJ_NAME & ".ExecuteSQL"
'--- Output: Recordset, 2D Array, XML or NULL
On Error GoTo Err
Dim lCol As Long
Dim oCn As ADODB.Connection
Dim oRs As ADODB.Recordset
Set oCn = CreateObject("ADODB.Connection")
'--- Open a connection
With oCn
If oLogonParameters.CommandTimeoutSeconds > 0 Then
.CommandTimeout = oLogonParameters.CommandTimeoutSeconds
End If
.IsolationLevel = adXactSerializable
.CursorLocation = adUseClient
.Open BuildConnectString(oLogonParameters)
End With
Set oRs = CreateObject("ADODB.Recordset")
With oRs
'--- Get a disconnected recordset rather then a regular recordset
'--- because of its complete meta data properties
Set .ActiveConnection = oCn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockBatchOptimistic
'--- Open the recordset
.Open Source:=sSQL, Options:=adCmdText
End With
'-------------------------------------------------------------------------
'--- Disconnect the recordset
'-------------------------------------------------------------------------
Set oRs.ActiveConnection = Nothing
'-------------------------------------------------------------------------
'--- Determine the format we will return the data in
'-------------------------------------------------------------------------
Select Case lDataStreamType
'--- ADOR.Recordset
Case eDataStreamRecordset
Set ExecuteSQL = oRs
'--- 2D Array
Case eDataStream2DArray
ExecuteSQL = oRs.GetRows(adGetRowsRest)
'--- XML
Case eDataStreamXML
ExecuteSQL = BuildXML(oRs)
End Select
'-------------------------------------------------------------------------
'--- Cleanup
'-------------------------------------------------------------------------
Set oRs = Nothing
If Not oCn Is Nothing Then
CloseObject oCn
End If
Set oCn = Nothing
'-------------------------------------------------------------------------
'--- Complete the MTS Transaction
'-------------------------------------------------------------------------
m.oObjCtx.EnableCommit
Exit Function
Err:
'-------------------------------------------------------------------------
'--- Abort the MTS Transaction
'-------------------------------------------------------------------------
Dim sError As String: sError = Err.Description
Dim sSource As String: sSource = Err.Source
Dim lErrNum As Long: lErrNum = Err.Number
On Error Resume Next
'-------------------------------------------------------------------------
'--- Cleanup
'-------------------------------------------------------------------------
Set oRs = Nothing
If Not oCn Is Nothing Then
If oCn.Errors.Count > 0 Then
sError = sError & vbCrLf & GetADOErrorInfo(oCn) & _
vbCrLf & vbCrLf & sSQL
Else
sError = sError & vbCrLf & vbCrLf & sSQL
End If
CloseObject oCn
Else
sError = sError & vbCrLf & vbCrLf & sSQL
End If
Set oCn = Nothing
'-------------------------------------------------------------------------
'--- Abort the transaction
'-------------------------------------------------------------------------
m.oObjCtx.DisableCommit
On Error GoTo 0
Err.Raise lErrNum, sSource & " <--- " & sPROC_NAME, sError
End Function
At this point, the code has created a Recordset consisting of the metadata and data requested by the sSQL statement. Next the code evaluates the lDataStreamType parameter to determine in which format to return the data. If the user omitted the parameter or specified eDataStreamRecordset, then we simply return a disconnected ADOR.Recordset from this method. The advantage of returning a Recordset is that we get all of its methods and properties along with it. This is perhaps the most feature-rich way of returning the data, if the calling application can accept it.
If the user passed in the eDataStream2Darray, then we return a two dimensional array of the Recordset's data from this method. To get the array of data, we use the Recordset object's GetRows() method. This option works well when we need to pass the data to a calling application that can not accept an ADOR.Recordset object. However, bear in mind that, with this option, we lose all of the Recordset's metadata as well and we are left with pure data. So we won't know what the names of the columns or even their datatypes. But the good news is that, because the overhead is so small, the array of data is extremely fast to marshal between applications and arrays are extremely fast to traverse, even with thousands of rows.
Notice that we pass our parameters in by value (ByVal). This means that we are passing a copy of our array parameter to the object. In a stateless environment, this is better than passing by reference (ByRef), which passes a reference to our array instead. Why is this better? For one thing, when we pass ByRef, any changes to the array in the object are reflected in its original instance back in the calling application. This could be a waste of time if it is not needed. Also, this causes more network traffic than we need. So for these reasons, we stick with passing ByVal.
The third option is to specify eDataStreamXML, which indicates that the user wants to get back a string containing XML. The XML will contain the data and the metadata generated by the SQL query. To generate the XML, we pass the disconnected Recordset to the BuildXML private method of this class. We'll take a closer look at the XML when we examine the code for this method, in a few moments.
Finally, we wrap up the code by telling MTS that everything went OK and we want it to keep our object instance around for the time being. (In other words, we issued the ObjectContext object's EnableCommit method.) We want to keep this object around in case the calling application intends to make another call to this object right away, without re-instantiating our object.
The next few code snippets are relatively simple and not the main focus, so we'll examine them at a high level, starting with the ObjectControl interface.
Since we are integrating this class with MTS and have declared that we intend to implement the ObjectControl interface, we must implement all of the methods of the interface (as shown in the following code section). The ObjectControl_Activate method is invoked by MTS when a calling application requests a C_Data_ADO_MTS object. So, since this method is telling us that the object is ready to be used, we in turn grab a reference to MTS's context using the GetObjectContext() method. This basically opens a channel of communication from our object back to MTS and we now have 2-way communication:
Private Sub ObjectControl_Activate()
Set m.oObjCtx = GetObjectContext()
End Sub
'--Called by MTS after object is deactivated
'--Our object returns True, to indicate that it wants to be pooled
Private Function ObjectControl_CanBePooled() As Boolean
ObjectControl_CanBePooled = True
End Function
'--Called by MTS just prior to deactivation
'--At which point we release the ObjectContext object from memory
Private Sub ObjectControl_Deactivate()
Set m.oObjCtx = Nothing
End Sub
The CloseObject method simply closes whatever object we pass in to it. Why go through this? Well, because we implement error handling here to ignore any errors that might be generated. Again, why is this important? Because if we attempt to close a Recordset or Connection, for example, that is already closed then an error is raised:
Private Function CloseObject(oObj As Object)
Const sPROC_NAME As String = msOBJ_NAME & ".CloseObject"
On Error Resume Next
oObj.Close
End Function
The BuildConnectString method builds the connection string for the data source specified in the C_LogonParameters object. In other words, this is where we evaluate the type of data source in order to create an appropriate connection string:
Private Function BuildConnectString( _
ByRef oLogonParameters As C_LogonParameters) As String
Const sPROC_NAME As String = msOBJ_NAME & ".BuildConnectString"
On Error GoTo Err
Dim sProvider As String
With oLogonParameters
'--- Setup the providor portion of the connect string
Select Case .DBProvider
Case eDBProviderAccess
sProvider = "Provider=Microsoft.Jet.OLEDB.1;"
Case eDBProviderSQLServer
sProvider = "Provider=SQLOLEDB.1;"
Case eDBProviderOracle
sProvider = "Provider=MSDAORA.1;"
End Select
'--- Finish crating the connect string
Select Case .DBProvider
Case eDBProviderSQLServer
If .UseSQLIntegratedSecurity Then
BuildConnectString = sProvider & _
"Persist Security Info=False;" & _
"Integrated Security=SSPI;" & _
";Initial Catalog=" & .DatabaseName & _
";Data Source=" & .DataSource
Else
BuildConnectString = sProvider & _
"Persist Security Info=False;" & _
"User ID=" & .UserID & ";Password=" & _
.Password & ";Initial Catalog=" & _
.DatabaseName & ";Data Source=" & .DataSource
End If
Case Else
BuildConnectString = sProvider & _
"Persist Security Info=False;User ID=" & _
.UserID & ";Password=" & .Password & _
";Data Source=" & .DataSource
End Select
End With
Exit Function
Err:
Err.Raise Err, Err.Source & " <--- " & sPROC_NAME, Error
End Function
The GetADOErrorInfo method accepts an ADODB.Connection and builds a string that contains all of the errors that exist in the Connection object's Errors collection:
Private Function GetADOErrorInfo(oCn As ADODB.Connection) As String
Const sPROC_NAME As String = msOBJ_NAME & ".GetADOErrorInfo"
On Error Resume Next
Dim oADOErr As ADODB.Error
Dim sError As String
If oCn Is Nothing Then Exit Function
'--- Enumerate Errors collection and display
'--- properties of each Error object.
If Not oCn Is Nothing Then
For Each oADOErr In oCn.Errors
sError = sError & "Error #" & oADOErr.Number & vbCrLf & _
" " & oADOErr.Description & vbCrLf & _
" (Source: " & oADOErr.Source & ")" & vbCrLf & _
" (SQL State: " & oADOErr.SQLState & ")" & vbCrLf & _
" (NativeError: " & oADOErr.NativeError & ")" & vbCrLF
Next
End If
GetADOErrorInfo = sError
Set oADOErr = Nothing
Exit Function
Err:
'NoOp
End Function
The BuildXML method accepts a disconnected Recordset object and passes it to the C_XML object's BuildXML method. Basically, the BuildXML method of the C_Data_ADO_MTS class is a conduit to the C_XML class, which we'll talk about next:
Private Function BuildXML(ByVal oRs As Recordset) As String
Dim oXML As BlueSand_DataLayer.C_XML
Set oXML = m.oObjCtx.CreateInstance("BlueSand_DataLayer.C_XML")
BuildXML = oXML.BuildXML(oRs)
Set oXML = Nothing
End Function