For Microsoft Visual Basic .NET versions of this article, see
316627.
This article refers to the following Microsoft .NET Framework Class library namespace:
System.Data.Sqlclient System.EnterpriseServices System.Runtime.CompilerServices System.Reflection
This task content
summary
Require Overview Creating Project Reference
Summary This article steps to demonstrate how to use .NET Provider and
The ServicedComponent class performs a distributed transaction. Although this paper is a SQLClient .NET provider on the Microsoft SQL Server server, you can also use ODBC or OLE DB .NET Managed Provider.
Back to the top
Require the following list summarizes the recommended hardware, software, network structure, and the required service pack:
Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server Microsoft Visual Studio .Net Microsoft SQL Server 7.0 or Microsoft SQL Server 2000
Back to the top
Overview If appropriate, an instance of the .NET Framework class can participate in automated transactions. All resources accessed by class instances or objects will be registered in transactions. For example, if an object uses ADO.NET in a database deposit, the resource manager of the database will determine if an object is running in a transaction. If the object is running in the transaction, the Explorer will automatically register the database in the transaction.
Prepare the steps to participate in automated transactions as follows:
Applying the TransactionAttribute class to your class to specify the automatic transaction type of the component request. The transaction type must be a member of TransactionOption enumeration. Delicate your class from the ServicedComponent class. ServicedComponent is a base class for all classes that use COM services. Make sure the assembly contains unique key pairs with a strong name (STRONG NAME) assembly (SIGN). Register an assembly that contains your class in COM Catalog. Remarks: If the client that calls your class's instance is run by the public language running library, the system will be registered for you. This step is required only when the universal instance is created and invoked for your class. Manually register the assembly using the .NET Service Installation Tool (Regsvcs.exe). For more information on how to use a strong name tag assembly, see the following topics in the Microsoft .NET Framework Developer Guide:
Use the strong name tag assembly http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconassigningassembly/html/cpconassigningassemblyStrongName.asp For more information on this step, see "Microsoft The following topics in the .NET Framework Developer Guide:
Automatic transaction and .NET framework class http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconautomaticTransactionsNetFrameworkClasses.asp
Back to the top
Create a project
Follow these steps to create a new console application project in Visual C # .NET:
Start Visual Studio .NET. In the File menu, point to New, and then click Project. In the New Project dialog box, click the Visual C # item under the project type, click Console Apps under Templates, and then click OK. In the Solution Explorer, rename the class1.cs file to distributedTransaction.cs. Delete all the code in the DistributeTransaction.cs file. In the project menu, click Add Reference, then add the following reference: System.EnterpriseServices System.Data.dll In the assemblyinfo.cs file, comment out of the following code rows: [Assembly: askEMBLYKEYFILE (")]
[assmbly: assemblykeykeyname ("")]
Add the following code to the DistributedTransaction.cs file: USING SYSTEM;
Using system.data.sqlclient;
Using system.enterprises;
Using system.Runtime.compilerServices;
Using system.reflection;
[Assembly: ApplicationName ("DistributedTransAction")]]]
[assmbly: assemblykeyKeyFileAttribute ("../../ distributetransaction.snk")]
Namespace DistributeTransaction
{
///
/// summary description for class1.
///
Class class1
{
///
/// The main entry point for the application.
///
[Stathread]
Static void main (string [] args)
{
Try
{
DistributedTran MyDistributeTran = New DistributeTran ();
MyDistributedTran.testDistributeTransAction ();
}
Catch (system.data.sqlclient.sqlexception e)
{
System.console.writeline ("Transaction Aborted: ERROR RETURNED: E.MESSAGE);
}
}
}
///
/// summary description for testApp.
///
[Transaction (TransactionOption.Required)]
Public Class DistributedTran: ServicedComponent
{
Public distributedtran ()
{
}
[AutoComplete]
Public String TestDistributeTransAction ()
{
// The Following Insert Statement Goes To The First Server.
// this Insert Statement Does Not Product Any Errors.
String insertcmdsql = "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.
String Exceptioncausingcmdsql = "delete from nonexistentable";
// 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.
SqlConnection SQLCONN1 = New SqlConnection ("Server = Server_Name; UID = User_ID; Database = DatabaseName; PWD = Password");
SqlConnection SQLCONN2 = New SqlConnection ("Server = Server_Name; UID = User_ID; Database = DatabaseName; PWD = Password");
Try
{
Sqlcommand insertcmd = new sqlcommand (insertcmdsql, sqlconn1);
SQLCommand ExceptionCausingcmd = New SqlCommand (ExceptionCausingcmdsql, SqlConn2);
// this Command Runs Properly.
INSERTCMD.CONNECMD.CONNECTION.OPEN ();
INSERTCMD.EXECUTENONQUERY ();
// this Command Results in An Exception, Which AutomaticLolls Back
// The first command (The InsertCMD Command).
Exceptioncausingcmd.connection.open ();
INT cmdResult = exceptioncausingcmd.executenonquery ();
SqlConn1.close ();
SqlConn2.close ();
Console.writeline ("Hello");
}
Catch (system.data.sqlclient.sqlexception ex)
{
// 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")
SqlConn1.close ();
IF (SqlConn2.State.toString () == "open")
SqlConn2.close ();
}
Return "Success";
}
}
}
In the File menu, click Save. Click Start, point to Programs, point to Microsoft Visual Studio .NET, point to the Visual Studio .Net tool, and then click the Visual Studio .NET command prompt. Open the folder that contains your project, then run the following command, with a strong name tag assembly: Sn -k distributetransaction.snk compiles your application. 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'uSERTABLE ') = 1)
DROP TABLE [DBO]. [TestTransaction]
Go
Create Table [DBO]. [TestTransaction] (
[Col1] [int] NULL,
[Col2] [varchar] (100) NULL
) On [primary]
Go
Run your application. Note that you will receive the following error message (this is an expected behavior):
Transaction Aborted: Error Returned: Invalid Object Name 'NoneXistentTable'.
Open the SQL Server Query Analyzer, add the following code, then press F5 to run the query: use northwind;
Select * from testtransaction where col1 = 1 and color = 'sql test'
Note that the query will not return any rows because the transaction has been rolled back. Find the following code in your Visual C # project: string exceptioncausingcmdsql = "delete from nonexistentable";
Use a valid query that does not cause a transaction rollback to replace the SQL statement. For example: string exceptioncausingcmdsql = "select @@ identity from customer;"
Press F5 to compile and run the application again. In the query analyzer, run the command in step 12 again. Note that the query returns a row because the transaction can be successfully submitted.
Remarks:
This example does not perform an error handling. All clients and servers must run SQL Server and Microsoft Distributed Transaction Coordinator (MS DTC).
Back to the top
Refer to other information, click the following article number, view the Microsoft Knowledge Base article:
306296 How to: Create a .NET service component using transactions in Visual C # .NET
The information in this article applies to:
Microsoft ADO.NET (included with the .NET Framework) Microsoft Visual C # .NET (2002) Microsoft Enterprise Services (included with the .NET Framework) Last Updated: 2004-3-25 (1.2) Keywords: kbCompiler kbhowto kbHOWTOmaster kbSqlClient kbSystemData KB316247 KBAUDDEVELOPER
