关于TransactionScope的DEMO

关于TransactionScope的DEMO

1.同一个Scope里多个本地连接。 先把官网上的代码贴过来：

// This function takes arguments for 2 connection strings and commands to create a transaction

// involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the

// transaction is rolled back. To test this code, you can connect to two different databases

// on the same server by altering the connection string, or to another 3rd party RDBMS by

// altering the code in the connection2 code block.

static public int CreateTransactionScope(

string connectString1, string connectString2,

string commandText1, string commandText2)

{

// Initialize the return value to zero and create a StringWriter to display results.

int returnValue = 0;

System.IO.StringWriter writer = new System.IO.StringWriter();

try

{

// Create the TransactionScope to execute the commands, guaranteeing

// that both commands can commit or roll back as a single unit of work.

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))

{

using (SqlConnection connection1 = new SqlConnection(connectString1))

{

// Opening the connection automatically enlists it in the

// TransactionScope as a lightweight transaction.

connection1.Open();

// Create the SqlCommand object and execute the first command.

SqlCommand command1 = new SqlCommand(commandText1, connection1);

returnValue = command1.ExecuteNonQuery();

writer.WriteLine("Rows to be affected by command1: {0}", returnValue);

//throw new ApplicationException("error happened");

// If you get here, this means that command1 succeeded. By nesting

// the using block for connection2 inside that of connection1, you

// conserve server and network resources as connection2 is opened

// only when there is a chance that the transaction can commit.

using (SqlConnection connection2 = new SqlConnection(connectString2))

{

// The transaction is escalated to a full distributed

// transaction when connection2 is opened.

connection2.Open();

// Execute the second command in the second database.

returnValue = 0;

SqlCommand command2 = new SqlCommand(commandText2, connection2);

returnValue = command2.ExecuteNonQuery();

writer.WriteLine("Rows to be affected by command2: {0}", returnValue);

}

}

// The Complete method commits the transaction. If an exception has been thrown,

// Complete is not called and the transaction is rolled back.

plete();

}

}

catch (TransactionAbortedException ex)

{

writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message); } catch (ApplicationException ex) {

writer.WriteLine("ApplicationException Message: {0}", ex.Message);

}

// Display messages. Console.WriteLine(writer.ToString());

return returnValue;

}

在上面的代码中，只有一个Transaction Scope,在Scope中存在两个连接，值得注意的是：

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))

其中 TransactionScopeOption.RequiresNew 会使得当前的 Transaction.Current.TransactionInformation.LocalIdentifier 发生变化，也就是说使用的是新的一个事务；另外，这里的事务隔离级别让我很不解，不管换成ReadUncommitted还是ReadUncommitted，貌似都会锁表，导致我在SQLSEVER Manamgment studio里查询被Pending住。

staticvoid Main(string[] args)

{

string str1 = "Data Source=JASPER-QIU;Initial Catalog=TestDB;Integrated Security=True;max pool size=5";

string str2 = str1;

string cmdText1 = "insert into students(name,age) values('jasper',25)";

string cmdText2 = "insert into courses(name,flag) values('english','Y')";

CreateTransactionScope(str1, str2, cmdText1, cmdText2);

//EmbededScopeTest(str1, str2, cmdText1, cmdText2);

Console.Read();

} 2.Scope嵌套

static void EmbededScopeTest(

string connectString1, string connectString2,

string commandText1, string commandText2)

{

int returnValue = 0; System.IO.StringWriter writer = new System.IO.StringWriter();

using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted }))

{

using (SqlConnection connection1 = new SqlConnection(connectString1))

{

string scope1ID = Transaction.Current.TransactionInformation.LocalIdentifier;

Console.WriteLine(scope1ID);

// Opening the connection automatically enlists it in the

// TransactionScope as a lightweight transaction.

connection1.Open();

// Create the SqlCommand object and execute the first command.

SqlCommand command1 = new SqlCommand(commandText1, connection1);

returnValue = command1.ExecuteNonQuery();

writer.WriteLine("Rows to be affected by command1: {0}", returnValue);

}

using (TransactionScope scope2 = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))

{

string scope2ID = Transaction.Current.TransactionInformation.LocalIdentifier;

Console.WriteLine(scope2ID);

using (SqlConnection connection2 = new SqlConnection(connectString2)) {

// Opening the connection automatically enlists it in the

// TransactionScope as a lightweight transaction.

connection2.Open();

// Create the SqlCommand object and execute the first command.

SqlCommand command2 = new SqlCommand(commandText2, connection2);

returnValue = command2.ExecuteNonQuery();

writer.WriteLine("Rows to be affected by command1: {0}", returnValue);

}

plete();

}

//Not Complete

//plete();

}

经过测试， 这里我想强调的是 :当被嵌套的SCOPE,使用的是TransactionScopeOption.Required 实例化出来的，在主SCOPE没有complete的情况下，被嵌套的事务时不会提交的，但是如果被嵌套的事务使用的是TransactionScopeOption.RequiresNew，即使 外面的事务回滚了，它也会被提交。

另外大家可以通过这个SQL 来查看当前数据连接状况：

create table #TempTable(spid int,ecid int,status varchar(32),loginname varchar(32),hostname varchar(32),blk int,dbname varchar(32),cmd varchar(32),request_id int);

insert into #TempTable

exec sp_who;

select * from #TempTable where [dbname] = 'TestDB';

drop table #TempTable

以上见解都可以通过DEMO来了解，感兴趣的朋友可以把我的DEMO跑一下，体会一下过程。

本文来源：https://www.bwwdw.com/article/0de1.html