The Jack's Files

下載Sample Code

分享個以前的小把戲，正常來說，要在VS2005內建的TableAdapter使用Transaction的話，是利用TransactionScope並搭配DTC，但如果不想這麼麻煩，我們可以利用Partial的特性，自己幫TableAdapter加上AdapterTransaction的屬性

下面是在VS2005 Winform下使用DataSet，所自動產生出來的code，如果是ASP.NET的專案，也是會有類似的檔案，不過隱藏起來就是

1.我們現在自己加個小類別，例如叫做UserDataTableAdapter.vb：

2.利用Partial的特性：

3.幫TableAdapter加個AdapterTransaction屬性：

Namespace DataSet1TableAdapters
    Partial Public Class UserDataTableAdapter
        Public _transaction As SqlClient.SqlTransaction
        Public Property AdapterTransaction() As SqlClient.SqlTransaction
            Get

                Return _transaction
            End Get

            Set(ByVal value As SqlClient.SqlTransaction)

                Me._transaction = value
                Me.Connection = _transaction.Connection()

                For Each cmd As SqlClient.SqlCommand In Me.CommandCollection()

                    If cmd IsNot Nothing Then

                        cmd.Connection = _transaction.Connection
                        cmd.Transaction = _transaction
                    End If

                Next



                If Me.Adapter.InsertCommand IsNot Nothing Then

                    Me.Adapter.InsertCommand.Connection = _transaction.Connection()

                    Me.Adapter.InsertCommand.Transaction = _transaction
                End If

                If Me.Adapter.UpdateCommand IsNot Nothing Then

                    Me.Adapter.UpdateCommand.Connection = _transaction.Connection()

                    Me.Adapter.UpdateCommand.Transaction = _transaction
                End If

                If Me.Adapter.DeleteCommand IsNot Nothing Then

                    Me.Adapter.DeleteCommand.Connection = _transaction.Connection()

                    Me.Adapter.DeleteCommand.Transaction = _transaction
                End If

            End Set

        End Property

    End Class

End Namespace

4.實際使用看看：

Public Class Form1



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim conn As New SqlClient.SqlConnection("ConnString")

        Dim tran As SqlClient.SqlTransaction



        Try

            conn.Open()

            tran = conn.BeginTransaction



            Dim tadUserData As New DataSet1TableAdapters.UserDataTableAdapter

            tadUserData.AdapterTransaction = tran



            Dim excuteCnt As Integer = tadUserData.UpdateAddressByID("新住址", "100")



            Throw New Exception("Error")



            tran.Commit()

        Catch ex As Exception

            tran.Rollback()

            MessageBox.Show(ex.Message)

        Finally

            conn.Close()

        End Try



    End Sub

End Class

這樣應該Transaction就能用了，進一步的利用Partial也可以自己加TableAdapter的method進去，例如SelectByFreeSQL之類的，這樣可以自己組where的條件

   Public Function SelectByFreeSQL(ByVal whereSql As String) As DataSet1.UserDataDataTable

            Dim dt As New DataSet1.UserDataDataTable



            Dim sql As String = "SELECT * FROM UserData" & whereSql

            Adapter.SelectCommand = New SqlClient.SqlCommand(sql, Connection)



            If (ClearBeforeFill) Then

                dt.Clear()

            End If



            If AdapterTransaction IsNot Nothing Then

                Adapter.SelectCommand.Transaction = AdapterTransaction

            End If



            Adapter.Fill(dt)



            Return dt

     End Function