11事务

1事务范围-TransactionScope

1.1 交易管理员会决定要参与哪个事务。

1.2 调用Complete 方法，提交事务。

1.3 如果有异常在事务范围内发生，则会复原范围所参与的事务。

2 添加dll

System.Transactions.dll

3 代碼

 

      /// <summary>
        /// 測試事務範圍
        /// </summary>
        private void btnTrans_Click(object sender, EventArgs e)
        {
            // 1 刪除所有
            // 2 添加一條新的記錄
            // 3 修改一條記錄
            // 4 添加一條已經存在記錄（違反PK）
            using (System.Transactions.TransactionScope tx = new System.Transactions.TransactionScope())
            {
                try
                {
                    // 刪除所有
                    try
                    {
                        Customer cust = db.Customers.Single<Customer>(n => n.CustomerID == "JIM");
                        if (cust != null)
                        {
                            db.Customers.DeleteOnSubmit(cust);
                            db.SubmitChanges();
                        }
                    }
                    catch (Exception ex)
                    {
                        // 經實踐，要拋出異常，不可忽略
                        // 一定要throw
                        throw ex;
                        //MessageBox.Show(ex.Message);
                    }


                    try
                    {
                        // 添加一條記錄-預期成功
                        object[] para = { "JIM", "TOM", "TOM", "other", "other2" };
                        int ret = db.ExecuteCommand(" INSERT INTO Customers(CustomerID, CompanyName, ContactName) VALUES({0},{1},{2}) ", para);

                        // 修改一條記錄-預期成功
                        object[] paraUpdate = { "TOMx", "TOMx", "JIM" };
                        db.ExecuteCommand(" UPDATE Customers SET CompanyName={0}, ContactName={1} WHERE CustomerID={2} ", paraUpdate);

                        // 添加一條記錄-預期失敗，違反主鍵約束
                        db.ExecuteCommand(" INSERT INTO Customers(CustomerID, CompanyName, ContactName) VALUES({0},{1},{2}) ", para);
                    }
                    catch (Exception ex)
                    {
                        // 經實踐，要拋出異常，不可忽略
                        // 一定要throw
                        throw ex;
                        //MessageBox.Show(ex.Message);
                    }

                    tx.Complete();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        } // end private void btnTrans_Click(object sender, EventArgs e)

注意：事务范围从{开始，到}结束。

（2）事务范围内，代码有异常一定得抛出。因为TransactionScope根据异常判断是否有错。

（3）必须调用Complete()方法。因为需要提交到数据库，并且保存到数据库。

4 学习资源

4.1MSDN TransactionScope 例子

        // 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();

            // 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())
            {
                using (SqlConnection connection1 = new SqlConnection(connectString1))
                {
                    try
                    {
                        // 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);

                        // 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))
                            try
                            {
                                // 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);
                            }
                            catch (Exception ex)
                            {
                                // Display information that command2 failed.
                                writer.WriteLine("returnValue for command2: {0}", returnValue);
                                writer.WriteLine("Exception Message2: {0}", ex.Message);
                            }
                    }
                    catch (Exception ex)
                    {
                        // Display information that command1 failed.
                        writer.WriteLine("returnValue for command1: {0}", returnValue);
                        writer.WriteLine("Exception Message1: {0}", ex.Message);
                    }
                }

                // The Complete method commits the transaction. If an exception has been thrown,
                // Complete is not  called and the transaction is rolled back.
                scope.Complete();
            }

            // The returnValue is greater than 0 if the transaction committed.
            if (returnValue > 0)
            {
                writer.WriteLine("Transaction was committed.");
            }
            else
            {
                // You could write additional business logic here, for example, you can notify the caller 
                // by throwing a TransactionAbortedException, or logging the failure.
                writer.WriteLine("Transaction rolled back.");
            }

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

            return returnValue;
        }