TableAdapterでTransactionを行うには、TransactionScopeを利用するのが一番簡単だと思いますけど、みなさんはTableAdapterでTransactionを行うのをどのようにされていますか？
複数のTableAdapterにトランザクションを実装したりするのも・・・
以下のようなクラスを作ってテスト中・・・
Imports System.Data.SqlClient
Imports System.Reflection
''' <summary>
''' TableAdapterにトランザクション機能を実装するクラス
''' </summary>
''' <remarks>
''' 利用方法の例
''' Dim tat As New TableAdapterTransactor
''' Dim taData1 As New Data1TableAdapter
''' Dim taData2 As New Data2TableAdapter
'''
''' tat.AddTableAdapter(taData1)
''' tat.AddTableAdapter(taData2)
''' tat.BeginTransaction()
''' Try
''' taData1.Insert......
''' taData2.Insert......
''' tat.Commit()
''' Catch ex As Exception
''' tat.Rollback()
''' End Try
''' </remarks>
Public Class TableAdapterTransactor
Private _conn As SqlConnection = Nothing
Private _trans As SqlTransaction = Nothing
Private _tableAdapters As New List(Of Object)
#Region "Pプロシージャ - GetConnection [TableAdapterのConnectionを取得する]"
''' <summary>
''' TableAdapterのConnectionを取得する
''' </summary>
''' <param name="tableAdapter">TableAdapter</param>
''' <returns>取得したConnection</returns>
''' <remarks></remarks>
Private Function GetConnection(ByVal tableAdapter As Object) As SqlConnection
Dim type As Type = tableAdapter.GetType()
Dim connectionProperty As PropertyInfo = _
type.GetProperty("Connection", BindingFlags.NonPublic Or BindingFlags.Instance)
Dim connection As SqlConnection = _
CType(connectionProperty.GetValue(tableAdapter, Nothing), SqlConnection)
Return connection
End Function
#End Region
#Region "Pプロシージャ - SetConnection [TableAdapterのConnectionを設定する]"
''' <summary>
''' TableAdapterのConnectionを設定する
''' </summary>
''' <param name="tableAdapter">TableAdapter</param>
''' <param name="connection">Connection</param>
''' <remarks></remarks>
Private Sub SetConnection(ByVal tableAdapter As Object, ByVal connection As SqlConnection)
Dim type As Type = tableAdapter.GetType()
Dim connectionProperty As PropertyInfo = _
type.GetProperty("Connection", BindingFlags.NonPublic Or BindingFlags.Instance)
connectionProperty.SetValue(tableAdapter, connection, Nothing)
End Sub
#End Region
#Region "Pプロシージャ - GetAdapter [TableAdapterのDataAdapterを取得する]"
''' <summary>
''' TableAdapterのDataAdapterを取得する
''' </summary>
''' <param name="tableAdapter">TableAdapter</param>
''' <returns>取得したDataAdapter</returns>
''' <remarks></remarks>
Private Function GetAdapter(ByVal tableAdapter As Object) As SqlDataAdapter
Dim type As Type = tableAdapter.GetType()
Dim adapterProperty As PropertyInfo = _
type.GetProperty("_adapter", BindingFlags.NonPublic Or BindingFlags.Instance)
Dim adapter As SqlDataAdapter = _
CType(adapterProperty.GetValue(tableAdapter, Nothing), SqlDataAdapter)
Return adapter
End Function
#End Region
#Region "Pプロシージャ - SetAdapter [TableAdapterのDataAdapterを設定する]"
''' <summary>
''' TableAdapterのDataAdapterを設定する
''' </summary>
''' <param name="tableAdapter">TableAdapter</param>
''' <param name="adapter">DataAdapter</param>
''' <remarks></remarks>
Private Sub SetAdapter(ByVal tableAdapter As Object, ByVal adapter As SqlDataAdapter)
Dim type As Type = tableAdapter.GetType()
Dim adapterProperty As PropertyInfo = _
type.GetProperty("_adapter", BindingFlags.NonPublic Or BindingFlags.Instance)
adapterProperty.SetValue(tableAdapter, adapter, Nothing)
End Sub
#End Region
#Region "Pプロシージャ - InitAdapter [TableAdapterのInitAdapterメソッドを呼び出す]"
''' <summary>
''' TableAdapterのInitAdapterメソッドを呼び出す
''' </summary>
''' <param name="tableAdapter">TableAdapter</param>
''' <remarks>利用しない</remarks>
Private Sub InitAdapter(ByVal tableAdapter As Object)
Dim type As Type = tableAdapter.GetType()
Dim mi As MethodInfo = _
type.GetMethod("InitAdapter", BindingFlags.NonPublic Or BindingFlags.Instance)
mi.Invoke(tableAdapter, Nothing)
End Sub
#End Region
#Region "Pプロシージャ - InitCommandCollection [TableAdapterのInitCommandCollectionメソッドを呼び出す]"
''' <summary>
''' TableAdapterのInitCommandCollectionメソッドを呼び出す
''' </summary>
''' <param name="tableAdapter">TableAdapter</param>
''' <remarks>利用しない</remarks>
Private Sub InitCommandCollection(ByVal tableAdapter As Object)
Dim type As Type = tableAdapter.GetType()
Dim mi As MethodInfo = _
type.GetMethod("InitCommandCollection", BindingFlags.NonPublic Or BindingFlags.Instance)
mi.Invoke(tableAdapter, Nothing)
End Sub
#End Region
#Region "Pプロシージャ - SetTransactionCommands [TableAdapterのCommandCollectionのTransactionを設定する]"
''' <summary>
''' TableAdapterのCommandCollectionのTransactionを設定する
''' </summary>
''' <param name="tableAdapter">TableAdapter</param>
''' <param name="transaction">Transaction</param>
''' <remarks></remarks>
Private Sub SetTransactionCommands(ByVal tableAdapter As Object, ByVal transaction As SqlTransaction)
Dim type As Type = tableAdapter.GetType()
Dim commandsProperty As PropertyInfo = _
type.GetProperty("CommandCollection", BindingFlags.NonPublic Or BindingFlags.Instance)
Dim commands As SqlCommand() = _
CType(commandsProperty.GetValue(tableAdapter, Nothing), SqlCommand())
For Each command As SqlCommand In commands
command.Transaction = transaction
Next
Me.SetConnection(tableAdapter, transaction.Connection)
End Sub
#End Region
#Region "Pプロシージャ - SetTransactionAdapter [TableAdapterのDataAdapterのTransactionを設定する]"
''' <summary>
''' TableAdapterのDataAdapterのTransactionを設定する
''' </summary>
''' <param name="tableAdapter">TableAdapter</param>
''' <param name="transaction">Transaction</param>
''' <remarks></remarks>
Private Sub SetTransactionAdapter(ByVal tableAdapter As Object, ByVal transaction As SqlTransaction)
Dim adp As SqlDataAdapter = Me.GetAdapter(tableAdapter)
If adp.InsertCommand IsNot Nothing Then
adp.InsertCommand.Transaction = transaction
End If
If adp.DeleteCommand IsNot Nothing Then
adp.DeleteCommand.Transaction = transaction
End If
If adp.UpdateCommand IsNot Nothing Then
adp.UpdateCommand.Transaction = transaction
End If
Me.SetAdapter(tableAdapter, adp)
'Me.InitCommandCollection(tableAdapter)
End Sub
#End Region
#Region "メソッド - AddTableAdapter [TableAdapterを追加する]"
''' <summary>
''' TableAdapterを追加する
''' </summary>
''' <param name="tableAdapter">TableAdapter</param>
''' <remarks></remarks>
Public Sub AddTableAdapter(ByVal tableAdapter As Object)
Me._tableAdapters.Add(tableAdapter)
End Sub
#End Region
#Region "メソッド - BeginTransaction [トランザクションを開始する]"
''' <summary>
''' トランザクションを開始する
''' </summary>
''' <remarks>開始する前に、AddTableAdapterメソッドで対象となるTableAdapterを追加すること</remarks>
Public Sub BeginTransaction()
Dim counter As Integer = 0
For Each adapter As Object In Me._tableAdapters
counter += 1
If counter = 1 Then
Me._conn = Me.GetConnection(adapter)
If Me._conn.State <> ConnectionState.Open Then
Me._conn.Open()
End If
Me._trans = Me._conn.BeginTransaction()
End If
Me.SetConnection(adapter, Me._conn)
Me.SetTransactionAdapter(adapter, Me._trans)
Me.SetTransactionCommands(adapter, Me._trans)
Next
End Sub
''' <summary>
''' トランザクションを開始する
''' </summary>
''' <param name="conn">コネクション</param>
''' <remarks></remarks>
Public Sub BeginTransaction(ByVal conn As SqlConnection)
Me._conn = conn
If Me._conn.State <> ConnectionState.Open Then
Me._conn.Open()
End If
Me._trans = Me._conn.BeginTransaction()
For Each adapter As Object In Me._tableAdapters
Me.SetConnection(adapter, Me._conn)
Me.SetTransactionAdapter(adapter, Me._trans)
Me.SetTransactionCommands(adapter, Me._trans)
Next
End Sub
''' <summary>
''' トランザクションを開始する
''' </summary>
''' <param name="trans">トランザクション</param>
''' <remarks></remarks>
Public Sub BeginTransaction(ByVal trans As SqlTransaction)
Me._conn = trans.Connection
Me._trans = trans
For Each adapter As Object In Me._tableAdapters
Me.SetConnection(adapter, Me._conn)
Me.SetTransactionAdapter(adapter, Me._trans)
Me.SetTransactionCommands(adapter, Me._trans)
Next
End Sub
#End Region
#Region "メソッド - Commit [トランザクションをコミットする]"
''' <summary>
''' トランザクションをコミットする
''' </summary>
''' <remarks></remarks>
Public Sub Commit()
Try
Me._trans.Commit()
Catch ex As Exception
Me._trans.Rollback()
Throw ex
Finally
If (Me._conn.State = ConnectionState.Open) Then
Me._conn.Close()
End If
End Try
End Sub
#End Region
#Region "メソッド - Rollback [トランザクションをロールバックする]"
''' <summary>
''' トランザクションをロールバックする
''' </summary>
''' <remarks></remarks>
Public Sub Rollback()
Me._trans.Rollback()
If (Me._conn.State = ConnectionState.Open) Then
Me._conn.Close()
End If
End Sub
#End Region
End Class