Using Visual Basic 5.0 to Build Microsoft Transaction Server Components

The database application components you build with Microsoft Visual Basic for Microsoft Transaction Server and SQL Server model the activity of your organization's business. These components implement business rules, provide views, and transform the state of an application. For example, bank records in one or more database systems represent the durable state of the business, such as the amount of money in an account. The application components update that state to reflect changes, such as debits and credits.

Microsoft Transaction Server application components are COM in-process server components (DLLs). You can create and implement these components with Visual Basic as well as Visual C++, Visual J++, or any ActiveX-compatible development tool.

Transaction Server shelters you from complex server issues, allowing you to focus on implementing business functions. Because components running under Transaction Server can take advantage of transactions, you can write applications as if they run in isolation. Transaction Server handles concurrency, resource pooling, security, context management, and other system-level complexities. The transaction system, working in cooperation with database servers and other types of resource managers, ensures that concurrent transactions are atomic, consistent, have proper isolation, and, once committed, are durable.

To help you learn to create Microsoft Transaction Server components, this section will show you how to build a component using SQL Server and Visual Basic. You build the component by creating a project with a class module that exposes a method. Database connectivity is handled by the Microsoft Transaction Server ODBC resource dispenser, which provides automatic connection pooling through Microsoft Transaction Server.

When you create a database application, each time the application is used, it obtains, uses, and then releases its database connection. A database connection is a valuable resource. One of the most efficient models for resource usage in scalable applications is to use connections sparingly, acquire them only when you really need them, and return them as soon as possible.

Microsoft Transaction Server provides resource sharing through its Resource Dispenser Manager and resource dispensers. The Resource Dispenser Manager works with specific resource dispensers to automatically pool and recycle resources. The ODBC 3.0 Driver Manager is a Microsoft Transaction Server resource dispenser, also referred to as the ODBC resource dispenser.

You do not need to implement any Transaction Server-specific APIs to begin using the ODBC resource dispenser. When you use Remote Data Objects (RDO) to access the database, RDO in turn uses ODBC. Whenever any component running in the Transaction Server run-time environment uses ODBC directly or indirectly, the component automatically uses the ODBC resource dispenser. Microsoft Transaction Server is aware of your database connectivity when you register your component with Microsoft Transaction Server Explorer.

When an object in your application releases the database connection, the connection is returned to a pool. When the object is called again, it requests the same database connection. Instead of creating a new connection, the ODBC resource dispenser recycles the pooled connection, which saves time and server resources.

For more information about creating Visual Basic/SQL Server applications, see Hitchhiker's Guide to Visual Basic and SQL Server, Fifth Edition by Bill Vaughn.

Before you start coding, you should have an ODBC data source configured to access SQL Server:

In Control Panel, click ODBC.
Click Add, and then select SQL Server. Click Finish.
Click Options.
Specify the data source name as MTxSamples and the Server as (local). Enter the default database to use for the Account table (pubs for example).
Make sure that Microsoft SQL Server is running. In the Server box of SQL Service Manager, click a SQL Server. If the spotlight is green, then that SQL Server is running; if not, double-click Start/Continue.
In Visual Basic, start a new project as an ActiveX DLL. After you have initiated the project, you can write Visual Basic code.
This is a server component and has no user interface. You access a database using Remote Data Objects (RDO) and enable the ODBC resource dispenser to pool connections.

Code Sample

Your code should follow the simplified format below.

Set public functions.

Check to see if the RDO connection is valid:

if rdoConnection throws an exception
On Error GoTo ErrorHandler

' obtain the RDO environment and connection
Dim rdoConn As rdoConnection
Set rdoConn = rdoEngine.rdoEnvironments(0).OpenConnection("", rdDriverNoPrompt, 
False, strConnect)

(Error handling is typically placed at the end of code; it is here for clarity.)

ErrorHandler:

If Not rdoConn Is Nothing Then
    rdoConn.Close
End If

strResult = Err.Description     'return the error message and indicate that 
an error occurred to your component
End Function

Write SQL logic for accessing SQL Server tables:

Dim strSQL As String
strSQL = SQL logic goes here…

Enter RDO connection parameters:

rdoConn.Execute strSQL, Options:=rdExecDirect

' if anything else happens
On Error GoTo ErrorHandler

If no errors, then continue processing:

strSQL = SQL logic goes here…
Dim rdoRS As rdoResultset
Set rdoRS = rdoConn.OpenResultset(strSQL, Option:=rdExecDirect)
If rdoRS.EOF <> True Then

Application logic goes here:

rdoConn.Close

Close database connections so MTS ODBC resource dispenser can reallocate as required:

Exit Function

As you can see, even existing Visual Basic code can be easily managed using Transaction Server because RDO uses ODBC and ODBC is managed by MTS at run time. Only DLLs can be registered with Microsoft Transaction Server—you must rebuild executables as DLLs first.

After writing code, build the component as a DLL and save it. You will have to use Microsoft Transaction Server Explorer to register the component with the MTS run-time environment. For more information about how to monitor transactions of an application during testing, see "Microsoft Transaction Server Explorer," later in this paper.

Each time you change a Microsoft Transaction Server component, the registry must be updated with the new component information. The Development version of Microsoft Transaction Server includes a Visual Basic 4.0–compatible add-in that automates this process (click VB Addin during Setup). The next time you run Visual Basic 4.0, the add-in is installed in Visual Basic. The add-in refreshes all of your Transaction Server component DLLs whenever you recompile your project.

You can set this feature on a per-project basis by toggling the command on the Visual Basic Add-Ins menu. After a compile, on the Visual Basic Add-Ins menu, point to MTxServer RegRefresh, and then click AutoRefresh. A check mark indicates that the feature is enabled. To refresh all Transaction Server components at any given time, on the Visual Basic Add-Ins menu, point to MTxServer RegRefresh, and then click Refresh all components now.

Improving Scalability

There are several ways to optimize Visual Basic code for MTS and SQL Server. Traditionally, objects have been handled in a couple ways:

A client can create, use, and release an object. The next time it needs the object, it creates it again. This technique conserves server resources. However, as an application scales up, performance slows down. If an object is on a remote computer, a network roundtrip occurs each time an object is created; this negatively affects performance.
A client can create an object and hold onto it until the client no longer needs it; this approach is faster. However, in a large-scale application, it quickly becomes expensive in terms of server resources.

While either of these approaches might be fine for a small-scale application, as an application scales up, they both become inefficient. Just-in-time activation provides the best of both approaches, while avoiding the disadvantages of each. It uses server resources efficiently without requiring changes to the client.

By adding a few lines of code, you can implement just-in-time activation. When an object calls SetComplete, it notifies the Transaction Server run-time environment that it should be deactivated as soon as it returns control to the client. This allows the Transaction Server run-time environment to release object resources, including any database connections held, prior to the release of the object. The client continues to hold a reference to the deactivated object. When a client calls a method on a deactivated object, the reference is automatically bound to a new object. Thus, the client has the illusion of a continuous reference to a single object, without tying up server resources unnecessarily.

Implementing Just-in-Time Activation

Every Transaction Server object has a context object associated with it. The context object contains information about the execution environment of the object, such as the identity of the object creator and, optionally, the transaction encompassing the work of the object. An object context is similar in concept to the process context that an operating system maintains for an executing program. The Microsoft Transaction Server run-time environment manages a context for each object. A context object is created when an object is created. You can use a context to declare when the work of the object is complete. The Transaction Server run-time environment can safely deactivate an object, making its resources available for reuse while supporting a greater number of concurrent users.

To implement just-in-time activation:

Call GetObjectContext to get a reference to the context object:

Dim ctxNAME As ObjectContext
Set ctxObject = GetObjectContext()

Call SetComplete when an object has completed its work successfully:
```
ctxObject.SetComplete
```
SetComplete notifies the Transaction Server run-time environment that the object (NAME in this case), should be deactivated as soon as it returns control to the client.

Call SetAbort when an object has not completed its work successfully.

SetAbort notifies the Transaction Server run-time environment that the object should be deactivated as soon as it returns control to the client. ctxObject.SetAbort would be set in the ErrorHandler division.