How to use COM+ transactions in a Visual C# component (816141)

SUMMARY

This step-by-step article describes how to use COM+ (Component Services) transactions in a Visual C# class. A set of database operations is considered one unit. Either all operations succeed or, if one operation fails, the whole transaction fails. In the latter case, any database operations that were tried 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 that you must have:

• Microsoft Windows 2000 Server SP1
• Microsoft Internet Information Services (IIS) version 4.0 or later
• Microsoft Internet Explorer versions 5.0, 5.5, or 6.0

This article assumes that you are familiar with the following:

• Transactional concepts and processing
• COM+ (Component Services)

back to the top

COM+ Transaction Services

You can implement transaction processing with the System.EnterpriseServices namespace in the Microsoft .NET Framework. To access COM+ transactional services, create a class. To do this, follow these steps:

1. Start Visual Studio .NET or Visual Studio 2005.
2. On the File menu, point to New, and then click Project.
3. Click Visual C# Projects under Project Types, and then click Class Library under Templates. Name the project prjEnterprise.
   
   Note In Visual Studio 2005, click Visual C# under Project Types, and then click Class Library under Templates. Name the project prjEnterprise.
4. By default, Class1 is created.
5. In Solution Explorer, right-click References, and then click Add Reference.
6. The Add Reference dialog box appears. Under Component Name on the .NET tab, double-click System.EnterpriseServices.
7. Make sure that System.EnterpriseServices appears under Selected Components. Click OK.
8. Add the following code before to any other statements in the Class1.cs file:

   using System.EnterpriseServices;
   using System.Data.SqlClient;

9. Add a new class that is named clsES to the Class1.cs file.
10. To use COM+ Transactional Services, your class (clsES) must inherit functionality from ServicedComponent as follows:

    public class clsES : ServicedComponent

11. A Transaction attribute is used to specify the level of transactional support for the class as follows:

    [Transaction(TransactionOption.Required)]public class clsES : ServicedComponent

12. Create a method in the clsES class, and name it 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:

    void dbAccess(int pID1,int onOrder, int pID2, int inStock)

13. In the dbAccess method, create a SQL connection object for the Northwind database, and then open the connection. Database operations occur by using the following database:
    
    Note Remember to change the following connection string parameters to reflect the correct values for your SQL Server server.
    

    SqlConnection Conn = new SqlConnection("user id=<username>;password=<strong password>;Initial Catalog=northwind;Data Source=2E124\\SQL;");
    				Conn.Open();
    

14. Set a try block to capture any exceptions that might occur during database processing. You must catch these exceptions to abort the transaction. The try block includes two database operations. Each operation updates a different field in a specified products table record.

     try { 
    

15. Perform the first update to the products table. Update the UnitsonOrder field with the onOrder value for product with ID, as specified in the first two input parameters. Use the following SQL command to run this SQL update:

    SqlCommand sqlCommand = new SqlCommand("UPDATE myProducts SET UnitsonOrder = " + onOrder + " WHERE productID = " + pID1, Conn);
    				sqlCommand.ExecuteNonQuery();

16. Perform another update to the products table. Update the UnitsinStock field with the inStock value for product with ID, as specified in the third and fourth input parameters. Use the following SQL command to run this SQL update:

    sqlCommand.CommandText = "UPDATE myProducts SET UnitsinStock = " + inStock + " WHERE productID = " + pID2;
    				sqlCommand.ExecuteNonQuery();
    

17. 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();

18. The connection to the Northwind database is closed:

    Conn.Close();
    }

19. You must catch any exceptions that occur while running the SQL commands so that you can abort the whole transaction:

    catch(Exception e){ 

20. The setAbort method of the contextUtil class from the System.EnterpriseServices namespace is used to abort the whole 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;
    }

21. For this component to function correctly, the component must have a strong name. Generate a strong name, and then sign the assembly with the strong name. To do this, follow these steps:
    1. At the Visual Studio .NET command prompt, type sn.exe -k snEnterprise.snk to create a key file. For more information about signing assemblies with strong names, see the .NET Framework SDK documentation.
    2. Copy snEnterprise.snk to your project folder.
    3. In AssemblyInfo.vc, add the following line of code before or after other assembly attribute statements:

       [assembly: AssemblyKeyFileAttribute("..\\..\\snEnterprise.snk")]		

    4. Save, and then build your project.

back to the top

Complete Code Listing

Note Remember to change the following connection string parameters to reflect the correct values for your SQL Server server.

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.Common;
using System.EnterpriseServices;
using System.Data.SqlClient;

namespace prjEnterprise
{
	
	[Transaction(TransactionOption.Required)]public class clsES:ServicedComponent
	{
		public SqlConnection Conn;

		public void dbAccess(int pID1, int onOrder, int pID2, int inStock)
		{
			try
			{			
				SqlConnection Conn = new SqlConnection("user id=<username>;password=<strong password>;Initial Catalog=northwind;Data Source=2E124\\SQL;");
				Conn.Open();
				
SqlCommand sqlCommand = new SqlCommand("UPDATE myProducts SET UnitsonOrder = " + onOrder + " WHERE productID = " + pID1, Conn);
				sqlCommand.ExecuteNonQuery();
				
				sqlCommand.CommandText = "UPDATE myProducts SET UnitsinStock = " + inStock + " WHERE productID = " + pID2;
				sqlCommand.ExecuteNonQuery();

				ContextUtil.SetComplete();
				Conn.Close();
			}
			catch(Exception e)
			{
				ContextUtil.SetAbort();
			
				throw e;
			}
			finally
			{

			}
		}
	}	
}

back to the top

Verify That It Works

To test this code, create a console application that uses the clsES project. 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, and the transaction is ignored.

1. In Visual Studio .NET or Visual Studio 2005, point to New on the File menu, and then click Project.
2. Click Visual C# Projects under Project Types, and then click Console Application under Templates.
   
   Note In Visual Studio 2005, click Visual C# under Project Types, and then click Console Application under Templates.
3. In the Name text box, type testES. Make sure that the Add to Solution option is selected.
4. Click OK to add this project to the solution.
5. For testES to test clsES, you must add a reference. In Solution Explorer, right-click References under testES (that you just added), and then click Add Reference.
6. The Add Reference dialog box appears. On the Projects tab, double-click prjEnterprise.
7. A reference appears under Selected Components. Click OK to add this reference to the project.
8. Add a reference to the project to the System.EnterpriseServices library. In Solution Explorer, right-click References, and then click Add Reference .
9. The Add Reference dialog box appears. Under Component Name on the .NET tab, double-click System.EnterpriseServices.
10. Make sure that System.EnterpriseServices appears under Selected Components. Click OK.
11. Right-click the console application ( testES), and then click Set as Startup Project
12. Paste the following source code in the Main function of the Class1 class :

    	prjEnterprise.clsES myTest = new prjEnterprise.clsES();
    
    				try 
    				{
    					myTest.dbAccess(1, 777, 2, 888);
    					Console.WriteLine("TRANSACTION ONE -- SUCCESS");
    
    					myTest.dbAccess(1, 5, 2, -20);
    					Console.WriteLine("TRANSACTION TWO -- SUCCESS");
    				}
    				catch (Exception e)
    				{
    					Console.WriteLine("TRANSACTION FAILURE");
    				}

13. Press F5 to run the test code.
    
    In the code in step 7, the first call to dbAccess succeeds. Product 1 and 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, you receive the following message in the output window TRANSACTION ONE - SUCCESS
    
    The second call to dbAccess fails. Therefore, neither update statement in 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 in the Product table definition, inStock is not permitted to have negative numbers.
    
    Therefore, this call to dbAccess fails, and the whole transaction fails. The Products table remains as it was before the call to dbAccess. The catch statement handles notification of the transaction failure from dbAccess, and you receive the following error message in the output window:
    TRANSACTION FAILURE
14. Examine the contents of the Northwind Products table by using SQL Server Enterprise Manager. When you view product 1, the onOrder field is equal to 777. When you view Product 2, the instock field is 888. Therefore, the second call to dbAccess (which would have resulted in different values for these fields) fails.

back to the top

Troubleshoot

• 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. The serviced component is located 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 example, the associated method has 1,200 seconds to complete any transaction before it times out:

  [Transaction(TransactionOption.Required,timeout:=1200)]

back to the top