How to use COM+ transactions in a Visual Basic .NET component (315707)


The information in this article applies to:

  • Microsoft Visual Basic .NET (2003)
  • Microsoft Visual Basic .NET (2002)
This article was previously published under Q315707
For a Microsoft Visual Basic 6.0 version of this article, see 261096.

IN THIS TASK

SUMMARY

This step-by-step article shows how to use COM+ (Component Services) transactions in a Visual Basic .NET class. A set of database operations is considered as one unit. The operations either all succeed, or, if one operation fails, the entire transaction fails. In the latter case, any database operations that were attempted are not posted to the underlying database.

back to the top

Requirements

The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs:
  • Microsoft Windows 2000 Server running SP-1
  • Microsoft Internet Information Services (IIS) version 4.0 or later
  • Microsoft Internet Explorer version 5.0, 5.5, or 6.0
Prior knowledge required:
  • Knowledge of transactional concepts and processing
  • Familiarity with COM+ (Component Services)
back to the top

COM+ transactional services

The System.EnterpriseServices namespace within the Microsoft .NET framework provides the functionality to implement transaction processing. To gain access to COM+ transactional services, create a class by following these steps:
  1. Open Visual Studio .NET.
  2. Create a new class library project named prjEnterprise in Visual Basic .NET. Visual Basic .NET creates a public class that is named Class1.
  3. Add a reference to the project to the System.EnterpriseServices library. In the Solution Explorer window, right-click the References folder, and then select Add Reference from the shortcut menu. On the .NET tab, scroll until you locate System.EnterpriseServices. Double-click System.EnterpriseServices, make sure that System.EnterpriseServices appears in the Selected Components window, and then click OK.
  4. Use the Imports statement for the System.EntpriseServices namespace and the System.Data.SqlClient namespace, so that you will not have to fully qualify any references to these namespaces later in your code. These statements must appear prior to any other statements in your class file:
    	Imports System.EnterpriseServices
Imports System.Data.SqlClient
  5. In order to use COM+ Transactional Services, your class, named clsES, must inherit functionality from ServicedComponent:
    	Public Class clsES
Inherits ServicedComponent
  6. A Transaction attribute is used to specify the level of transactional support for the class:
    <Transaction(TransactionOption.Required)> Public Class clsES
   	Inherits ServicedComponent
  7. Create a method within clsES called dbAccess that receives four input integer parameters. The first two parameters provide a product ID and the units on order for that product. The second two parameters provide a product ID and the units in stock for that product. This method performs a set of database operations against these specified product IDs that are to be treated as a transaction:
    	Public Sub dbAccess (ByVal pid1 As Integer, _
ByVal onOrder As Integer, _
      ByVal pid2 As Integer, ByVal inStock As Integer)
  8. In the dbAccess method, create a SQL connection object for the Northwind database, and then open the connection. Database operations take place by using this database:
    Dim Conn As New _
  SqlConnection("uid=sa;database=northwind;server=localhost")
Conn.Open()
  9. Set a Try block to capture any database processing that might occur. These errors must be caught in order to be able to abort the transaction. The Try block will include two database operations, each one updating a different field in a specified products table record.
    		Try
  10. Perform the first update to the products table. Update the UnitsonOrder field with the value of onOrder for product with ID, as specified in the first two input parameters. A SQLcommand is used to run this update SQL:
    	   Dim Cmd As SqlCommand
      Dim sqlString As String = _
           "update products set UnitsonOrder = " & onOrder _
           & " where productId = " & pid1         
      Cmd = New SqlCommand(sqlString, Conn)
      Cmd.ExecuteNonQuery()
  11. Perform another update to the products table. Update the UnitsinStock field with the value of inStock for product with ID, as specified in the third and fourth input parameters. A SQLCommand is used to run this update SQL:
          sqlString = _
"update products set UnitsinStock = " _
& inStock & " where productId = " & pid2
           
      Cmd.CommandText = sqlString
      Cmd.ExecuteNonQuery()
  12. Because these updates are part of a COM+ transaction, they are committed as a unit. The setComplete method of the contextUtil class from the System.EnterpriseServices namespace is used to commit the transaction--in this case the two updates--if no errors were thrown:
          ContextUtil.SetComplete()
  13. The connection to the Northwind database is closed:
          Conn.Close()
  14. Any errors that occur while running the SQL commands must be caught, so that the entire transaction can be aborted:
    	   Catch e As Exception
  15. The setAbort method of the contextUtil class from the System.EnterpriseServices namespace is used to abort the entire transaction. If the first update is successful and the second update fails, neither update is posted to the products table. The caught exception is thrown to the caller, indicating that the transaction failed:
          	ContextUtil.SetAbort()
			Throw e
		End Try
  16. In order for this component to function correctly, the component must have a strong name. Follow these steps to generate a strong name and to sign the assembly with the strong name:

    1. To open a Visual Studio .NET command prompt, click Start, click Programs, click Microsoft Visual Studio .NET, and then click Visual Studio .NET Tools.
    2. At the command prompt, type sn.exe -k snEnterprise.snk to give your assembly a strong name.

      For more information about signing assemblies with strong names, refer to the .NET Framework SDK documentation.
    3. Copy snEnterprise.snk to the project folder.
    4. In AssemblyInfo.vb, add the following line of code before or after other Assembly attribute statements:
      <Assembly: AssemblyKeyFileAttribute("..\..\snEnterprise.snk")>
    5. Save and build your project.
back to the top

Complete code listing

Imports System.Data.SqlClient
Imports System.EnterpriseServices

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

Public Sub dbAccess(ByVal pid1 As Integer, ByVal onOrder As Integer, _
      ByVal pid2 As Integer, ByVal inStock As Integer)

        Dim Conn As New _
          SqlConnection("uid=sa;database=northwind;server=localhost")
          Conn.Open()
        Try
          Dim Cmd As SqlCommand
          Dim sqlString As String = _
             "update products set UnitsonOrder = " & _
              onOrder & " where productId = " & pid1
          Cmd = New SqlCommand(sqlString, Conn)
          Cmd.ExecuteNonQuery()

          sqlString = _

         " update products set UnitsinStock = " & inStock & _
                " where productId = " & pid2
           Cmd.CommandText = sqlString
           Cmd.ExecuteNonQuery()

           ContextUtil.SetComplete()
           Conn.Close()
        
         Catch e As Exception
            ContextUtil.SetAbort()
		  Throw e
        End Try
    End Sub 
End Class
back to the top

Verify that it works

To test this code, create a console application that uses clsES. In one case, a transaction succeeds, and the onorder and instock fields for the specified product are updated. In the second case, the update for the onOrder field for a specified product succeeds, but the update for the inStock field for a product fails, because the specified product number does not exist in the Products table. This results in a transaction failure, with the transaction being ignored.
  1. Add a new console application named testES to your solution, so that the solution contains both the testES project and the prjEnterprise project. To add the new project, click New on the File menu, and then select Project.
  2. A New Project dialog box appears. Select the Visual Basic folder in the Project Types pane, and then select Console Application from the templates. In the Name box under the Templates pane, type testES, which is the name of the project. Make sure that the Add to Solution button, which appears at the bottom of the dialog box, is selected. Click OK to add this project to the solution.
  3. In order for testES to test clsES, you must add a reference. In the Solutions window, right-click the References folder that is under the project testES, which you just added. Select Add Reference. An Add Reference dialog box appears.
  4. Select the Projects tab, and then double-click prjEnterprise. A reference should appear in Selected Components, which is in the lower part of the dialog box. Click OK to add this reference to this project.
  5. Right-click the console application, testES, and then select Set as Startup Project.
  6. On the Debug menu, select Windows. On the submenu, select Immediate. This ensures that the Immediate window is visible.
  7. Copy the following test code into Module Module1, replacing the Sub Main and End Sub statements. The module should appear as follows:
        Sub Main()
        Dim myTest As New prjEnterprise.clsES()

        Try
            myTest.dbAccess(1, 777, 2, 888)
            Debug.WriteLine("TRANSACTION ONE -- SUCCESS")
           
            myTest.dbAccess(1, 5, 2, -20)
            Debug.WriteLine("TRANSACTION TWO -- SUCCESS")
        
        Catch
            Debug.WriteLine("TRANSACTION FAILURE")
        End Try
    End Sub
  8. Press F5 to run the test code.
  9. In the code in step 7, the first call to dbAccess succeeds. A product 1 and a product 2 are in the Products table. The onOrder field for product 1 is updated to 777, and the inStock field for product 2 is updated to 888. Because this transaction succeeded, the Immediate window should display the message "TRANSACTION ONE - SUCCESS".

    The second call to dbAccess fails. Therefore, neither one of the update statements within dbAccess to the Products table is posted to the database. Although product 1 could have its onOrder field updated to 5, product 2 cannot have its inStock field set to -20. Because of a constraint that is defined within the Product table definition, inStock is not allowed to have negative numbers.

    Consequently, this call to dbAccess fails, resulting in the entire transaction failing. The Products table remains as it was before the call to dbAccess. The Catch statement handles notification of the transaction failure from dbAccess, resulting in the message "TRANSACTION FAILURE" being displayed in the Immediate window.
Examine the contents of the Northwind Products table by using the SQL Server Enterprise Manager. When you view product 1, the onOrder field is equal to 777; for product 2, the instock field is 888. Consequently, the second call to dbAccess, which would have resulted in different values for these fields, has failed.

back to the top

Troubleshooting

  • Make sure that any project that uses COM+ services has a strong name.
  • Any class that uses COM+ services must inherit from the serviced component, which is found in the System.EnterpriseServices namespace.
  • While debugging, a transaction may time out before it is committed or aborted. To avoid a timeout, use a timeout property on the transaction attribute. In the following, the associated method gets 1,200 seconds to complete any transaction before it times out:

    <Transaction(TransactionOption.Required, timeout:=1200)>

back to the top

REFERENCES

For additional information, visit the following Microsoft Developer Network (MSDN) Web sites:

COM+ Integration: How .NET Enterprise Services Can Help You Build Distributed Applications
http://msdn.microsoft.com/msdnmag/issues/01/10/complus/

Serviced Components
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/f_and_m/html/vxconInterop.asp

back to the top
Modification Type:MajorLast Reviewed:11/8/2005
Keywords:kbHOWTOmaster KB315707 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.