確保交易的新利器(TransactionScope)初體驗-Part 2(物件維護的交易確保)

2008-05-15

承繼上一篇【確保交易的新利器(TransactionScope)初體驗-Part 1(注意Scope.Complete的位置)】接著小喵有興趣的事，現在很多人開始使用OOP設計系統，那麼在一個商業邏輯中，透過兩個以上的物件來分別維護數個資料，在這樣的過程中，又如何確保交易的完整性呢。

於是小喵再次撰寫個物件來做個測試，這次

承繼上一篇【確保交易的新利器(TransactionScope)初體驗-Part 1(注意Scope.Complete的位置)】接著小喵有興趣的事，現在很多人開始使用OOP設計系統，那麼在一個商業邏輯中，透過兩個以上的物件來分別維護數個資料，在這樣的過程中，又如何確保交易的完整性呢。

於是小喵再次撰寫個物件來做個測試，這次針對北風資料庫的Employees這個資料表的內容來設計一個Employee物件，物件中有個UpdateEmployeeData的方法(Function)來維護Employee的資料

透過分別建立兩個Employee物件執行個體，然後分別呼叫UpdateEmployeeData，跟上一個範例一樣：

建立Emp1,Emp9兩個物件執行個體，他們的EmployeeID分別為1,9
將Emp1的LastName最後一個字加上【X】字元，這部分是應該可以過得
小喵試著要把Emp9的FirstName設定為【ABC123456789】,從資料表Layout看出，FirstName欄位大小是10個字元，因此這個應該會出現錯誤

接著來看看程式碼的部分

首先是objEmployee這個物件類別的內容如下：

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Public Class objEmployee
    Private m_EmployeeID As Integer
    Private m_LastName As String
    Private m_FirstName As String
    Private m_Title As String
    Private m_TitleOfCourtesy As String
    Private m_BirthDate As DateTime
    Private m_HireDate As DateTime
    Private m_Address As String

    Public Property EmployeeID() As Integer
        Get
            Return m_EmployeeID
        End Get
        Set(ByVal value As Integer)
            m_EmployeeID = value
        End Set
    End Property

    Public Property LastName() As String
        Get
            Return m_LastName
        End Get
        Set(ByVal value As String)
            m_LastName = value
        End Set
    End Property

    Public Property FirstName() As String
        Get
            Return m_FirstName
        End Get
        Set(ByVal value As String)
            m_FirstName = value
        End Set
    End Property

    Public Property Title() As String
        Get
            Return m_Title
        End Get
        Set(ByVal value As String)
            m_Title = value
        End Set
    End Property

    Public Property TitleOfCourtesy() As String
        Get
            Return m_TitleOfCourtesy
        End Get
        Set(ByVal value As String)
            m_TitleOfCourtesy = value
        End Set
    End Property

    Public Property BithDay() As DateTime
        Get
            Return m_BirthDate
        End Get
        Set(ByVal value As DateTime)
            m_BirthDate = value
        End Set
    End Property

    Public Property HirDate() As DateTime
        Get
            Return m_HireDate
        End Get
        Set(ByVal value As DateTime)
            m_HireDate = value
        End Set
    End Property

    Public Property Address() As String
        Get
            Return m_Address
        End Get
        Set(ByVal value As String)
            m_Address = value
        End Set
    End Property

    Sub New()

    End Sub

    Sub New(ByVal myEmployeeID As Integer)
        m_EmployeeID = myEmployeeID
        GetEmployeeData()
    End Sub

    Public Sub GetEmployeeData()
        Dim oConnStr As New objConnStr
        Dim ConnStr As String = oConnStr.ConnectionString

        Using Conn As New SqlConnection(ConnStr)
            Conn.Open()
            Try
                Dim SqlTxt As String = ""
                SqlTxt += " SELECT * "
                SqlTxt += " FROM Employees "
                SqlTxt += " WHERE EmployeeID = @EmployeeID "
                SqlTxt += "  "
                Dim Cmmd As New SqlCommand(SqlTxt, Conn)
                Cmmd.Parameters.AddWithValue("@EmployeeID", m_EmployeeID)

                Dim Dr As SqlDataReader = Cmmd.ExecuteReader
                If Not Dr.HasRows Then
                    Throw New Exception("無此員工代號資料!!")
                End If

                Dr.Read()

                m_LastName = Dr.Item("LastName")
                m_FirstName = Dr.Item("FirstName")
                m_Title = Dr.Item("Title")
                m_TitleOfCourtesy = Dr.Item("TitleOfCourtesy")
                m_BirthDate = Dr.Item("BirthDate")
                m_HireDate = Dr.Item("HireDate")
                m_Address = Dr.Item("Address")

                Dr.Close()

            Catch ex As Exception
                Throw New Exception(ex.Message)

            End Try
        End Using
    End Sub

    Public Sub UpdateEmployee()
        Dim oConnStr As New objConnStr
        Dim ConnStr As String = oConnStr.ConnectionString

        Using Conn As New SqlConnection(ConnStr)
            Conn.Open()
            Try
                Dim SqlTxt As String = ""
                SqlTxt += " UPDATE Employees "
                SqlTxt += " SET "
                SqlTxt += "     LastName=@LastName "
                SqlTxt += "     ,FirstName=@FirstName "
                SqlTxt += "     ,Title=@Title "
                SqlTxt += "     ,TitleOfCourtesy=@TitleOfCourtesy "
                SqlTxt += "     ,BirthDate=@BirthDate "
                SqlTxt += "     ,HireDate=@HireDate "
                SqlTxt += "     ,Address=@Address "
                SqlTxt += " WHERE EmployeeID = @EmployeeID "
                SqlTxt += "  "
                Dim Cmmd As New SqlCommand(SqlTxt, Conn)
                Cmmd.Parameters.AddWithValue("@EmployeeID", m_EmployeeID)
                Cmmd.Parameters.AddWithValue("@LastName", m_LastName)
                Cmmd.Parameters.AddWithValue("@FirstName", m_FirstName)
                Cmmd.Parameters.AddWithValue("@Title", m_Title)
                Cmmd.Parameters.AddWithValue("@TitleOfCourtesy", m_TitleOfCourtesy)
                Cmmd.Parameters.AddWithValue("@BirthDate", m_BirthDate)
                Cmmd.Parameters.AddWithValue("@HireDate", m_HireDate)
                Cmmd.Parameters.AddWithValue("@Address", m_Address)

                Cmmd.ExecuteNonQuery()

            Catch ex As Exception
                Throw New Exception(ex.Message)
            End Try
        End Using
    End Sub
End Class

這裡面小喵有用了一個小的物件用來存放ConnectionString

Imports Microsoft.VisualBasic

Public Class objConnStr
    Private m_ConnStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;User Instance=True"

    Public ReadOnly Property ConnectionString() As String
        Get
            Return m_ConnStr
        End Get
    End Property

End Class

接著在畫面上安排第二顆按鈕，該按鈕按下後，進行以下的程式碼

        Dim Emp1 As New objEmployee()
        Emp1.EmployeeID = 1
        Emp1.GetEmployeeData()

        Dim Emp9 As New objEmployee(9)

        Using Scope As New TransactionScope
            Try
                Emp1.LastName = Emp1.LastName & "X"
                Emp1.UpdateEmployee()

                Emp9.FirstName = "ABC1234567890"
                Emp9.UpdateEmployee()

                Scope.Complete()
            Catch ex As Exception
                Response.Write(ex.Message)

            End Try
        End Using
        Me.GridView1.DataBind()

從程式碼中可以看到，只要把要呼叫維護的部分，使用TransactionScrope包起來，這樣維護的過程就能夠包成一個Transaction，用來確保內交易內容的完整性。其中第一個部分沒問題，第二個部分會有Exception。所以直到End Using都沒有呼叫Scope.Complete()，因此就把異動的內容Rollback。使用起來真是簡單。

恭喜老爺、賀喜夫人。以往小喵都是用COM+的特別寫法才能夠做到的事情。使用TransactionScope竟然輕鬆的就能夠辦到。這讓使用物件導向設計的人有了很好的解套方式。並更能夠讓物件的行為設計時更能夠獨立處理。例如訂單產生就會動用到【訂單物件】維護OrderHead,OrderDetail，同時透過【倉庫物件】維護機種的【可販數量】這樣的維護過程應該要包在一個Transaction裡面才是。

最後，小喵運作過程中發生了【伺服器...上的MSDTC無法使用...】的訊息，代表主機上的MSDTC的服務沒有開啟，請到【服務】裡面找到【Distributed Transaction Coordinator】將之啟動，即可啟動MSDTC，更細部的設定在【元件服務】裡。

以下是簽名：

歡迎轉貼本站的文章，不過請在貼文主旨上加上【轉貼】，並在文章中附上本篇的超連結與站名【topcat姍舞之間的極度凝聚】，感恩大家的配合。
小喵大部分的文章會以小喵熟悉的語言VB.NET撰寫，如果您需要C#的Code，也許您可以試著用線上的工具進行轉換，這裡提供幾個參考

Microsoft MVP
Visual Studio and Development Technologies
(2005~2019/6)

topcat
Blog:http://www.dotblogs.com.tw/topcat

回首頁

topcat 姍舞之間的極度凝聚

世界上有兩樣東西分享給別人後，不但不會變少，還會變更多：一個是快樂，另一個是知識~分享...是知識累積的開始...

確保交易的新利器(TransactionScope)初體驗-Part 2(物件維護的交易確保)

Microsoft MVP

SkillTree

系列文章

標籤雲

最新留言