Use TransactionScope for multi-database connection transaction operations

When an application needs to perform transactional operations in multiple databases, the TransactionScope class can be used to meet this requirement of the application. As long as the operation code for multiple databases is within the same transaction range, you can perform transaction operations for multi-database connections.

Technical Points

This example describes how to use TransactionScope in a program to implement multi-database connection transaction operations. The technical points are as follows.

-Because different database operations in the same transaction scope are considered as the same transaction, the transaction scope can be used to easily implement transaction operations with multiple data connections.

-The Complete method should be called only once within the transaction scope. When the Complete method within the transaction scope is called, data operations in the transaction scope are attempted to be submitted, and automatic rollback is performed when the commit fails, if the Complete method is not executed within the transaction scope, the transaction scope ends when the operation is not committed.

Steps

(1) create a console application project named "MultiDatabaseTransactionScope ".

(2) Open and edit the Program. cs file. The Code is as follows.

Using System;

Using System. Collections. Generic;

Using System. Text;

Using System. Transactions;

Using System. Data;

Using System. Data. SqlClient;

Namespace MultiDatabaseTransactionScope

{

Class Program

{

Static void Main (string [] args)

{

// Run the code in the created transaction range instance

Using (TransactionScope ts = new TransactionScope ())

{

// String connecting to database 1

String ConnectionString1 = @ "Data Source = localhost; Initial Catalog = Northwind; Integrated Security = SSPI ;";

// Create database 1 connection class instance 1

SqlConnection conn1 = new SqlConnection (ConnectionString1 );

// Create database 1 command class instance 1

SqlCommand command1 = new SqlCommand (@ "INSERT Shippers (CompanyName, Phone)

VALUES ('test ship2', '2014-0000 ') ", conn1 );

Conn1.Open (); // connect to database 1

Command1.ExecuteNonQuery (); // execute the command on database 1

Console. WriteLine ("database 1 Command executed ");

Conn1.Close (); // close database 1

// String connecting to database 2

String ConnectionString2 = @ "Data Source = localhost; Initial Catalog = pubs; Integrated Security = SSPI ;";

// Create database 2 connection class instance 2

SqlConnection conn2 = new SqlConnection (ConnectionString2 );

// Create database 2 command class instance 2

SqlCommand command2 = new SqlCommand (@ "INSERT Discounts (Discounttype, Discount) VALUES ('other', 12)", conn2 );

Conn2.Open (); // connect to database 2

Command2.ExecuteNonQuery (); // execute the command on Database 2

Console. WriteLine ("database 2 Command executed ");

Conn2.Close (); // closes database 2

Console. Write ("do you want to submit a transaction? (Y/N )");

If (Console. ReadKey (false). Key = ConsoleKey. Y)

{

Ts. Complete (); // submit the transaction

Console. WriteLine ("");

Console. WriteLine ("transaction commit completed ");

}

Else

{

Console. WriteLine ("cancel transaction commit ");

}

}

}

}

}

(3) Press F5 to run the program. The running result is as follows.

Database 1 Command executed

Database 2 Command executed

Submit a transaction? (Y/N) y

Transaction submitted

Source program explanation

In this example, a transaction range is created, two different database connections are created within the transaction range, and data operations are performed through SqlCommand instances, finally, you can use your keyboard input to determine whether to submit data operation transactions in two database connections.