Transaction Aware Table-adapters in .NET 2.0

' ----------------------------------------------------------------------------
'
' TransactionSupport
'
' Original author: Mike Pagel
' After ideas given in
'     http:'www.codeproject.com/useritems/typed_dataset_transaction.asp
'     http:'entwickler-forum.de/showpost.php?p=2032&postcount=2
'
'  Translated to VB and converted to OLEDB for use with MS ACCESS  (Jet) by Jim Hansen, Jan 2009
' ----------------------------------------------------------------------------

'imports System.Data.SqlClient
Imports System.Data.OleDb
imports System.Data
imports System.Reflection

namespace BizWiz

    '/ 

    '/ Transaction support for generated table adapters.
    '/ 

    '/ 
    '/ me class adds transaction support to table adapters. It is used by changing the base
    '/ class of a table adapter from Component to me class. The implementation of me class
    '/ then accesses the derived table adapter's properties through reflection.
    '/ 
    Public MustInherit Class TransactionSupport
        Inherits System.ComponentModel.Component

        ' --------------------------------------------------------------------
#Region "Reflective access to table adapter properties"
        ' --------------------------------------------------------------------
        Private Property Connection() As OleDbConnection 'SqlConnection

            ' Access to propertis as public and non-public as generated table-adapter
            ' scope seems to be different for different installations:
            ' http:'www.codeproject.com/useritems/transactionta.asp?msg=2225021#xx2225021xx
            Get
                'return (SqlConnection)GetType().GetProperty( "Connection", BindingFlags.Public or BindingFlags.NonPublic or BindingFlags.Instance ).GetValue( me, nothing )
                Return Me.GetType().GetProperty("Connection", BindingFlags.Public Or BindingFlags.NonPublic Or BindingFlags.Instance).GetValue(Me, Nothing)
            End Get
            Set(ByVal value As OleDbConnection) ' SqlConnection)
                Me.GetType().GetProperty("Connection", BindingFlags.Public Or BindingFlags.NonPublic Or BindingFlags.Instance).SetValue(Me, value, Nothing)
            End Set
        End Property

        ' --------------------------------------------------------------------
        Private ReadOnly Property Adapter() As OleDbDataAdapter  'SqlDataAdapter

            Get

                Return Me.GetType().GetProperty("Adapter", BindingFlags.NonPublic Or BindingFlags.Instance).GetValue(Me, Nothing)
            End Get
        End Property

        ' --------------------------------------------------------------------
        Private ReadOnly Property CommandCollection() As OleDbCommand() 'SqlCommand()

            Get

                Return Me.GetType().GetProperty("CommandCollection", BindingFlags.NonPublic Or BindingFlags.Instance).GetValue(Me, Nothing)
            End Get
        End Property

#End Region
        ' --------------------------------------------------------------------
#Region "Properties"
        ' --------------------------------------------------------------------
        '/ 

        '/ Transaction of me table adapter.
        '/ 

        '/ 
        '/ me property is used to share a transaction and its associated connection
        '/ across multiple table adapters. The typical pattern is shown in the sample
        '/ code below.
        '/ 
        '/ 
        '/ XTableAdapter xta = new XTableAdapter()
        '/ YTableAdapter yta = new YTableAdapter()
        '/ 
        '/ xta.BeginTransation()
        '/ yta.Transation = xta.Transaction
        '/ try
        '/ {
        '/     ' perform xta and yta modifications here.
        '/     xta.CommitTransaction()
        '/ }
        '/ catch( Exception )
        '/ {
        '/     xta.RollbackTransaction()
        '/ }
        '/ 
        Public Property Transaction() As OleDbTransaction 'SqlTransaction

            Get
                Return my_transaction
            End Get
            Set(ByVal value As OleDbTransaction) ' SqlTransaction)

                ' attach transaction to all commands of my adapter:
                If CommandCollection IsNot Nothing Then
                    For Each command As OleDbCommand In CommandCollection 'SqlCommand In CommandCollection
                        command.Transaction = value
                    Next 'command
                End If
                If Adapter.InsertCommand IsNot Nothing Then
                    Adapter.InsertCommand.Transaction = value
                End If
                If Adapter.UpdateCommand IsNot Nothing Then
                    Adapter.UpdateCommand.Transaction = value
                End If
                If Adapter.DeleteCommand IsNot Nothing Then
                    Adapter.DeleteCommand.Transaction = value
                End If

                ' also set connection of my adapter accordingly:
                If value IsNot Nothing Then
                    Connection = value.Connection
                Else

                    ' only clear connection if it was attached to
                    ' transaction before:
                    If (Transaction IsNot Nothing) Then
                        Connection = Nothing
                    End If
                End If


                my_transaction = value
            End Set
        End Property

#End Region
        ' --------------------------------------------------------------------
#Region "Operations"
        ' --------------------------------------------------------------------
        Public Sub BeginTransaction()

            ' Open the connection, if needed
            If (Connection.State <> ConnectionState.Open) Then
                Connection.Open()
            End If
            ' Create the transaction and assign it to the Transaction property
            Transaction = Connection.BeginTransaction()
        End Sub

        ' --------------------------------------------------------------------
        Public Sub CommitTransaction()

            ' Commit the transaction
            my_transaction.Commit()

            ' Close the connection
            Connection.Close()
        End Sub

        ' --------------------------------------------------------------------
        Public Sub RollbackTransaction()

            ' Rollback the transaction
            my_transaction.Rollback()

            ' Close the connection
            Connection.Close()
        End Sub

#End Region
        ' --------------------------------------------------------------------
#Region "Fields"
        ' --------------------------------------------------------------------
        '/ 

        '/ Fields supporting properties.
        '/ 

        Private my_transaction As OleDbTransaction 'SqlTransaction

#End Region
        ' --------------------------------------------------------------------

    End Class
end namespace

public SqlTransaction Transaction
{
    set
    {                
        // attach transaction to all commands of this adapter:
        if( CommandCollection != null )
        {
            foreach( SqlCommand command in CommandCollection )
            {
                command.Transaction = value;
            }
        }
        if( Adapter.InsertCommand != null )
        {
            Adapter.InsertCommand.Transaction = value;
        }
        if( Adapter.UpdateCommand != null )
        {
            Adapter.UpdateCommand.Transaction = value;
        }
        if( Adapter.DeleteCommand != null )
        {
            Adapter.DeleteCommand.Transaction = value;
        }
 
        ...
    }
}