Click here to Skip to main content
Click here to Skip to main content

Transaction Aware Table-adapters in .NET 2.0

By , 5 Dec 2007
Rate this:
Please Sign up or sign in to vote.

Introduction

Much has been said about missing support for atomic transactions in .NET 2.0 when using table adapters that get generated by the dataset designer. This article provides a ready-to-use base-class that by injecting it into a table-adapter's inheritance line equips it with transaction capabilities.

Discussion of Approaches

One way to get around the aforementioned limitations is to configure your server to have DTC (Distributed Transaction Coordination) enabled. In that case, you can use TransactionScope instances guarding actions like modifications through table adapters. However, if you don't want to or cannot change the server's configuration, TransactionScope is not an option.

Others on The Code Project and other sites have explained how to enable transactions for table adapters. The basic idea always is to attach an SqlTransaction to all commands of a table-adapter. If multiple table-adapters are used, share the same transaction across all commands of all adapters. What makes this a little difficult is that a generated table-adapter keeps its SqlDataAdapter property Adapter private, but transaction code requires modifying the property.

There are several approaches to solve this issue. For instance Avoiding DTC with Typed DataSets and SQL Server 2000 suggests adding transaction code in the form of partial classes. As the partial extension lives in the same scope as the original table-adapter, it can also access its private properties. The major disadvantage here is that partial classes have to be created for each generated table adapter.

A much more elegant approach is mentioned in this post (unfortunately in German): use reflection to get your hands at those properties. The article also mentions a small detail I found very interesting: when editing a table-adapter in designer-mode, you are actually able to change its base class! So instead of the default base-class Component you can fill in your own base-class. If you now provide such a base-class that is derived from Component and implements the transaction stuff through reflection, usage becomes simple and elegant. And this is exactly what I did: putting together such a handy base-class called TransactionSupport.

Using the Code

To take advantage of the base class is very simple: First download the class source code and drop it into your project. Then, for all tables in your dataset that require transaction support, change the table adapter's base-class like this:

  1. Select the table-adapter (not the table) in the dataset designer, such that it becomes visible in the properties view.

    Screenshot - ta-properties.png

  2. Change the property BaseClass from System.ComponentModel.Component to the class BizWiz.TransactionSupport I provided like is shown in the screenshot.

And that's it. Do this for each table adapter required. Then you can write code of the following pattern:

// begin transaction and share it among all participating table-adapters:
customerTableAdapter.BeginTransaction();
orderTableAdapter.Transaction = customerTableAdapter.Transaction;

// now start the modifications:
try
{
    // do modifications here, throw exceptions if things go wrong...
    // ...

    // ok, all is well, commit transaction:
    customerTableAdapter.CommitTransaction();
}
catch( Exception e )
{
    // if anything went wrong, roll-back transaction
    customerTableAdapter.RollbackTransaction();
}

You can choose any of the participating table-adapters to perform commit or rollback, but I make it good practice to have one dedicated transaction master, that is doing it all: BeginTransaction(), CommitTransaction()and RollbackTransaction().

History

Date Comment
2007-SEP-15 Uploaded modified version of base that looks for Connection property of generated table adapters in public as well as non-public scope, as this seems to differ throughout installations.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Mike Pagel
Software Developer (Senior) BMW AG
Germany Germany
No Biography provided

Comments and Discussions

 
GeneralNice article PinmemberAJMAL SHAHZAD13-Jul-12 20:13 
GeneralNice article PinmemberAJMAL SHAHZAD13-Jul-12 20:12 
GeneralTransaction property returning null PinmemberSCourt.NET24-Aug-10 10:19 
GeneralRe: Transaction property returning null PinmemberSCourt.NET24-Aug-10 10:28 
GeneralThank you very much!!! PinmemberHOUMAN MASNAVI10-Jun-10 3:49 
GeneralFabulous work.... Pinmembermadhuraj00119-May-10 2:45 
GeneralHi all, Pinmemberbruce2willis23-Jun-09 7:52 
GeneralThank You PinmemberGeorge_Botros19-Mar-09 5:52 
GeneralIf anyone is looking for a VB version of this... Pinmemberegbdfine29-Jan-09 7:03 
I found this works fine in VB.NET 2005. Although if you edit the dataset in the designer, the reference to BizWiz gets decorated to Global.BizWiz which breaks the compiler.
 
' ----------------------------------------------------------------------------
'
' 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
 
    '/ <summary>
    '/ Transaction support for generated table adapters.
    '/ </summary>
    '/ <remarks>
    '/ 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.
    '/ </remarks>
    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"
        ' --------------------------------------------------------------------
        '/ <summary>
        '/ Transaction of me table adapter.
        '/ </summary>
        '/ <remarks>
        '/ 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.
        '/ </remarks>
        '/ <example>
        '/ 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()
        '/ }
        '/ </example>
        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"
        ' --------------------------------------------------------------------
        '/ <summary>
        '/ Fields supporting properties.
        '/ </summary>
        Private my_transaction As OleDbTransaction 'SqlTransaction
 
#End Region
        ' --------------------------------------------------------------------
 
    End Class
end namespace

GeneralRe: If anyone is looking for a VB version of this... Pinmembercbankovic18-Aug-10 5:21 
Generalweird ... Pinmembertomtom198011-Nov-08 2:30 
GeneralRe: weird ... PinmemberMike Pagel11-Nov-08 2:53 
QuestionHelp please on MySql + C# PinmemberSeuss18-Sep-08 1:58 
AnswerRe: Help please on MySql + C# PinmemberTim@Kin15-Dec-08 21:57 
QuestionHelp please on MySQL and C# PinmemberSeuss18-Sep-08 0:01 
AnswerRe: Help please on MySQL and C# PinmemberMike Pagel18-Sep-08 0:34 
QuestionRe: Help please on MySQL and C# PinmemberSeuss18-Sep-08 1:07 
Generalpublic vs. private scope on connection PinmemberJosh Ryon15-Jul-08 10:31 
GeneralTransaction with strongly typed dataset and multiple table adapters PinmemberAnil Pandya6-Jan-08 23:43 
GeneralRe: Transaction with strongly typed dataset and multiple table adapters PinmemberMike Pagel7-Jan-08 0:19 
GeneralRe: Transaction with strongly typed dataset and multiple table adapters PinmemberAnil Pandya7-Jan-08 17:48 
GeneralConnection is Public PinmemberTroytTheCodeProject12-Sep-07 13:21 
AnswerRe: Connection is Public [modified] PinmemberMike Pagel12-Sep-07 20:26 
GeneralRe: Connection is Public Pinmembertmv1913-Sep-07 5:12 
AnswerRe: Connection is Public PinmemberMike Pagel14-Sep-07 23:28 
GeneralThe problem with this approach PinmemberMuhahahahahahahahahahahaha10-Sep-07 22:56 
AnswerRe: The problem with this approach PinmemberMike Pagel11-Sep-07 3:16 
GeneralRe: The problem with this approach PinmemberKarel Kral17-Sep-07 3:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 5 Dec 2007
Article Copyright 2007 by Mike Pagel
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid