Distributed Transactions in Visual Basic .NET

  • Article

 

Robert MacHale
MacHale.NET, Inc.

February 2002

Summary: Shows you how to use Microsoft Visual Basic .NET to create an OLTP system and includes complete code samples demonstrating the syntax for writing a transactional component and a transactional Web service in Microsoft Visual Basic .NET. (14 printed pages)

Objectives

  • Learn when to use a distributed transaction.
  • Learn how to write a transactional component in Microsoft® Visual Basic® .NET.
  • Learn how to manually complete or abort a distributed transaction in Visual Basic .NET.
  • Learn how to write a transactional Web service in Visual Basic .NET.

Assumptions

  • You are familiar with Visual Basic.
  • You have access to Microsoft Visual Studio® .NET.
  • You are familiar with basic database functionality.

Contents

Introduction to Distributed Transactions
Transactional Components in .NET
Manual Transactions
Transactional Web Services
What's Different from Visual Basic 6.0?
Summary

Introduction to Distributed Transactions

Online Transaction Processing (OLTP) systems are being installed into business organizations at an increasing rate. These systems update database records in real-time at the moment of a business transaction, like a bank ATM machine that must immediately record a cash withdrawal or a Web site for a retailer that must immediately reduce the amount of available inventory to reflect an order.

In contrast, there are Message Queue (MQ) systems that use less urgency to complete a transaction. For example, once an order is received over a Web site or by telephone, the fulfillment department for a retailer can ship the order as soon as possible. Although the amount of available inventory needs to be reduced in an accessible database, the actual order does not have to ship before the customer hangs up the telephone or leaves the Web site. The fulfillment of the order and the database records that log the shipment may be handled through a Message Queue.

This document shows you how to use Visual Basic .NET to create an OLTP system. You will find complete code samples demonstrating the syntax for writing a transactional component and a transactional Web service in Visual Basic .NET.

Transaction Defined

A transaction is any modification to any record in a database table. When you insert, update, or delete a record, it is called a transaction. On the other hand, when you select a record to read, it is not a transaction, because nothing changes in the database.

Some business rules require that a group of modifications be treated as a single transaction. For example, in accounting software, a debit and a credit must succeed or fail together because the accounts must "balance." If a debit succeeds and the credit fails, the accounts will not balance.

This kind of balanced modification collectively represents a single transaction. If an error occurs in the transaction, the entire transaction must be rolled back. Microsoft® SQL Server™, for instance, maintains a transaction log of modified records so that they can be returned to their original state if an error occurs during a transaction.

A transaction is initiated from a single program. This type of program executes a command to begin and commit (complete) a transaction. Between these two commands, the program can modify any quantity of records within the database. This kind of multiple-step modification collectively represents a single transaction.

Transactions in ADO.NET

The Connection object in ADO.NET provides the commands to begin and commit a transaction in a .NET program. This Connection object transaction includes all records that are modified between the begin and commit commands. However, the Connection object can be connected to only a single database.

For more information on ADO.NET transactions, refer to Transactions in ADO.NET in the Visual Studio .NET documentation.

Transactions in T/SQL

T/SQL provides commands that begin and commit a transaction in a SQL Server stored procedure. This kind of transaction includes all records that are modified between the two commands, begin and commit. Like the Connection object in ADO.NET, T/SQL transactions are applied to a single database.

For more information on T/SQL transactions refer to SQL Server Books Online.

Transactions in COM+ Services

If your transaction modifies records in more than one database, you will need more than ADO.NET or T/SQL to complete the job. In this case, you will need COM+ Services to help your program monitor the modifications to multiple databases. The modifications to each database collectively represent a single transaction. Your Visual Basic .NET application can define a database transaction using COM+.

For more information on COM+ Services, see the COM+ Services Primer.

Distributed Transactions Defined

A distributed transaction involves more than one database. A single distributed transaction may involve a company's inventory, customer relations management, and accounts receivable databases. For example, when an order is received on a Web site, the program may need to modify product inventory levels in one database, customer profile information in another database, and an account balance in yet another database.

In .NET, the System.EnterpriseServices assembly provides the commands to begin and commit a distributed transaction. This type of transaction includes all the records that are modified between these commands. This assembly transaction works with connections to SQL Server or other relational database servers that support distributed transactions.

Before your component can participate in a distributed transaction, it will need a strong name. This strong name is created by the Sn.exe tool outside of the IDE. You will see examples of how this works later in this article.

For more information on assemblies in the Microsoft .NET Framework, see Understanding Assemblies and the .NET Framework SDK.

Transactional Components in .NET

In this section, you will learn how to build a transactional component that is called from a form. In this example, Visual Basic .NET is used to create both the component and the form. The information you supply in the form will then be applied to the database as part of the transaction.

Sample Databases

A distributed transaction involves more than one database. The standard installation of SQL Server includes the Northwind database and the Pubs database. Each of these databases contains an Employee table.

The following example demonstrates how to create a distributed transaction to both Northwind and Pubs. The program will insert an employee record into the Employee table of both databases. Together, these inserts represent a single distributed transaction.

The steps you will follow to create a distributed transaction are summarized as follows:

  1. Create a Windows Form project.
  2. Create a component in the project.
  3. Create a form in the project.
  4. Launch the program.

To create a Windows Form project

  1. Start the .NET development environment and, on the File menu, click Project to display the New Project dialog box.
  2. In the Project Types tree view, select Visual Basic Projects.
  3. In the Templates list, select Windows Application.
  4. Click OK.

Create a Transactional Component

The code that creates the distributed transaction is written in a transactional component and is part of the same project as the form. The form calls the component when the user clicks the Save button.

  1. In Solution Explorer, right-click the project icon. On the shortcut menu, click Add, and then click Add Component.
  2. In the Name text box, type the name MyComponent.vb.
  3. Click OK.

Write the Code

Support for distributed transactions is included in the System.EnterpriseServices assembly. To add a reference to this assembly:

  1. On the Project menu, click Add Reference. The Add Reference dialog box displays a list of .NET components.
  2. Select System.EnterpriseServices.
  3. Click OK.

By default, the new component will open in design view when you add it to the project. You'll need to type the code for your component in code view. Right-click in the design view window and select View Code. Then add the following lines of code:

Imports System.Data.SqlClient
Imports System.EnterpriseServices

<Transaction(TransactionOption.Required)> _
Public Class MyComponent
    Inherits ServicedComponent

    <AutoComplete()> _
    Function AddEmployee( _
ByVal strFirst As String, _
ByVal strLast As String, _
ByVal strPubsId As String) As String
        'Insert new employee into Northwind database
        Dim strDB1 As String = "server=127.0.0.1;" &_
"user id=sa;" & _
"password=;" & _
"initial catalog=northwind"
        Dim conDB1 As SqlConnection
        Dim cmdDB1 As SqlCommand
        Dim strSQL1 As String = "insert into employees " & _
"(firstname, lastname) " & _
"values " & _
"('" & strFirst & "', '" & strLast & "');"

        conDB1 = New SqlConnection(strDB1)
        conDB1.Open()
        cmdDB1 = New SqlCommand(strSQL1, conDB1)
        cmdDB1.ExecuteNonQuery()

        'Insert new employee into Pubs database
        Dim strDB2 As String = "server=127.0.0.1;" & _
"user id=sa;" & _
"password=;" & _
"initial catalog=pubs"
        Dim conDB2 As SqlConnection
        Dim cmdDB2 As SqlCommand
        Dim strSQL2 As String = "insert into employee " & _
"(emp_id, fname, lname) " & _
"values ('" & strPubsId & "', '" & strFirst & "', '" & strLast & "');"

        conDB2 = New SqlConnection(strDB2)
        conDB2.Open()
        cmdDB2 = New SqlCommand(strSQL2, conDB2)
        cmdDB2.ExecuteNonQuery()

        AddEmployee = "OK"
    End Function

End Class

As you study this code, you will find three lines that are distinctly different from non-transactional code. First, the System.EnterpriseServices assembly is required to produce a distributed transaction. Second, there is a new attribute on the class definition:

<Transaction(TransactionOption.Required)> _
Public Class MyComponent
    Inherits ServicedComponent

The Transaction attribute makes this bit of code into a transactional component. A transaction begins any time this method is called. The transactional components are inherited from ServicedComponent.

A transaction automatically ends when the function ends if the following attribute is supplied with the function definition:

    <AutoComplete()> _
Function AddEmployee( _
ByVal strFirst As String, _
ByVal strLast As String, _
ByVal strPubsId As String) As String

The AutoComplete attribute before the Function keyword makes this an automatic transaction. Later in this document, you will see a comparison between an automatic and a manual transaction. Now that the logic in the transactional component is complete, you need to create a Strong Name for the component.

Create a Strong Name

Before the component can be serviced by COM+ Services, it must have a strong name. The strong name contains a public/private key pair that you generate through a command line utility named Sn.exe. This public/private key pair enables the strong name to uniquely identify the object. Once you locate this executable file in your .NET installation folder, you can create the strong name from the Microsoft Windows® Command Prompt like this:

sn.exe –k c:\MyComponent.snk

Run this tool before you compile your component. The .snk file it creates contains the strong name key pair your component will need to operate within the context of COM+ Services. After you create this file, you can add it to your .NET project.

  1. To add the .snk file to the project, in the Solution Explorer window, right-click the project icon. On the shortcut menu, click Add, and then click Add Existing Item.

  2. In the Add Existing Item dialog box, select your .snk file and then click Open.

  3. In the Solution Explorer window, select the AssemblyInfo.vb file and double-click it to open it in the editor.

  4. Add the following line to the AssemblyInfo.vb file

    <Assembly: AssemblyKeyFile("..\..\MyComponent.snk")>

For more information on strong names in .NET, refer to the .NET Framework SDK.

Create a Windows Form

The development environment presents a blank form when you create a Windows Application project.

  1. Create a Windows Application project and open the blank form in design view.

  2. Add three text boxes and a button and name them as follows:

    • txtFirst
    • txtLast
    • txtPubsEmpId
    • btnSave

    This form will look like the one shown in Figure 1:

    Figure 1. A sample Windows Form

  3. Double-click the button to add the code behind the button.

    Public Class frmEmployee
    Inherits System.Windows.Forms.Form

    Private Sub btnSave_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnSave.Click

        Dim objEmployee As MyComponent
        objEmployee = New MyComponent()
        Try
            MsgBox(objEmployee.AddEmployee(_
txtFirst.Text, _
txtLast.Text, _
txtPubsId.Text)_
)
        Catch ex As Exception
            MsgBox(Ex.Message)
        End Try
    End Sub

End Class

When the user clicks the Save button, this subroutine executes. First, the program instantiates the MyComponent object created earlier, and then the AddEmployee method gets called, passing the values typed into the form.

If no errors occur within the AddEmployee method, the new employee record is inserted into both database tables. If an exception occurs within the component, the records are rolled back and an exception is thrown in the form. The Try/Catch block within the form handles the exception and displays an error message.

For more information on the Try/Catch block and exception handling, see Using the Try/Catch Block to Catch Exceptions in the .NET Framework SDK.

Launch the Program

At this point, the component and form are complete.

  1. Press F5 to launch the program in the debugger. The form will open on the screen.
  2. Type in a new employee name and click Save. The new employee record will be inserted into both the Northwind database and the Pubs database. If an exception occurs, an error message will display.

Manual Transactions

When you place the AutoComplete() attribute on your Function definition, COM+ Services automatically commits your database modifications after the function completes.

    <AutoComplete()> _
    Function AddEmployee(ByVal strFirst As String, _
 ByVal strLast As String, ByVal strPubsId As String) As String

The database modifications will only be rolled back if an exception occurs during the process. At the point where the exception occurs, the database modifications are automatically rolled back.

Exception Handling

If you place a Try/Catch block inside the transactional component, your code can manually commit or rollback the transaction.

Imports System.Data.SqlClient
Imports System.EnterpriseServices

<Transaction(TransactionOption.Required)> _
Public Class MyComponent
    Inherits ServicedComponent

    Function AddEmployee(_
ByVal strFirst As String, _
ByVal strLast As String, _
ByVal strPubsId As String_
) As String
        Try
            'Insert new employee into Northwind database
            Dim strDB1 As String = "server=127.0.0.1;" _ &
"user id=sa;" & _
"password=;" &
"initial catalog=northwind"
            Dim conDB1 As SqlConnection
            Dim cmdDB1 As SqlCommand
            Dim strSQL1 As String = "insert into employees " & _
"(firstname, lastname) " & _
"values " & _
"('" & strFirst & "', '" & strLast & "');"

            conDB1 = New SqlConnection(strDB1)
            conDB1.Open()
            cmdDB1 = New SqlCommand(strSQL1, conDB1)
            cmdDB1.ExecuteNonQuery()

            'Insert new employee into Pubs database
            Dim strDB2 As String = "server=127.0.0.1;" & _
"user id=sa;" & _
"password=;" & _
"initial catalog=pubs"
            Dim conDB2 As SqlConnection
            Dim cmdDB2 As SqlCommand
            Dim strSQL2 As String = "insert into employee " & _
"(emp_id, fname, lname) " & _
"values " & _
"('" & strPubsId & "', '" & strFirst & "', '" & strLast & "');"

            conDB2 = New SqlConnection(strDB2)
            conDB2.Open()
            cmdDB2 = New SqlCommand(strSQL2, conDB2)
            cmdDB2.ExecuteNonQuery()

            ContextUtil.SetComplete()
            AddEmployee = "Commit"
        Catch
            ContextUtil.SetAbort()
            AddEmployee = "Rollback: " & Err().Description
        End Try

    End Function

End Class

By removing the <AutoComplete()> attribute, your program no longer relies on the automatic transaction services of COM+ Services. Your program can manually commit or rollback the transaction on its own. This option provides two advantages: scrutiny of return values to determine transaction success, and customized rollback criteria.

Your component can manually manage exceptions gracefully. When you remove the <AutoComplete()> attribute from your function, you add exception handling to the function. When you add exception handling to your component, it can handle database exceptions internally. This is more elegant than throwing the exception back to the form that called it because the form can look at the function return value to determine the success of the transaction.

Another advantage to using a manual transaction is that you can determine to rollback a transaction based on criteria external to database errors. Your business rule may determine that a transaction is rolled back rather than using a database rule. This gives your program more control.

ContextUtil

The ContextUtil object allows your program to interact with COM+ Services. COM+ Services is managing the distributed transaction and waiting for the signal to commit or rollback the transaction. The SetComplete or SetAbort method of the ContextUtil object is called by the program at the end of the transaction.

SetComplete

At the end of your function, you can commit all the database modifications through the SetComplete() method of the ContextUtil object.

            ContextUtil.SetComplete()

This gives the signal to commit the modifications to all databases involved in the transaction. Your component executes this method when all business rules and database rules are met.

SetAbort

At the end of your function, you can roll back all the database modifications through the SetAbort() method of the ContextUtil object.

            ContextUtil.SetAbort()

This gives the signal to roll back the modifications to all databases involved with the transaction. Your component executes this method when a business or database rule is not met.

Transactional Web Services

One of the new project types in .NET is Web services. A Web service is essentially a function that gets invoked remotely over the Internet. Typically, a transactional component runs on an application server; client computers invoke the transaction by remotely invoking a transactional component.

Let's examine how to create a transactional Web service. Because a Web service is fundamentally different from a Windows application, you will need to create a separate project to create these examples. The Web Service project type requires a Web server, such as Microsoft Internet Information Services.

  1. In the .NET development environment, on the File menu, click New, and then click Project.
  2. From the Project Types tree view, select Visual Basic Projects.
  3. From the Templates list, select Web Service.
  4. In the Name text box type MyWebService.
  5. Click OK.

The Web service initially opens in design view. Toggle to code view to add your .NET program code. Then, right-click the design window and select Code View to add your code.

Imports System.Data.SqlClient
Imports System.EnterpriseServices

<WebServiceAttribute(_
Namespace:=http://www.machale.com/msdn/MyWebService _
)> _
Public Class MyWebService

   <WebMethod(TransactionOption:=
TransactionOption.Required)> _
Public Function AddEmployee( _
ByVal id As String, _
ByVal first As String, _
ByVal last As String) As String        

      Try
         Dim strDB1 As String = "server=127.0.0.1;" & _
"user id=sa;" & _
"password=;" & _
"initial catalog=northwind"
         Dim conDB1 As SqlConnection
         Dim cmdDB1 As SqlCommand
         Dim strSQL1 As String = "insert into employees " & _
"(firstname, lastname) " & _
"values " & _
"('" & first & "', '" & last & "');"

         conDB1 = New SqlConnection(strDB1)
         conDB1.Open()
         cmdDB1 = New SqlCommand(strSQL1, conDB1)
         cmdDB1.ExecuteNonQuery()

         Dim strDB2 As String = "server=127.0.0.1;" & _
"user id=sa;" & _
"password=;" & _
"initial catalog=pubs"
         Dim conDB2 As SqlConnection
         Dim cmdDB2 As SqlCommand
         Dim strSQL2 As String = "insert into employee " & _
"(emp_id, fname, lname) " & _
"values ('" & id & "', '" & first & "', '" & last & "');"

         conDB2 = New SqlConnection(strDB2)
         conDB2.Open()
         cmdDB2 = New SqlCommand(strSQL2, conDB2)
         cmdDB2.ExecuteNonQuery()

         AddEmployee = "Commit"
      Catch
         AddEmployee = "Rollback: " & Err().Description
      End Try
   End Function
End Class

This function is nearly identical to the transactional component example. The main difference is found in the Function definition. The WebMethod() attribute is used to set the transaction option. The remaining code is functionally identical.

   <WebMethod(TransactionOption:=
TransactionOption.Required)> _
Public Function AddEmployee( _
ByVal id As String, _
ByVal first As String, _
ByVal last As String) As String

Press F5 to launch the Web service in the debugger. A sample Web page will prompt you to supply the values for the function parameters. The return value of the function will be in the form of a well-formed XML document.

For more information, see XML Document Creation in the .NET Framework SDK and XML Web Services Basics.

What's Different from Visual Basic 6.0?

There are many shared concepts between transactions in Visual Basic .NET and in Visual Basic 6.0. It's the syntax that is often different. For instance, to create a transactional component in Visual Basic 6.0, you set the appropriate property on the class module. In Visual Basic .NET, you type the transaction mode attribute on the function declaration. Both rely on COM+ to monitor the transaction. The syntax for committing a transaction is also different in .NET; use the SetComplete() method of the ContextUtil object.

Summary

Online Transaction Processing (OLTP) systems require transactions that complete at the same moment as a business transaction. Transactions can be written in ADO.NET or T/SQL when a single database is modified. However, if multiple databases are modified during a transaction, COM+ Services are required to manage the transaction.

A distributed transaction is a transaction that involves multiple databases. Using .NET, you can write a distributed transaction as a transactional component or a transactional Web service. A transactional component is called within a .NET program such as a Windows Form.

Your program can commit a transaction automatically or manually. Using the ContextUtil object in conjunction with the SetComplete() and SetAbort() methods, your program can manually control the transaction. When used with exception handling, the manual approach provides a more elegant solution than automation.

.NET provides a rich set of tools for building enterprise applications. The support for distributed transactions is one of many features included to make this a great platform for development.

About the Author

Robert MacHale is a trainer, author, and consultant. His company, MacHale.NET, Inc., specializes in DHTML/XML applications. Robert is a leading HTML component (HTC) developer utilizing DHTML/XML/XSLT to build rich client applications for Microsoft Internet Explorer. He trains students on ASP, Microsoft® SQL Server, XML, and .NET for AppDev Training (www.appdev.com). He has authored numerous training videos for Keystone Learning Systems (www.keystonelearning.com).

About Informant Communications Group

Informant Communications Group, Inc. (www.informant.com) is a diversified media company focused on the information technology sector. Specializing in software development publications, conferences, catalog publishing and Web sites, ICG was founded in 1990. With offices in the United States and the United Kingdom, ICG has served as a respected media and marketing content integrator, satisfying the burgeoning appetite of IT professionals for quality technical information.

Copyright © 2002 Informant Communications Group and Microsoft Corporation

Technical editing: PDSA, Inc.