關於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.
                    scope.Complete();

                }

            }
            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);
                    }
                    scope2.Complete();
                }

                //Not Complete
                //scope1.Complete();

            } 

 

經過測試， 這裡我想強調的是 :當被嵌套的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跑一下，體會一下過程。