Ado. NET when TransactionScope needs to be enabled MSTDC (Distributed transaction Management)

We know that in ADO you can use TransactionScope to commit or cancel multiple SqlConnection (multiple database connections) of SQL statements into one thing, but use TransactionScope with extra care, because when transacti Onscope in special cases need to start MSTDC (Distributed transaction Management), then we see when TransactionScope need to start mstdc?

First of all, the code in this example and the database environment, first of all, the C # code running in this example of the computer and SQL Server database is located on the local area network of two machines, that is, the program and SQL Server is located on two computers, why do you want to emphasize this problem? Because I read the article found that the program and SQL Server are deployed on a computer, and deployed on two computers, TransactionScope behavior is not the same, this is also related to SQL Server version, there will be a summary later.

Let's take a look at an example, in the following client program code we use TransactionScope to launch two SqlConnection, these two SqlConnection use the same connection string, At this time our client program is located on the computer is not started MSTDC, so if TransactionScope need to start MSTDC then the following code will throw an exception.

1 protected Const stringConnectionString =@"Data source=192.168.1.3;initial catalog=mobile_reporting_dm_staging; Persist Security info=true; User Id=sa; PASSWORD=0OKM9IJN*UHB&YGV";2 3 Static voidMain (string[] args)4 {5     stringsql =string. Empty;6 SqlCommand sqlcom;7 8     using(TransactionScope tran =NewTransactionScope (transactionscopeoption.requiresnew))9     {Ten         using(SqlConnection SqlCon1 =NewSqlConnection (connectionString)) One         { A Sqlcon1.open (); -  -sql ="INSERT INTO [dbo]. [T_people] (name,age) VALUES (N ' gang ' )"; the  -sqlcom =NewSqlCommand (SQL, sqlCon1); - sqlcom.executenonquery (); -         } +  -         using(SqlConnection SqlCon2 =NewSqlConnection (connectionString)) +         { A Sqlcon2.open (); at  -sql ="INSERT INTO [dbo]. [T_people] (name,age) VALUES (N ' Li Qiang ' )"; -  -sqlcom =NewSqlCommand (SQL, sqlCon2); - sqlcom.executenonquery (); -         } in  - Tran.complete (); to     } +  - console.readline (); the}

After executing this code we find that the program does not have an exception, and two SqlConnection have successfully inserted a data into the database, which indicates that the above code TransactionScope does not use the MSTDC service. This means that TransactionScope does not start the MSTDC service if multiple SqlConnection (multiple database connections) connected to the same database on the same SQL Server instance are started in TransactionScope.

Then we modify the above code to define the two connection string TransactionScope two SqlConnection

Ado. NET when TransactionScope needs to be enabled MSTDC (Distributed transaction Management)