none
TableAdapterManager and a Shared Transaction RRS feed

  • Question

  • I would like to use a TableAdaperManager in a shared transaction.  What I am after is that another TableAdapter would be responsible for the creation of the transaction.  An update would be call first on this TableAdapter and then later on the TableAdapterManager.  How can I configure the TableAdapterManager to use the same transaction?

    Thanks in advance for your help,
    Terry


    twahl
    Tuesday, July 27, 2010 4:27 AM

Answers

  • Hi Terry,

    You can assign the Transaction created by TableAdapter to all the TableAdapters associated with the TableAdapterManager. For example: (Suppose OrdersTableAdapter is associated with the TableAdapterManager)

    Partial Class OrdersTableAdapter
    
      Public Sub AssignConnection(ByVal conn As SqlClient.SqlConnection, _
    Optional ByVal trans As SqlClient.SqlTransaction = Nothing)
    
       Me.Connection = conn
    
       If trans IsNot Nothing Then
        Me.Adapter.InsertCommand.Transaction = trans
        Me.Adapter.DeleteCommand.Transaction = trans
        Me.Adapter.UpdateCommand.Transaction = trans
       End If
    
       Me.Adapter.AcceptChangesDuringUpdate = False
       Me.Adapter.ContinueUpdateOnError = False
      End Sub
    
     End Class
    
    



    Assign the Transaction previously created by calling AssignConnection() with connection. 

    Dim conn As New SqlClient.SqlConnection(My.Settings.NorthwindConnectionString)
    Dim trans As SqlClient.SqlTransaction = Nothing
     
    conn.Open()
    trans = conn.BeginTransaction
     
    Me.OrdersTableAdapter.AssignConnection(conn, trans)
    
    

     

    And assign the OrdersTableAdapter to TableAdapterManager’s corresponding TableAdapter.

    Dim manager As New OrdersDataSetTableAdapters.TableAdapterManager   
    manager.OrdersTableAdapter = Me.OrdersTableAdapter
    
    

     

    In addtion, you can refer to the following links which contain some articles written by Beth Massi, talking about
    using TableAdapterManager to update related tables in a transaction.

    http://blogs.msdn.com/b/bethmassi/archive/2007/07/11/tableadapters-and-transactions.aspx

    http://blogs.msdn.com/b/bethmassi/archive/2008/01/07/the-new-tableadaptermanager-in-visual-studio-2008.aspx

     

     

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com




    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, July 27, 2010 8:15 AM
    Moderator

All replies

  • Hi Terry,

    You can assign the Transaction created by TableAdapter to all the TableAdapters associated with the TableAdapterManager. For example: (Suppose OrdersTableAdapter is associated with the TableAdapterManager)

    Partial Class OrdersTableAdapter
    
      Public Sub AssignConnection(ByVal conn As SqlClient.SqlConnection, _
    Optional ByVal trans As SqlClient.SqlTransaction = Nothing)
    
       Me.Connection = conn
    
       If trans IsNot Nothing Then
        Me.Adapter.InsertCommand.Transaction = trans
        Me.Adapter.DeleteCommand.Transaction = trans
        Me.Adapter.UpdateCommand.Transaction = trans
       End If
    
       Me.Adapter.AcceptChangesDuringUpdate = False
       Me.Adapter.ContinueUpdateOnError = False
      End Sub
    
     End Class
    
    



    Assign the Transaction previously created by calling AssignConnection() with connection. 

    Dim conn As New SqlClient.SqlConnection(My.Settings.NorthwindConnectionString)
    Dim trans As SqlClient.SqlTransaction = Nothing
     
    conn.Open()
    trans = conn.BeginTransaction
     
    Me.OrdersTableAdapter.AssignConnection(conn, trans)
    
    

     

    And assign the OrdersTableAdapter to TableAdapterManager’s corresponding TableAdapter.

    Dim manager As New OrdersDataSetTableAdapters.TableAdapterManager   
    manager.OrdersTableAdapter = Me.OrdersTableAdapter
    
    

     

    In addtion, you can refer to the following links which contain some articles written by Beth Massi, talking about
    using TableAdapterManager to update related tables in a transaction.

    http://blogs.msdn.com/b/bethmassi/archive/2007/07/11/tableadapters-and-transactions.aspx

    http://blogs.msdn.com/b/bethmassi/archive/2008/01/07/the-new-tableadaptermanager-in-visual-studio-2008.aspx

     

     

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com




    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, July 27, 2010 8:15 AM
    Moderator
  • Hi Terry,

    I'm writing to follow up the post. Does the above suggestion work?

    Please feel free to let me know if you need any help.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, July 31, 2010 6:20 AM
    Moderator
  • Hi Terry,

    I am marking my reply as answer. If you have other questions, please feel free to let me know.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, August 3, 2010 2:34 AM
    Moderator
  • Hi Alex,
    Unless there is a way to set some option in the way C# designer code is generated I do not think using shared transactions with at TableAdapterManager is possible in C#.   

    It looks like the problem is in the way the designer code is generated.  If you look at the designer code below (assume that I have attempted to set the transaction using by the time this code is executed) there is no check to see if a transaction is already in progress.  The code (last line) simply issues a BeginTransaction() which results in a ‘Parallel transation not supported exception’.  I checked the generated code in VS 2010 and I’m seeing the same thing.  In essense it looks like the code generated for the TableAdapterManager operates as an island.  It handles all transaction management (creation/commit/rollback).  So it looks like if you need shared transactions the TableAdapterManager is not the ticket.

     

    Thanks,

    Terry Wahl

            /// <summary>

            ///Update all changes to the dataset.

            ///</summary>

            [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]

            public virtual int UpdateAll(TcmCcyDs dataSet) {

                if ((dataSet == null)) {

                    throw new global::System.ArgumentNullException("dataSet");

                }

                if ((dataSet.HasChanges() == false)) {

                    return 0;

                }

                if (((this._tcmCcyTableAdapter != null)

                            && (this.MatchTableAdapterConnection(this._tcmCcyTableAdapter.Connection) == false))) {

                    throw new global::System.ArgumentException("All TableAdapters managed by a TableAdapterManager must use the same connection s" +

                            "tring.");

                }

                global::System.Data.IDbConnection workConnection = this.Connection;

                if ((workConnection == null)) {

                    throw new global::System.ApplicationException("TableAdapterManager contains no connection information. Set each TableAdapterMana" +

                            "ger TableAdapter property to a valid TableAdapter instance.");

                }

                bool workConnOpened = false;

                if (((workConnection.State & global::System.Data.ConnectionState.Broken)

                            == global::System.Data.ConnectionState.Broken)) {

                    workConnection.Close();

                }

                if ((workConnection.State == global::System.Data.ConnectionState.Closed)) {

                    workConnection.Open();

                    workConnOpened = true;

                }

                global::System.Data.IDbTransaction workTransaction = workConnection.BeginTransaction();

              ...

     


    twahl
    Tuesday, August 3, 2010 5:54 PM
  • Hi Terry,

    TableAdapterManager explicitly creates and commits transaction. If you want to use the transaction you previously created by a TableAdapter, as my first post mentioned, I think you can assign the Transaction created by TableAdapter to all the TableAdapters associated with the TableAdapterManager. Add use these TableAdapters(which associated with the TableAdapterManager) to update their corresponding table instead of using the TableAdapterManager’s UpdateAll method. In this way, all the TableAdapters’ update operations are within the same Transaction. The functionality of it is the similar to using the TableAdapterManager’s UpdateAll method.

    For example (Suppose OrdersTableAdapter and DetailsTableAdapter is associated with the TableAdapterManager) :

    Partial Class OrdersTableAdapter
    
      Public Sub AssignConnection(ByVal conn As SqlClient.SqlConnection, _
    Optional ByVal trans As SqlClient.SqlTransaction = Nothing)
    
       Me.Connection = conn
    
       If trans IsNot Nothing Then
        Me.Adapter.InsertCommand.Transaction = trans
        Me.Adapter.DeleteCommand.Transaction = trans
        Me.Adapter.UpdateCommand.Transaction = trans
       End If
    
       Me.Adapter.AcceptChangesDuringUpdate = False
       Me.Adapter.ContinueUpdateOnError = False
      End Sub
    
     End Class
    
     Partial Class Order_DetailsTableAdapter
    
      Public Sub AssignConnection(ByVal conn As SqlClient.SqlConnection, _
    Optional ByVal trans As SqlClient.SqlTransaction = Nothing)
    
       Me.Connection = conn
    
       If trans IsNot Nothing Then
        Me.Adapter.InsertCommand.Transaction = trans
        Me.Adapter.DeleteCommand.Transaction = trans
        Me.Adapter.UpdateCommand.Transaction = trans
       End If
    
       Me.Adapter.AcceptChangesDuringUpdate = False
       Me.Adapter.ContinueUpdateOnError = False
      End Sub
    
     End Class
    
    

     


    Then assign the Transaction you want to use to the TableAdapters associated with TableAdapterManager by calling
    AssignConnection() with connection.

     

      'Suppose “trans” is the transaction you want to use, and the “conn” is the connection to database
      Me.OrdersTableAdapter.AssignConnection(conn, trans)
      Me.Order_DetailsTableAdapter.AssignConnection(conn, trans)
    
    



    Then use these TableAdapters to update their corresponding table instead of using TableAdapterManager’s UpdateAll method.

    Me.OrdersTableAdapter.Update(Me.NorthWindDataSet.Orders)
    Me.Order_DetailsTableAdapter.Update(Me.NorthWindDataSet.Orders_Details)
    
    


    You can refer to completely example from http://blogs.msdn.com/b/bethmassi/archive/2007/07/11/tableadapters-and-transactions.aspx


    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com



    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, August 4, 2010 7:54 AM
    Moderator