Transaction Aware Table-adapters in .NET 2.0

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.

Presents a good concept of transaction in type Table-adapters. Thumbs Up | :thumbsup:

Present a good concept of transaction in type Table-adapters. Thumbs Up | :thumbsup:

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();
           try
           {
               if (accesoServicioClass.CompruebaPermisos(IdUsuario, new Guid("ac4b6780-a833-4208-9fb7-9f75e065b3e7"), TipoObjeto.PrestamoAutorizacion, out MensajeError))
               {
                   tablaAmortizacionDataSet = tablaAmortizacionClass.ObtenTablaAmortizacion(IdUsuario, IdPrestamoSolicitud, out MensajeError);
                   prestamosAutorizacionesTableAdapter.BeginTransaction();

                   int Id = (int)prestamosAutorizacionesTableAdapter.ObtenId();
                   if (prestamosAutorizacionesTableAdapter.InsertarPrestamoAutorizacion(Id, IdPrestamoSolicitud, FechaActivacion, PorcentajeFondoGarantia, AplicarIVAInteresOrdinario,
                       true) > 0)
                   {
                       if(MensajeError==String.Empty)
                       {
                           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))
                                   prestamosAutorizacionesTableAdapter.RollbackTransaction();
                           }
                           prestamosAutorizacionesTableAdapter.CommitTransaction();
                           Resultado = true;
                       }
                   }
               }
           }
           catch (System.Exception ex)
           {
               MensajeError = ex.Message;
           }
           finally
           {
               tablaAmortizacionDataSet.Dispose();
           }
           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).

By the way, the compiler show this warning

<br />
Warning	2	'ilink.DataSets.PrestamoSolicitudesDataSetTableAdapters.PrestamoSolicitudesTableAdapter.Transaction' hides inherited member 'BizWiz.TransactionSupport.Transaction'. Use the new keyword if hiding was intended.	G:\Proyectos\Proehsa\FAIFAP\FAIFAP\ilink\ilink\DataSets\PrestamoSolicitudesDataSet.Designer.cs	983	63	ilink<br />

Thank you very much!!!
Your Article helped me a lot!!!
I have changed it to a .NET CLASS LIBRARY with your Name.
Then I Easily added a reference to it!
I named it LazyComp Sleepy | :zzz:

for my students!

Dear Mike Pagel,

This is what i was really looking for.
You have done really a great job. Please accept my heartiest thanks.

I came across your code and I was in the middle of implementing your code.

But for shared transaction, the rollback was not initiated fully

/// xta.BeginTransation();
/// yta.Transation = xta.Transaction;
/// try
/// {
/// // perform xta and yta modifications here.
/// xta.CommitTransaction();
/// }
/// catch( Exception )
/// {
/// xta.RollbackTransaction();
/// }

In this eg: only rollback works for xta. How can I have the rollback for yta ?

Thank You
You Saved Me from Writing dozens Of Code

George Batres

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

Is there anyway to fix the problem with the "global.bizwiz" problem? I'm searching Google and have come up empty so far.

whenever i share an open connection with multiple TableAdapters the TM stays LTM and won't get promoted to MSDTC.
i'm using VS 2k8 with .net 3.5 + SQL 2k5 and can't really reproduce the behavior mentioned in the first passage, that is, i'm using multiple TableAdapters within a TransactionScopes without having my TM escalated. any thoughts on that? probably something MS changed between the .net versions?

I have not tried this for .NET 3.5. Maybe another user did...

What you put:

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

// now start the modifications:
try
{
WHAT GOES IN HERE???
customerTableAdapter.Insert("blob"); <----------- it still inserts

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

The Transaction used in the Topic is the Transaction property of TableAdapter. No the property of TransactionSupport

The handle this, i think we can rename the Transaction get setter in TransactionSupport.cs such as 'SharedTransaction'

Finally , we can:
orderTableAdapter.SharedTransaction=customerTableAdapter.SharedTransaction

Tim

I am using MySQLConnector.Net 5.2.1, SQLYog CommunityEdition v6.16, VS 2005 (C#).

I have created several table adapters using XSD Wizard and I am not using insertcommand, updatecommand, selectcommand and deletecommand. I have just
created a delete SQL text through the XSD wizard for the table adapters.

However, I used your transactionsupport class and I still cannot get it to work and it still deletes and never does a rollback.

Do you have any ideas or suggestions on how the coding in C# should be done to make the rollback occur for MySql Database?

I would appreciate your help as I am struggling to find an answer to this. Cry | :((

Thanks

Hi,

I have no experience with the connectors you mention. However, the transaction used in my class is attached to all default commands of the table adapter. I suspect that this does not happen for your literal SQL statement.

Take a look at the TransactionSupport.Transaction property. Its setter is currently implemented as:

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;
        }
 
        ...
    }
}

Put a breakpoint in there. Is your custom command also modified by setting its Transaction property?

If you find out what's going on, please let me know. I am happy to update the base class.

Mike

Hi
I am not sure what you mean by:

"Is your custom command also modified by setting its Transaction property?"

Can you please explain?

Thanks

Many thanks for providing this class. I added it in my project & changed the base class for my tableadapters & it all works like a charm. The other solutions would have required me to enable network access to DTC/enable the MSDTC service on the server, but the solution you posted avoided all that & still works fine. Thanks again Smile | :)

First, thanks for the code. I was not looking forward to the copy/pasting the transaction property for 40 different adapters in my project. This really helped me out Wink | ;)

Second, I believe the problem with the scope on the connection object (thread here) has to do with how the adapter is set up. In the designer, if you check the properties on an adapter, you can change the scope of the connection by changing the "ConnectionModifier". My guess would be that the person in the thread below here had his defined as public, while the author had his defined as internal, protected, or private.

Regardless, the fix to make the code work for everybody is to change the binding flags on the reflection as the author did. Nice work, and thanks again.

if you want to implement transaction with strongly typed dataset it is some what trikey,

Table adapters which are created in typed datasets are implemented in partial classes by visual studio 2005,

if you want to use multiple table adapters in one transaction than you have to follow this sceanario.....

implement partial classes foR all your table adapters like this use onky one connection object for all, (assign new connection to first adapter and to other pass just reference of that connection)

Partial classes are used to extend the functionality provided by TableAdapters. They are to be written in “App_Code” folder. In partial classes, the physical files are scattered any where in the project with the same name like “public partial class <classname>” but when project is compiled all the physical files are combined like one physical file and make the whole class

Example of a partial class

using System;
using System.Data;
using System.Data.SqlClient;
namespace dsGroundSelectTableAdapters
{
public partial class ADAPTERNAME_TableAdapter
{
private SqlTransaction _transaction;
private SqlTransaction Transaction {
get
{ return this._transaction; }
set {
this._transaction = value; }
}
public SqlConnection GetConnection()
{
return this.Connection;
}
public void SetConnection(ref SqlConnection Conn)
{
this.Connection = Conn;
}
public SqlTransaction GetTransaction()
{
return this.Transaction;
}
public void SetTransaction(ref SqlTransaction Trans)
{
this.Transaction = Trans;
foreach (SqlCommand command in this.CommandCollection)
{
command.Transaction = this.Transaction;
}
this.Adapter.InsertCommand.Transaction = this.Transaction;
}
public void BeginTransaction()
{
// Open the connection, if needed
if (this.Connection.State != ConnectionState.Open)
{
this.Connection.Open();
}
// Create the transaction and assign it to the Transaction property
this.Transaction = this.Connection.BeginTransaction();
// Attach the transaction to the Adapters
foreach (SqlCommand command in this.CommandCollection)
{
command.Transaction = this.Transaction;
}
this.Adapter.InsertCommand.Transaction = this.Transaction;
}
public void CommitTransaction()
{
// Commit the transaction
this.Transaction.Commit();
// Close the connection
this.Connection.Close();
}
public void RollbackTransaction()
{
// Rollback the transaction
this.Transaction.Rollback();
// Close the connection
this.Connection.Close();
}
}}

************************
and in code behind file

//////create object of all your adapters

(1) dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter daOBJECT1 = new dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter();

(2) dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter daOBJECT2 = new dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter();

(3) dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter daOBJECTn = new dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter();

/////////create connection
///////// assign it to first adapter object and give others a refernce ////////to the same

/////////all operation must be performed using first adapter object

//even they all are refernced by a same connectin they all are in one ///////transaction...

SqlConnection Conn= new SqlConnection();
Conn=daOBJECT1.GetConnection();
daOBJECT2.SetConnection(ref Conn);
daOBJECTn.SetConnection(ref Conn);

daOBJECT1.BeginTransaction();

SqlTransaction trnTrans = daOBJECT1.GetTransaction();
daOBJECT2.SetTransaction(ref trnTmpTrans);
daOBJECTn.SetTransaction(ref trnTmpTrans);
try
{
//EXECUTE YOUR QUERIES HERE
// YOU CAN CALL FUNCTIONS TO UPDATE INSERT DATA
//insertDATAInTable();
//updateDATATable();

daOBJECT1.CommitTransaction();
}
catch (Exception ex)
{
daOBJECT1.RollbackTransaction();
}

***********************************
Anil Pandya
Senior Software Programmer
Mumbai
+919920066603

anil

Thanks for the comment, but I am not quite sure what you want to say... Do you have an issue with my implementation that you suggest to solve in a specific way?

Mike

i was having problem with transaction...
but i found a solutions in proper way and implemented with my whole project. so i have just described it to public

Anil Pandya
Programmer Mumbai- India

In order for this to work for me, I had to change the NonPublic BindingFlags to Public in the Connection property Set & Get.

I am wondering why it behaves differently on your machine... You said you changed it from NonPublic to Public. I remember that did not work for me, though.

Could you try combining the two as in NonPublic | Public (or did you actually already do it that way)? That should work for both of us. If you could post quick feedback I will upload a modified version afterwards.

Thanks a lot,
Mike

-- modified at 2:36 Thursday 13th September, 2007

For some reason my Connection property is public in my table adapters outside the BaseClass. I did create my DataTable and Adapters within an XSD DataSet via the Toolbox wizard.

The combination of the two BindingFlags worked as expected.

Transaction Aware Table-adapters in .NET 2.0

Introduction

Discussion of Approaches

Using the Code

History

License

Comments and Discussions