Click here to Skip to main content
15,309,768 members
Articles / Programming Languages / C#
Posted 31 Aug 2007


30 bookmarked

Transaction Aware Table-adapters in .NET 2.0

Rate me:
Please Sign up or sign in to vote.
4.89/5 (16 votes)
5 Dec 2007CPOL3 min read
A quick and elegant way to add transaction capabilities to your table-adapters


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:
orderTableAdapter.Transaction = customerTableAdapter.Transaction;

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

    // ok, all is well, commit transaction:
catch( Exception e )
    // if anything went wrong, roll-back transaction

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().


Date Comment
2007-SEP-15Uploaded 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.


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 Pin
AJMAL SHAHZAD13-Jul-12 20:13
MemberAJMAL SHAHZAD13-Jul-12 20:13 
GeneralNice article Pin
AJMAL SHAHZAD13-Jul-12 20:12
MemberAJMAL SHAHZAD13-Jul-12 20:12 
GeneralTransaction property returning null Pin
SCourt.NET24-Aug-10 10:19
MemberSCourt.NET24-Aug-10 10:19 
Hi everyone

I was trying to use this class, but I found that the property Transaction is returning null. I checked step by step and I found no problems. But when I try to access the property from outside it returns null Cry | :((

Any help will be very appreciated

public bool AgregarAutorizacionPrestamo(Guid IdUsuario,int IdPrestamoSolicitud,DateTime FechaActivacion,double PorcentajeFondoGarantia,bool AplicarIVAInteresOrdinario,
           out string MensajeError)
           MensajeError = string.Empty;
           bool Resultado = false;
           DataSet tablaAmortizacionDataSet = new DataSet();
               if (accesoServicioClass.CompruebaPermisos(IdUsuario, new Guid("ac4b6780-a833-4208-9fb7-9f75e065b3e7"), TipoObjeto.PrestamoAutorizacion, out MensajeError))
                   tablaAmortizacionDataSet = tablaAmortizacionClass.ObtenTablaAmortizacion(IdUsuario, IdPrestamoSolicitud, out MensajeError);

                   int Id = (int)prestamosAutorizacionesTableAdapter.ObtenId();
                   if (prestamosAutorizacionesTableAdapter.InsertarPrestamoAutorizacion(Id, IdPrestamoSolicitud, FechaActivacion, PorcentajeFondoGarantia, AplicarIVAInteresOrdinario,
                       true) > 0)
                           foreach (DataRow drAmortizacion in tablaAmortizacionDataSet.Tables["Resultado"].Rows)
                               if (!tablaAmortizacionClass.AgregarAmortizacion(IdUsuario, Id, Convert.ToInt32(drAmortizacion["NumeroPago"]),
                                   Convert.ToDateTime(drAmortizacion["FechaInicio"]), Convert.ToDateTime(drAmortizacion["FechaVencimiento"]),
                                   Convert.ToDecimal(drAmortizacion["Capital"]), Convert.ToDecimal(drAmortizacion["Interes"]),
                                   Convert.ToDecimal(drAmortizacion["IVAInteres"]), Convert.ToDecimal(drAmortizacion["AbonoCapital"]),
                                   Convert.ToDecimal(drAmortizacion["Pago"]), prestamosAutorizacionesTableAdapter.Transaction, out MensajeError))
                           Resultado = true;
           catch (System.Exception ex)
               MensajeError = ex.Message;
           return Resultado;

When I begin transaction the adapter assign the transaction, but when I try to access it in prestamosAutorizacionesTableAdapter it returns null.
I'm using Microsoft Visual Studio 2010, .net 4.0 Client profile (I've changed and got the same effect).

GeneralRe: Transaction property returning null Pin
SCourt.NET24-Aug-10 10:28
MemberSCourt.NET24-Aug-10 10:28 
GeneralThank you very much!!! Pin
Houman Masnavi ,Phd10-Jun-10 3:49
MemberHouman Masnavi ,Phd10-Jun-10 3:49 
GeneralFabulous work.... Pin
madhuraj00119-May-10 2:45
Membermadhuraj00119-May-10 2:45 
GeneralHi all, Pin
bruce2willis23-Jun-09 7:52
Memberbruce2willis23-Jun-09 7:52 
GeneralThank You Pin
George_Botros19-Mar-09 5:52
MemberGeorge_Botros19-Mar-09 5:52 
GeneralIf anyone is looking for a VB version of this... Pin
egbdfine29-Jan-09 7:03
Memberegbdfine29-Jan-09 7:03 
GeneralRe: If anyone is looking for a VB version of this... Pin
cbankovic18-Aug-10 5:21
Membercbankovic18-Aug-10 5:21 
Generalweird ... Pin
tomtom198011-Nov-08 2:30
Membertomtom198011-Nov-08 2:30 
GeneralRe: weird ... Pin
Mike Pagel11-Nov-08 2:53
MemberMike Pagel11-Nov-08 2:53 
QuestionHelp please on MySql + C# Pin
Seuss18-Sep-08 1:58
MemberSeuss18-Sep-08 1:58 
AnswerRe: Help please on MySql + C# Pin
Tim@Kin15-Dec-08 21:57
MemberTim@Kin15-Dec-08 21:57 
QuestionHelp please on MySQL and C# Pin
Seuss18-Sep-08 0:01
MemberSeuss18-Sep-08 0:01 
AnswerRe: Help please on MySQL and C# Pin
Mike Pagel18-Sep-08 0:34
MemberMike Pagel18-Sep-08 0:34 
QuestionRe: Help please on MySQL and C# Pin
Seuss18-Sep-08 1:07
MemberSeuss18-Sep-08 1:07 
GeneralWorks beautifully Pin
NeedTechHelp13-Aug-08 11:44
MemberNeedTechHelp13-Aug-08 11:44 
Generalpublic vs. private scope on connection Pin
Josh Ryon15-Jul-08 10:31
MemberJosh Ryon15-Jul-08 10:31 
GeneralTransaction with strongly typed dataset and multiple table adapters Pin
Anil Pandya6-Jan-08 23:43
MemberAnil Pandya6-Jan-08 23:43 
GeneralRe: Transaction with strongly typed dataset and multiple table adapters Pin
Mike Pagel7-Jan-08 0:19
MemberMike Pagel7-Jan-08 0:19 
GeneralRe: Transaction with strongly typed dataset and multiple table adapters Pin
Anil Pandya7-Jan-08 17:48
MemberAnil Pandya7-Jan-08 17:48 
GeneralConnection is Public Pin
tmv1912-Sep-07 13:21
Membertmv1912-Sep-07 13:21 
AnswerRe: Connection is Public [modified] Pin
Mike Pagel12-Sep-07 20:26
MemberMike Pagel12-Sep-07 20:26 
GeneralRe: Connection is Public Pin
tmv1913-Sep-07 5:12
Membertmv1913-Sep-07 5:12 

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

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