DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 815814 - Last Review: November 14, 2007 - Revision: 3.2

On This Page

SUMMARY

This step-by-step article describes how to use COM+ (Component Services) transactions in a Microsoft Visual C++ .NET class or in a Microsoft Visual C++ 2005 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.

Requirements

This article assumes that you are familiar with the following topics:
  • Transactional concepts and processing
  • COM+ (Component Services)

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. Under Project Types, click Visual C++ Projects, and then click Class Library (.NET) under Templates. Name the project prjEnterprise.

    Note In Visual Studio 2005, click Visual C++ under Project Types, and then click Class Library under Templates.
  4. In Solution Explorer, right-click References, and then click Add Reference.
  5. Click the NET tab in the Add Reference dialog box.
  6. Double-click System.EnterpriseServices under Component Name.
  7. Make sure that System.EnterpriseServices appears under Selected Components, and then click OK.
  8. Add the following code before any other statements in the prjEnterprise.h file:
    using namespace System;
    using namespace System::Data;
    using namespace System::Data::SqlTypes;
    using namespace System::Data::Common;
    using namespace System::EnterpriseServices;
    using namespace System::Data::SqlClient;
    
  9. Add a new class that is named clsES to the prjEnterprise.h file.
  10. To use COM+ Transactional Services, make sure that your class (clsES) inherits functionality from the ServicedComponent class as follows:
    public __gc class clsES:public ServicedComponent
    
  11. Use a Transaction attribute to specify the level of transactional support for the class as follows:
    [Transaction(TransactionOption::Required,Timeout=5)]public __gc class clsES:public ServicedComponent
    
  12. Create a method in the clsES class that receives four input integer parameters. Name the class dbAccess. 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:	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:
    SqlConnection * Conn = new SqlConnection("user id=<username>;password=<password>;Initial Catalog=northwind;Data Source=<Your SQL Server name>;");
    Conn->Open();
    Note Remember to change the connection string parameters to reflect the correct values for your SQL Server server.
  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 Products SET UnitsonOrder = @onOrderString WHERE productID = @pID1String ", Conn);
    sqlCommand->Parameters->Add(new SqlParameter("@onOrderString",SqlDbType::VarChar ,
    	40,ParameterDirection::Input,true,0,0,"Description",DataRowVersion::Current,onOrder.ToString()));
    sqlCommand->Parameters->Add(new SqlParameter("@pID1String", SqlDbType::VarChar,
    	40,ParameterDirection::Input,true,0,0,"Description",DataRowVersion::Current,pID1.ToString()));
    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->Parameters->Add(new SqlParameter("@inStockString",SqlDbType::VarChar ,
    	40,ParameterDirection::Input,true,0,0,"Description",DataRowVersion::Current,inStock.ToString()));
    sqlCommand->Parameters->Add(new SqlParameter("@pID2String", SqlDbType::VarChar,
    	40,ParameterDirection::Input,true,0,0,"Description",DataRowVersion::Current,pID2.ToString()));
    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. Use the following code to close the connection to the Northwind database:
    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.

Complete code listing

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

using namespace System;
using namespace System::Data;
using namespace System::Data::SqlTypes;
using namespace System::Data::Common;
using namespace System::EnterpriseServices;
using namespace System::Data::SqlClient;

namespace prjEnterprise
{
	
	[Transaction(TransactionOption::Required,Timeout=5)]
	public __gc class clsES:public ServicedComponent
	{
	public:	SqlConnection * Conn;

	public:	void dbAccess(int pID1, int onOrder, int pID2, int inStock)
		{
			try
			{			
				SqlConnection * Conn = new SqlConnection("user id=<username>;password=<password>;Initial Catalog=northwind;Data Source=<Your SQL Server name>;");
				Conn->Open();
				
				SqlCommand * sqlCommand = new SqlCommand("UPDATE Products SET UnitsonOrder = @onOrderString WHERE productID = @pID1String ", Conn);
				sqlCommand->Parameters->Add(new SqlParameter("@onOrderString",SqlDbType::VarChar ,
					40,ParameterDirection::Input,true,0,0,"Description",DataRowVersion::Current,onOrder.ToString()));
				sqlCommand->Parameters->Add(new SqlParameter("@pID1String", SqlDbType::VarChar,
					40,ParameterDirection::Input,true,0,0,"Description",DataRowVersion::Current,pID1.ToString()));
				sqlCommand->ExecuteNonQuery();
				
				sqlCommand->CommandText = "UPDATE Products SET UnitsinStock = @inStockString WHERE productID = @pID2String" ;
				sqlCommand->Parameters->Add(new SqlParameter("@inStockString",SqlDbType::VarChar ,
					40,ParameterDirection::Input,true,0,0,"Description",DataRowVersion::Current,inStock.ToString()));
				sqlCommand->Parameters->Add(new SqlParameter("@pID2String", SqlDbType::VarChar,
					40,ParameterDirection::Input,true,0,0,"Description",DataRowVersion::Current,pID2.ToString()));
				sqlCommand->ExecuteNonQuery();

				ContextUtil::SetComplete();
				Conn->Close();
			}
			catch(Exception * e)
			{
				ContextUtil::SetAbort();
			
				throw e;
			}			
		}
	};


}
Note You must add the common language runtime support compiler option (/clr:oldSyntax) in Visual C++ 2005 to successfully compile the previous code sample. To add the common language runtime support compiler option in Visual C++ 2005, follow these steps:
  1. Click Project, and then click <ProjectName> Properties.

    Note <ProjectName> is a placeholder for the name of the project.
  2. Expand Configuration Properties, and then click General.
  3. Click to select Common Language Runtime Support, Old Syntax (/clr:oldSyntax) in the Common Language Runtime support project setting in the right pane, click Apply, and then click OK.
For more information about the common language runtime support compiler option, visit the following Microsoft Web site:
/clr (Common Language Runtime Compilation)
http://msdn2.microsoft.com/en-us/library/k8d11d4s.aspx (http://msdn2.microsoft.com/en-us/library/k8d11d4s.aspx)

Verify that it works

To test this code, create a Console Application project that uses the clsES class. In one case, a transaction succeeds, and the UnitsOnOrder and UnitsInStock fields for the specified product are updated. In the second case, the update for the UnitsOnOrder field for a specified product succeeds, but the update for the UnitsInStock field for a product fails because the specified product number does not exist in the Products table. This causes a transaction failure, and the transaction is ignored.
  1. Start Visual Studio .NET or Visual Studio 2005.
  2. On the File menu, point to New, and then click Project.
  3. Under Project Types, click Visual C++ Projects, and then click Console Application (.NET) under Templates.

    Note In Visual Studio 2005, click Visual C++ under Project Types, and then click CLR Console Application under Templates.
  4. In the Name text box, type testES. Make sure that the Add to Solution option is selected.
  5. Click OK to add this project to the solution.
  6. For testES to test prjEnterprise, you must add a reference. In Solution Explorer, right-click References under testES (that you just added), and then click Add Reference.
  7. The Add Reference dialog box appears. On the Projects tab, double-click prjEnterprise.
  8. A reference appears under Selected Components. Click OK to add this reference to the project.
  9. Add a reference to the project to the System.EnterpriseServices namespace. In Solution Explorer, right-click References, and then click Add Reference.
  10. The Add Reference dialog box appears. Under Component Name on the .NET tab, double-click System.EnterpriseServices.
  11. Make sure that System.EnterpriseServices appears under Selected Components. Click OK.
  12. Right-click testES, and then click Set as Startup Project.
  13. Paste the following source code in the _tmain() function of the testES 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");
    	//Console::WriteLine("Error Message: {0}",e->Message);	 
    	//uncomment this line if you must get detailed error information
    }
    
  14. 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 UnitsOnOrder field updated to 5, Product 2 cannot have its UnitsInStock field set to -20. Because of a constraint that is defined in the Product table definition, UnitsInStock 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
  15. Examine the contents of the Northwind Products table by using SQL Server Enterprise Manager. When you view product 1, the UnitsOnOrder field is equal to 777. When you view Product 2, the UnitsInStock field is 888. Therefore, the second call to dbAccess (which would have resulted in different values for these fields) fails.

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. 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)]
    
  • You may receive some unexpected exceptions when running the code. To receive more information about these exceptions, uncomment the last two lines in step 13:
    Console::WriteLine("Error Message: {0}",e->Message);
    uncomment this line if you must get detailed error information

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
Serviced Components

APPLIES TO
  • Microsoft Visual C++ 2005 Express Edition
  • Microsoft Visual C++ .NET 2003 Standard Edition
Keywords: 
kbsqlclient kbcomplusqc kbcode kbhowtomaster kbhowto KB815814
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support