Using the File Manager, create a new directory named \MTS. Beneath this new directory, create a directory named SERVER.
To start your new Visual Basic ActiveX DLL project, choose References from the Project menu and set references to the Microsoft Remote Data Object 2.0 component and the Microsoft Transaction Server 1.0 Type Library.
Change the properties of Project1 by choosing Project1 Properties from the Project menu. In the Project Properties dialog box, change the name of the project to MTSObject and change the description to MTS Training Exercise Objects. Click OK.
In this part of the project, you will use a single class to query the NorthWind data source and return records and you will learn custom marshaling techniques to return data from a lookup table and records from a query.
Change the name of the Class1 class to Query.
Save the project in the \MTS\Server directory.
Create a new method in the Query class by choosing Add Procedure from the Tools menu. In the Add Procedure dialog box, set the following attributes:
Name: GetCategories Type: Function Scope: Public
After inserting the new function, modify it to return a String data type. The complete function should now look like this:
Public Function GetCategories() As String End Function
The GetCategories method returns a delimited string with all the category names. This technique is a quick way to return lookup table information to a list.
Add the following code to the GetCategories method to read the category names from the data source and format the return string:
On Error GoTo GetCategoriesErr ` Get object context Dim objContext As MTxAS.ObjectContext Set objContext = GetObjectContext() Dim objConnection As RDO.rdoConnection Dim objResultset As RDO.rdoResultset ` Make connection. ` NOTE: Connections made to MTS must ` be made with the rdDriverNoPrompt option! Set objConnection = New RDO.rdoConnection objConnection.Connect = "DSN=NorthWind" objConnection.EstablishConnection rdDriverNoPrompt, True ` Run query Set objResultset = objConnection.OpenResultset _ ("SELECT CategoryName FROM Categories") ` Build return string Dim strReturn As String strReturn = "" Do While Not objResultset.EOF strReturn = strReturn & objResultset("CategoryName") & "|" objResultset.MoveNext Loop ` Close connection to allow pooling objResultset.Close objConnection.Close Set objResultset = Nothing Set objConnection = Nothing ` Tell MTS we are done objContext.SetComplete GetCategoriesExit: GetCategories = strReturn Exit Function GetCategoriesErr: strReturn = Err.Description ` Tell MTS we failed objContext.SetAbort Resume GetCategoriesExit
After the category names are returned to the front end, the user can select a category and see the associated products from the data source for that category. This is accomplished with a method named GetProducts, which returns all the products in an array to the client. The client then uses the array to fill a grid.
Add the GetProduct method by choosing Add Procedure from the Tools menu. In the Add Procedure dialog box, set the following attributes:
Name: GetProducts Type: Function Scope: Public
Change the GetProducts function to accept two arguments and to return a Boolean value that indicates success or failure. The resulting function should look like this:
Public Function GetProducts(strCategory As String, _ ByRef arrProducts As Variant) As Boolean End Function
GetProducts uses the category to search for products and fill an array with results. It also uses Just-In-Time activation to ensure that instances of the Query class are recycled as soon as possible. Add the following code to the GetProducts function to return an array of products to the client:
On Error GoTo GetProductsErr ' Get object context Dim objContext As MTxAS.ObjectContext Set objContext = GetObjectContext() ' Declare database objects Dim objConnection As RDO.rdoConnection Dim objResultset As RDO.rdoResultset ' Make connection Set objConnection = New RDO.rdoConnection objConnection.Connect = "DSN=NorthWind" objConnection.EstablishConnection rdDriverNoPrompt, True ' Run query Dim strSQL As String strSQL = "SELECT ProductName,CompanyName,UnitPrice " strSQL = strSQL & "FROM Products,Suppliers,Categories " strSQL = strSQL & "WHERE Products.SupplierID=Suppliers.SupplierID " strSQL = strSQL & "AND Products.CategoryID=Categories.CategoryID " strSQL = strSQL & "AND Categories.CategoryName='" & strCategory & "'" Set objResultset = objConnection.OpenResultset(strSQL) ' Build return array arrProducts = objResultset.GetRows(100) ' Close connection to allow pooling objResultset.Close objConnection.Close Set objResultset = Nothing Set objConnection = Nothing ' Tell MTS we are done GetProducts = True objContext.SetComplete GetProductsExit: Exit Function GetProductsErr: ' Tell MTS we failed GetProducts = False objContext.SetAbort Resume GetProductsExit
Now that the business object is complete, you can compile it by choosing Make MTSObject.dll from the File menu. When the DLL is created, save your work and exit Visual Basic.