HOW TO: Perform a Distributed Transaction with a .NET Provider by Using ServicedComponent in Visual Basic .NET

Article translations Article translations
Article ID: 316627 - View products that this article applies to.
This article was previously published under Q316627
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article demonstrates how to perform a distributed transaction by using a .NET provider with the ServicedComponent class. Although this article uses the SqlClient .NET provider against a Microsoft SQL Server server, you can also use the ODBC or OLE DB .NET managed provider.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server
  • Microsoft Visual Studio .NET
  • Microsoft SQL Server 7.0 or SQL Server 2000

Overview

Instances of a .NET Framework class can participate in an automatic transaction if you prepare the class to do this. Each resource that a class instance or an object accesses enlists in the transaction. For example, if an object uses ADO.NET to post money on an account in a database, the resource manager for the database determines whether the object runs in a transaction. If the object should run in a transaction, the resource manager automatically enlists the database in the transaction.

Use the following process to prepare a class to participate in an automatic transaction:
  1. Apply the TransactionAttribute class to your class to specify the automatic transaction type that the component requests.

    The transaction type must be a member of the TransactionOption enumeration. For example:
    <Transaction(TransactionOption.Required)> Public Class Bar
       Inherits ServicedComponent
       '. . .
    End Class
    					
  2. Derive your class from the ServicedComponent class. ServicedComponent is the base class of all classes that use COM+ services.
  3. Sign the assembly with a strong name to make sure that the assembly contains a unique key pair.
  4. Register the assembly that contains your class with the COM+ catalog.

    NOTE: If the client that calls an instance of your class is managed by the common language runtime, the registration is performed for you. This step is required only if an unmanaged caller creates and calls instances of your class. Use the .NET Services Installation Tool (Regsvcs.exe) to manually register the assembly.
For more information about how to sign an assembly with a strong name, see the following topic in the Microsoft .NET Framework Developer's Guide:
Signing an Assembly with a Strong Name
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconassigningassemblystrongname.asp
For more information about this process, see the following topic in the .NET Framework Developer's Guide:
Automatic Transactions and .NET Framework Classes
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconautomatictransactionsnetframeworkclasses.asp

Create the Project

  1. Follow these steps to create a new Console Application project in Visual Basic .NET:
    1. Start Visual Studio .NET.
    2. On the File menu, point to New, and then click Project.
    3. In the New Project dialog box, click Visual Basic Projects under Project Types, click Console Application under Templates, and then click OK.
    4. In Solution Explorer, rename the Module1.vb file as DistributedTransaction.vb.
  2. Delete all of the code from the DistributedTransaction.vb file.
  3. On the Project menu, click Add Reference, and then add the following references:
    • System.EnterpriseServices
    • System.Data.dll

  4. Add the following code to the DistributedTransaction.vb file:
    Imports System
    Imports System.Data.SqlClient
    Imports System.EnterpriseServices
    Imports System.Runtime.CompilerServices
    Imports System.Reflection
    
    <Assembly: ApplicationName("DistributedTransaction")> 
    <Assembly: AssemblyKeyFileAttribute("..\..\DistributedTransaction.snk")> 
    
    
    Namespace DistributedTransaction
    
        '<summary>
        'Summary description for Class1.
        '</summary>
        Module Module1
            '<summary>
            'The main entry point for the application.
            '</summary>
            <STAThread()> Sub Main()
                Try
                    Dim myDistributedTran As New DistributedTran()
                    myDistributedTran.TestDistributedTransaction()
                Catch e As System.Data.SqlClient.SqlException
                    System.Console.WriteLine("Transaction Aborted: Error returned: " + e.Message)
                End Try
            End Sub
        End Module
    
        '<summary>
        'Summary description for TestApp.
        '</summary>
        <Transaction(TransactionOption.Required)> Public Class DistributedTran
            Inherits ServicedComponent
    
            Public Sub DistributedTran()
            End Sub
    
            <AutoComplete()> Public Function TestDistributedTransaction() As String
    
    
                'The following Insert statement goes to the first server.
                'This Insert statement does not produce any errors. 
                Dim insertCmdSql As String = "Insert Into TestTransaction (Col1, Col2) Values (1,'Sql Test')"
    
                'The following Delete statement goes to the second server.
                'Because the table does not exist, this code throws an exception. 
                Dim exceptionCausingCmdSQL As String = "Delete from NonExistentTable"
    
                'The following connection strings create instances of two SqlConnection objects
                'to connect to two different SQL Server servers in your environment.
                'Modify the connection strings as necessary for your environment.
                Dim SqlConn1 As New SqlConnection("Server=Name_of_Server1;uid=User_Id;database=DatabaseName;pwd=Password")
                Dim SqlConn2 As New SqlConnection("Server=Name_of_Server2;uid=User_Id;database=DatabaseName;pwd=Password")
    
                Try
                    Dim insertCmd As New SqlCommand(insertCmdSql, SqlConn1)
                    Dim exceptionCausingCmd As New SqlCommand(exceptionCausingCmdSQL, SqlConn2)
    
                    'This command should run properly.
                    insertCmd.Connection.Open()
                    insertCmd.ExecuteNonQuery()
    
                    'This command results in an exception, which automatically rolls back
                    'the first command (the insertCmd command).
                    exceptionCausingCmd.Connection.Open()
                    Dim cmdResult As Integer = exceptionCausingCmd.ExecuteNonQuery()
    
                    SqlConn1.Close()
                    SqlConn2.Close()
    
                    Console.WriteLine("Hello")
    
                Catch ex As System.Data.SqlClient.SqlException
    
                    'After you catch the exception in this function, throw it.
                    'The service component receives this exception and aborts the
                    'transaction. The service component then throws the same exception,
                    'and the calling function receives the error message.
                    Console.WriteLine(ex.Message)
                    Throw (ex)
    
                Finally
    
                    'Close the connection.
                    If SqlConn1.State.ToString() = "Open" Then
                        SqlConn1.Close()
                    End If
    
                    If SqlConn2.State.ToString() = "Open" Then
                        SqlConn2.Close()
                    End If
    
                End Try
    
                Return "Success"
    
            End Function
    
        End Class
    End Namespace 
    					
  5. On the File menu, click Save All.
  6. Click Start, point to Programs, point to Microsoft Visual Studio .NET, point to Visual Studio .NET Tools, and then click Visual Studio .NET Command Prompt.
  7. Open the folder that contains your project, and then run the following command to sign the assembly with a strong name:
    sn -k DistributedTransaction.snk
  8. Build your application.
  9. Create the following table in the first SQL Server server:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTransaction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[TestTransaction]
    GO
    
    CREATE TABLE [dbo].[TestTransaction] (
    	[Col1] [int] NULL ,
    	[Col2] [varchar] (100) NULL 
    ) ON [PRIMARY]
    GO 
    					
  10. Run your application. Notice that you receive the following error message (which is the expected behavior):
    Transaction Aborted: Error returned: Invalid object name 'NonExistentTable'.
  11. Open the SQL Server Query Analyzer, add the following code, and then press F5 to run the query:
    USE NORTHWIND;
    SELECT * FROM TestTransaction WHERE Col1=1 AND Col2='Sql Test'
    						
    Note that the query does not return any rows because the transaction was aborted.
  12. Locate the following code in your Visual Basic project:
    Dim exceptionCausingCmdSQL As String = "Delete from NonExistentTable"
    						
    and replace the SQL statement with a valid query that does not cause the transaction to abort. For example:
    Dim exceptionCausingCmdSQL As String = "Select @@Identity from customers"
    					
  13. Press F5 to compile and to run the application again.
  14. Run the command from step 11 in Query Analyzer again. Notice that the query returns a row because the transaction was able to complete successfully.
NOTES:
  • This example does not perform error handling.
  • SQL Server and Microsoft Distributed Transaction Coordinator (MS DTC) must be running on all clients and servers.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
312902 HOW TO: Create a Serviced .NET Component in Visual Basic .NET

Properties

Article ID: 316627 - Last Review: February 28, 2014 - Revision: 2.1
APPLIES TO
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 1.1
  • Microsoft .NET Framework Service Pack 2
  • Microsoft Enterprise Services (included with the .NET Framework 1.1)
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
Keywords: 
kbnosurvey kbarchive kbcompiler kbhowtomaster kbsqlclient kbsystemdata KB316627

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com