<%@ Page Language="C#" MasterPageFile="~/howto/howto.master" %> <%@ Register TagPrefix=Acme Namespace=Acme %> <%@ Register TagPrefix="Acme" TagName="SourceRef" Src="~/util/SrcRef.ascx"%>

ADO.NET: Use Distributed Transactions



This sample illustrates how to use distributed transactions, which are available automatically from the Microsoft .NET Framework component services. Microsoft Transaction Server (MTS), Microsoft .NET Framework SDK, and the common language runtime support the same automatic, distributed transaction model. To use this feature, complete the following steps to provide for automatic distributed transactions to your data source.

  1. Use System.EnterpriseServices.
  2. Create a ServicedComponent to provide automatic transaction support.
  3. Add some features to the ServicedComponent.
  4. Write code to execute (distributed) transactions on the ServicedComponent.


One major advantage of the automatic distributed transaction programming model (Windows 2000 Component Services) is that data stores (the Resource Managers) are automatically enlisted in transactions. When the transaction group is to be completed, call SetComplete or if it is not to be completed call SetAbort. These distributed transactions can span multiple databases. ADO.NET providers (ADO Interop Provider and SQL Server Provider) automatically enlist in Component Services transactions that initiated using ComponentServices.

Any instance of a .NET Framework class can participate in an automatic transaction, as long as you prepare the class to do so. Each resource accessed by a class instance or object 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 if it should execute in a transaction and, if so, it enlists the database in the transaction automatically.

First, make sure you are using System.EnterpriseServices.

using System.EnterpriseServices; Imports System.EnterpriseServices using namespace System::EnterpriseServices;

Next, create a derived class of ServicedComponent for ComServices to provide automatic transaction support.

A serviced component is a .NET Framework component that derives from the System.ServicedComponent class and is automatically serviced by Microsoft Windows 2000 Component Services. A serviced component is typically hosted in a Microsoft .NET Framework library as an in-process server or as an out-of-process server that is called by an unmanaged client.

To prepare a class for using the transaction model, the class must be marked to participate in a transaction. Once the class is marked with a Transaction attribute it will automatically execute within the scope of a transaction. You can control an object's transactional behavior by setting a transaction attribute value on the page, webmethod, or class. The attribute value, in turn, determines the transactional behavior of the object that is created.

In the case of class myServicedComponent 'TransactionOption.Required' indicates that the object requires a transaction. It runs in the scope of an existing transaction, if one exists. If not, it will start one. [TransactionAttribute(TransactionOption.Required)] public class myServicedComponent:ServicedComponent { ... } public class TransactionAttribute<(TransactionOption.Required)> myServicedComponent : Inherits ServicedComponent ... end class [TransactionAttribute(TransactionOption::Required)] public __gc class myServicedComponent : public ServicedComponent { ... }

The next step is to add some features to the derived class of the ServicedComponent.

You can use System.EnterpriseServices.ContextUtil, which exposes the SetComplete or SetAbort members, to explicitly commit or abort a transaction. SetComplete indicates that your object votes to commit its work; SetAbort indicates that your object encountered a problem and votes to abort the ongoing transaction. A transaction is neither committed nor aborted until it reaches the end of the page. Further, a single abort vote from any object participating in the transaction causes the entire transaction to fail. ... private SqlConnection m_rConnection; public myServicedComponent() { } public SqlConnection SqlConn { get { return m_rConnection; } } public ConnectionState State { get { return m_rConnection.State; } } public void myServicedComponentConnect(string ConnectionString) { m_rConnection = new SqlConnection(ConnectionString); m_rConnection.Open(); } public void myServicedComponentClose() { if (m_rConnection != null) m_rConnection.Close(); } public void myServicedComponentDispose() { if (m_rConnection != null) m_rConnection.Dispose(); } public SqlCommand myServicedComponentCreateCommand() { return new SqlCommand(null, m_rConnection); } public void myServicedComponentSetComplete() // to commit changes { Console.WriteLine("Committing connection's transaction."); ContextUtil.SetComplete(); Console.WriteLine("SetComplete"); } public void myServicedComponentSetAbort() // to rollback the distributed transaction { Console.WriteLine("Aborting connection's transaction."); ContextUtil.SetAbort(); Console.WriteLine("SetAbort"); } ... ... private m_rConnection as SqlConnection public sub myServicedComponent() end sub public function SqlConn as SqlConnection return m_rConnection end function public function State as ConnectionState return m_rConnection.State end function public sub myServicedComponentConnect(ConnectionString as string) m_rConnection = new SqlConnection(ConnectionString) m_rConnection.Open() end sub public sub myServicedComponentClose() if not (m_rConnection is nothing) then m_rConnection.Close() end sub public sub myServicedComponentDispose() m_rConnection.Dispose() end sub public function myServicedComponentCreateCommand() as SqlCommand return new SqlCommand(Nothing, m_rConnection) end function public sub myServicedComponentSetComplete() ' to commit changes Console.WriteLine("Committing connection's transaction.") ContextUtil.SetComplete() Console.WriteLine("SetComplete") end sub public sub myServicedComponentSetAbort() ' to rollback the distributed transaction Console.WriteLine("Aborting connection's transaction.") ContextUtil.SetAbort() Console.WriteLine("SetAbort") end sub ... ... private: SqlConnection * m_rConnection; public: myServicedComponent() { } __property SqlConnection * get_SqlConn() { return m_rConnection; } __property ConnectionState * get_State() { return __box (m_rConnection->State); } void myServicedComponentConnect(String * ConnectionString) { m_rConnection = new SqlConnection(ConnectionString); m_rConnection->Open(); } void myServicedComponentClose() { if (m_rConnection != 0) m_rConnection->Close(); } void myServicedComponentDispose() { if (m_rConnection != 0) m_rConnection->Dispose(); } void myServicedComponentChangeDatabase(String * DBName) { if (m_rConnection != 0) m_rConnection->ChangeDatabase(DBName); } SqlCommand * myServicedComponentCreateCommand() { return new SqlCommand(0, m_rConnection); } void myServicedComponentSetComplete() // to commit changes { Console::WriteLine("Committing connection\'s transaction."); ContextUtil::SetComplete(); Console::WriteLine("SetComplete"); } void myServicedComponentSetAbort() // to rollback the distributed transaction { Console::WriteLine("Aborting connection\'s transaction."); ContextUtil::SetAbort(); Console::WriteLine("SetAbort"); } ...

Finally, you will write code to execute (distributed) transactions on the ServicedComponent. In this code, you create an instance of the new derived class of the ServicedComponent and use it to connect to the database provider. Then several SQL commands are executed as distributed transactions using the myServicedComponent. The transactions are then finalized using the SetComplete method of the Microsoft .NET Framework Component Services. SqlCommand mySqlCommand; myServicedComponent mySC = new myServicedComponent(); mySC.myServicedComponentConnect("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind"); Console.WriteLine("connection state is: " + mySC.State); mySqlCommand = new SqlCommand(null, mySC.SqlConn); try { // Clean up the database to restore to its original state. mySqlCommand.CommandText = "if exists (select * from sysobjects where name = 'TestTable' and type = 'U') drop table TestTable"; mySqlCommand.ExecuteNonQuery(); // Create table "TestTable". Console.WriteLine("Creating table TestTable."); mySqlCommand.CommandText = "create table TestTable (c1 int identity, c2 int)"; mySqlCommand.ExecuteNonQuery(); // Create unique index ss on TestTable. Console.WriteLine("Creating unique index ss on TestTable."); mySqlCommand.CommandText = "create unique index ss on TestTable(c1)"; mySqlCommand.ExecuteNonQuery(); // Insert into TestTable values. Console.WriteLine("Inserting into TestTable values."); mySqlCommand.CommandText = "insert into TestTable values (11)"; mySqlCommand.ExecuteNonQuery(); // Commit the transaction. mySC.myServicedComponentSetComplete(); } catch (Exception e) { Console.Write(e.ToString()); // Rollback the transaction. mySC.myServicedComponentSetAbort(); } finally { mySC.myServicedComponentClose(); Console.WriteLine("connection state is: " + mySC.State); } Dim mySqlCommand as SqlCommand Dim mySC as myServicedComponent = new myServicedComponent() mySC.myServicedComponentConnect("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind") Console.WriteLine("connection state is: " & mySC.State) mySqlCommand = new SqlCommand(Nothing, mySC.SqlConn) try ' Clean up the database to restore to its original state. mySqlCommand.CommandText = "if exists (select * from sysobjects where name = 'TestTable' and type = 'U') drop table TestTable" mySqlCommand.ExecuteNonQuery() ' Create table "TestTable". Console.WriteLine("Creating table TestTable.") mySqlCommand.CommandText = "create table TestTable (c1 int identity, c2 int)" mySqlCommand.ExecuteNonQuery() ' Create unique index ss on TestTable. Console.WriteLine("Creating unique index ss on TestTable.") mySqlCommand.CommandText = "create unique index ss on TestTable(c1)" mySqlCommand.ExecuteNonQuery() ' Insert into TestTable values. Console.WriteLine("Inserting into TestTable values.") mySqlCommand.CommandText = "insert into TestTable values (11)" mySqlCommand.ExecuteNonQuery() ' Commit the transaction. mySC.myServicedComponentSetComplete() catch e as Exception Console.Write(e.ToString()) ' Rollback the transaction. mySC.myServicedComponentSetAbort() finally mySC.myServicedComponentClose() Console.WriteLine("connection state is: " & mySC.State) end try SqlCommand * mySqlCommand; myServicedComponent * mySC = new myServicedComponent(); mySC->myServicedComponentConnect(S"server=(local)\\SQLExpress;Trusted_Connection=yes;database=northwind;provider=SQLNCLI"); Console::Write("connection state is: "); Console::WriteLine(mySC->State); mySqlCommand = new SqlCommand(0, mySC->SqlConn); try { // Clean up the database to restore to its original state. mySqlCommand->CommandText = "if exists (select * from sysobjects where name = 'TestTable' and type = 'U') drop table TestTable"; mySqlCommand->ExecuteNonQuery(); // Create table "TestTable". Console::WriteLine("Creating table TestTable."); mySqlCommand->CommandText = "create table TestTable (c1 int identity, c2 int)"; mySqlCommand->ExecuteNonQuery(); // Create unique index ss on TestTable. Console::WriteLine("Creating unique index ss on TestTable."); mySqlCommand->CommandText = "create unique index ss on TestTable(c1)"; mySqlCommand->ExecuteNonQuery(); // Insert into TestTable values. Console::WriteLine("Inserting into TestTable values."); mySqlCommand->CommandText = "insert into TestTable values (11)"; mySqlCommand->ExecuteNonQuery(); // Commit the transaction. mySC->myServicedComponentSetComplete(); } catch (Exception * e) { Console::Write(e->ToString()); // Rollback the transaction. mySC->myServicedComponentSetAbort(); } __finally { //mySC->myServicedComponentClose(); //Console::WriteLine("connection state is: " + mySC->State); }

A related issue is key creation and strong naming of keys. We can use Strong Name tool "sn.exe" to create a key file. For example, the following command creates a new, random key pair and stores it in keyPair.snk.

sn -k keyPair.snk


Here is how to use the key pair in the registration of the assembly.

[assembly:System.Reflection.AssemblyKeyFile("keyPair.snk")] <assembly:System.Reflection.AssemblyKeyFile("keyPair.snk")> [assembly: AssemblyKeyFileAttribute("keyfile.snk")];