TableAdapterにトランザクションを実装
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
投稿日時 : 2007年2月13日 9:23
Tweet
コメントを追加
# グッチ 財布 メンズ 2013年3月11日 7:36 グッチ 財布 メンズ
はじめまして。突然のコメント。失礼しました。# ヴィトン タイガ 2013年3月13日 23:13 ヴィトン タイガ
お世話になります。とても良い記事ですね。