Click here to Skip to main content
11,648,436 members (79,694 online)
Click here to Skip to main content

Using A Single Transaction With Multiple Typed DataSets

, 4 May 2008 CPOL 36.6K 24
Rate this:
Please Sign up or sign in to vote.
Provides a detailed explanation of how to implement a single transaction across multiple Typed Datasets


If you have ever used Typed DataSets, you know how fast and easy they can make database access programming. The only drawback I ever ran into while using them is when I needed to put actions associated with several different Typed DataSets into a single Transaction.


Transactions and Typed DataSets are nothing new. However, when you want to place Updates to more than one database table through different Typed DataSets into a single transaction, things get a little more complicated. Hopefully, this article will help to explain that complexity and provide you with some generic class objects you can incorporate into your project with a few trivial modifications.

After searching the Web for hours and only finding a couple examples (which I couldn't get to work), I was forced to figure out my own way to accomplish this seemingly simple task. Let me tell you, the code you see here in this article took many hours to figure out and get working reliably. I am posting this article because there was so little information available on this topic on the Web that it seemed like an article on the topic would benefit the development community.

Using the Code

The Typed DataSet Extender Class code shown in this article is added to each Typed DataSet's TableAdapterclass you want to be able to enlist in the Transaction. The TableAdapter's Partial Class file is automatically created in Visual Studio 2005 by simply double-clicking on the Typed DataSet design surface. I believe you can also right click on Typed DataSet in the Solution Explorer and choose the "View Code" option.

Unfortunately, the Auto-Generated code does not create the NameSpace or TableAdapter's Partial Class so you'll have to do that manually for each Typed DataSet you want to enlist in the Transaction. The code shown below will be copied into each Typed DataSet's Partial Class file created when you double-click on the Typed DataSet Design surface (the filename will be <TypedDataSetName>.vb).

Modifying the code below to extend your Typed DataSet classes is done by simply replacing the <TypedDataSetName> tags (including the brackets < >) in the class example shown below with the name of your Typed DataSet (the DataSet name without the XSD extension). You will need to copy the extender code below into every Typed DataSet class you want to use with a single Transaction. You'll only need to change the <TypedDataSetName> tags to match the name of the Typed DataSet, the rest of the code does not require any changes to work with your Typed DataSets.

Since this extender code is placed in a Partial Class, it will not be lost when the Typed DataSet is modified or rebuilt so this effort only needs to be done once (and for any additional Typed DataSets you add to your project in the future). This is the power of the Partial Classand is what makes it possible for you to have a Transactionthat spans across multiple Typed DataSets without invoking a Distributed Transactionlike using TransactionScopedoes.

Here is the Typed DataSet NameSpacecode block you'll need to paste into each Typed DataSet's Partial Classfile:

Imports System.Data.SqlClient
Namespace <TypedDataSetName>TableAdapters

    Partial Public Class <TypedDataSetName>Adapter

        'Retrieves the return value from a Stored Procedure
        Call Public Function GetReturnValue() As Integer
            Return CInt(Me.Adapter.UpdateCommand.Parameters(0).Value)
        End Function

        'Exposes the Typed DataSet DataAdapter
        Public Function GetAdapter() As SqlDataAdapter
            Return (Me.Adapter)
        End Function

        'Exposes the Command Object associated with Database Select Operations
        'This is important if Select commands are going to be issued during an
        'active Update/Insert/Delete Transaction. The Transaction and its
        'associated Connection Object will be set on the SelectCommand object
        'to prevent the Update Transaction from Blocking the Select statement.
        Public ReadOnly Property SelectCommand() As SqlCommand
                Return Me.CommandCollection(0)
            End Get
        End Property

        'Provides Access to the TableAdapters Transaction Object.
        Public Property Transaction() As SqlTransaction
                If (Not IsNothing((Me.Adapter.UpdateCommand.Transaction))) Then
                    Return (Me.Adapter.UpdateCommand.Transaction)
                ElseIf (Not IsNothing((Me.Adapter.InsertCommand.Transaction))) Then
                    Return (Me.Adapter.InsertCommand.Transaction)
                ElseIf (Not IsNothing((Me.Adapter.DeleteCommand.Transaction))) Then
                    Return (Me.Adapter.DeleteCommand.Transaction)
                ElseIf (Not IsNothing((Me.Adapter.SelectCommand.Transaction))) Then
                    Return (Me.Adapter.SelectCommand.Transaction)
                    Return (Nothing)
                End If
            End Get
            Set(ByVal value As SqlTransaction)
                If (Not IsNothing(Me.Adapter.UpdateCommand)) Then
                    Me.Adapter.UpdateCommand.Transaction = value
                    Me.Adapter.UpdateCommand.Connection = value.Connection
                End If
                If (Not IsNothing(Me.Adapter.DeleteCommand)) Then
                    Me.Adapter.DeleteCommand.Transaction = value
                    Me.Adapter.DeleteCommand.Connection = value.Connection
                End If
                If (Not IsNothing(Me.Adapter.InsertCommand)) Then
                    Me.Adapter.InsertCommand.Transaction = value
                    Me.Adapter.InsertCommand.Connection = value.Connection
                End If
            End Set
        End Property

    End Class

End Namespace

The code shown below is the DAL Interface Class required to facilitate the Transactionsharing across multiple Typed DataSets. Just create an SqlInterface.vb file, paste all the code below into it and add it to your project - you will also need to add the required Imports to support any TableAdaptermethods you may use in this class to retrieve data from the database.

Placing all the Typed DataSet Fillmethods in the SqlInterface Classprovides a single access point for Data Access and eliminates a lot of unnecessary complexity in your application code that would be required to handle Multi-Typed DataSet Transactionparticipation.

Imports System.Data.SqlClient

Public Class SqlInterface

#Region " Singleton Constructor Methods "

    'Private Shared reference to this Class required to provide a Singleton instance
     Private Shared SqlInterfaceInstance As SqlInterface

     'Provides a Singleton reference to this Class Instance
     Public Shared Function GetInstance() As SqlInterface
         If (IsNothing(SqlInterfaceInstance)) Then
             SqlInterfaceInstance = New SqlInterface()
        End If
        Return SqlInterfaceInstance
    End Function

    'Private Class Constructor called by the Shared Singleton GetInstance Method
    Private Sub New()
        'Do not remove this sub. This prevents someone from instantiating another
        'SqlInterface Class Instance. Call the 'GetInstance()' method to get an
        'instance reference to this Class. This enforces a Singleton Pattern.
    End Sub

#End Region
#Region " Private Class Variables "

    Private _UsingTransaction As Boolean = False
    Private _Transaction As SqlTransaction = Nothing
    Private _TransConnection As SqlConnection = Nothing
    Private _TableAdapter As SqlDataAdapter = Nothing
    Private _TransCommand As SqlCommand = Nothing

#End Region
#Region "Transaction Methods"

    'Initiates a Transaction on a Typed DataSet TableAdapter Connection. This
    'transaction is automatically assigned to any other Typed DataSet TableAdapter
    'initialized through this Interface Class during an active Transaction until
    'the transaction is explicitly Committed or Rolled back through the SqlInterface
    'Class Instance. (Note: The Private Scope of this method is intentional)
    Private Function BeginTransaction(ByVal TableAdapter As SqlDataAdapter) As Boolean

        'Be sure we don't already have a transaction initiated
        If (Not IsNothing(_Transaction)) Then Return False

        'Set Class TableAdapter Object
        _TableAdapter = TableAdapter

        'Set Class Connection Object
        If (Not IsNothing(_TableAdapter.UpdateCommand)) Then
            _TransConnection = _TableAdapter.UpdateCommand.Connection
        ElseIf (Not IsNothing(_TableAdapter.InsertCommand)) Then
            _TransConnection = _TableAdapter.InsertCommand.Connection
        ElseIf (Not IsNothing(_TableAdapter.DeleteCommand)) Then
            _TransConnection = _TableAdapter.DeleteCommand.Connection
        ElseIf (Not IsNothing(_TableAdapter.SelectCommand)) Then
            _TransConnection = _TableAdapter.SelectCommand.Connection
            Throw New Exception("A Connection could not be established _
                because no Command Object has been initialized")
        End If

        'Open the DB Connection

        'Set the Class Transaction Object
        _Transaction = _TransConnection.BeginTransaction()

        'Set the Transaction property for any instantiated Command Objects
        If (Not IsNothing(_TableAdapter.UpdateCommand)) _
            Then _TableAdapter.UpdateCommand.Transaction = _Transaction
        If (Not IsNothing(_TableAdapter.InsertCommand)) _
            Then _TableAdapter.InsertCommand.Transaction = _Transaction
        If (Not IsNothing(_TableAdapter.DeleteCommand)) _
            Then _TableAdapter.DeleteCommand.Transaction = _Transaction
        If (Not IsNothing(_TableAdapter.SelectCommand)) _
            Then _TableAdapter.SelectCommand.Transaction = _Transaction

        Return True
     End Function

     'This method enlists Adapters used for Database Select 
     'Operations during an active Transaction into the current
     'Transaction and Connection so the Select operation is not blocked 
     'by the active Update Transaction.
     'This is required because the Auto-Generated TableAdpater Class 
     'is coded in such a way that the DataAdapter's
     'SelectCommand is not initialized like the 
     'Update, Insert and Delete Command Objects. The SelectCommand is instead
     'added to the TableAdapater internal CommandCollection 
     '(it's the ONLY Command object in the collection). So this is
     'somewhat of a hack to work around this but at least 
     'it keeps all the necessary code confined to the TableAdapter
     'Extension Class and this DAL Interface Class eliminating the need 
     'for any complicated code in the general application.
     Private Sub EnlistAdapterInTransaction(ByVal Adapter As Object)
         Adapter.SelectCommand.Transaction = Me._Transaction
         Adapter.SelectCommand.Connection = Me._TransConnection
     End Sub

    'Commits all DB changes performed during the Transaction and releases 
    'all resources and SQL DB connections.
    Public Function CommitTransaction() As Boolean
        If (Not IsNothing(_Transaction)) Then
            Return True
            Return False
        End If
    End Function

    'Rolls back all DB changes performed during the Transaction and 
    'releases all resources and SQL DB connections.
    Public Function RollBackTransaction() As Boolean
        If (Not IsNothing(_Transaction)) Then
            Return True
           Return False
       End If
    End Function

    'Handles cleanup for a DAL initiated transaction
    Private Sub EndTransaction()
        If (Not IsNothing(_TransConnection)) Then _TransConnection.Close()
        If (Not IsNothing(_Transaction)) Then _Transaction.Dispose()
        If (Not IsNothing(_TableAdapter)) Then _TableAdapter.Dispose()
    End Sub

#End Region
#Region " Public Class Methods "

    'Example method that executes a Select Operation on the DataBase 
    'with a check for an active
    'transaction. If a transaction is active on the Connection, 
    'the SelectCommand object from
    'the TableAdapter is enlisted into that Transaction 
    'so it is not blocked by the active Transaction.
    'Note: The Typed DataSet name shown in the example is 'TdSomeData'
    Public Function GetSomeDataByKey(ByVal Key As Integer) As TdSomeDataDataTable
        Dim TdAdapter As New TdSomeDataAdapter

        'See if an active transaction exists on the DAL and enlist 
    'the TableAdapter in the Transaction if there is.
        If (Not IsNothing(Me._Transaction)) Then EnlistAdapterInTransaction(TdAdapter)
        Return (TdAdapter.GetSomeData(Key))
    End Function

#End Region
#Region " Public Class Properties "

    'This Property provides access to an active Transaction object 
    'regardless of what Typed DataSet initiated the transaction.
    Public ReadOnly Property Transaction(ByVal TableAdapter As SqlDataAdapter) _
        As SqlTransaction
            If ((Me.UsingTransAction) AndAlso (IsNothing(_Transaction))) Then
            End If
            Return (_Transaction)
        End Get
    End Property

    'Class flag indicating a Transaction is to be used for 
    'subsequent DB changes until committed or rolled back.
    'This flag is required to initiate a transaction on 
    'subsequent TableAdapter reads/writes on the underlying Database.
    'The SqlInterface Class Instance (DAL Interface) will 
    'automatically initiate and maintain the Command, Connection and
    'Transaction objects across multiple Typed DataSet TableAdapter 
    'interactions with the Database until the Transaction
    'is explicitly Committed or Rolled back. Setting this property to True 
    'automatically begins a transaction when required.
    Public Property UsingTransAction() As Boolean
            Return (_UsingTransaction)
        End Get
        Set(ByVal value As Boolean)
            _UsingTransaction = value
        End Set
    End Property

#End Region
End Class

Here's an example of how to use the SQLInterface Classin your general application code:

'Declare a local Class variable of the SQLInterface Class 
'(you don't have to do this - you can use SqlInterface.GetInstance
'if you like, I just like shorter method call strings. 
'So in the example method below, you could use:
'SqlInterface.GetInstance.UsingTransAction = True 
'if you prefer not to use a Class variable like I did in my example.
Private SQL As SqlInterface = SqlInterface.GetInstance

    Public Sub Save()
            'Set the DAL Interface Transaction Flag. 
            'This instructs the SqlInterface Class to begin a transaction
            'or enlist in an existing active transaction when the 
            'SqlInterface Transaction Property is accessed.
            'As long as this flag was True the first time the 
            'SqlInterface Transaction property was assigned to a
            'Typed DataSet's TableAdapter Transaction property, 
            'any other Typed DataSets that assign the SqlInterface
            'transaction property to their transaction property will 
            'automatically be enlisted in the existing Transaction
            'even if the 'UsingTransAction' flag has been reset to False. 
            'To terminate an existing SqlInterface Transaction,
            'you MUST call either the SqlInterface CommitTransaction or 
            'RollBackTransaction method.
            'Note that either of these methods will reset the 'UsingTransAction' flag.
            SQL.UsingTransAction = True

              'Do some code processing here that will cause the Typed DataSet 
              'to Modify a Table the DB. To use the
              'MyTypedDataSetAdapter in the method below, we would need to have an 
              'Imports Statement at the top of
              'this Class file something like this: 
              'Imports <Solution Name><solutionname />.MyTypedDataSetTableAdapter
              If (Me.HasChanges) Then
                  Dim TdAdapter As New MyTypedDataSetAdapter

                  'Assign the SqlInterface Transaction to the TableAdapter 
                  'Transaction Property if the 'UsingTransaction'
                  'Flag has been previously set, this line of code will either 
                  'begin a transaction or enlist this Typed
                  'DataSet's TableAdapter in the current Transaction that was 
                  'initiated by another Typed DataSet
                  'participating in this Transaction.
                  If (SQL.UsingTransAction) Then TdAdapter.Transaction = 

                  'Call the Update Method to save Data to the DB
                  TdAdapter.Update(Param1, Param2, Param3)

              End If

              'Commit the Transaction

          Catch ex As Exception

              'Something went wrong, Rollback the Transaction

              'Throw the Exception so it can be caught and displayed if required
              Throw ex
          End Try
      End Sub

That's it! If you've been avoiding the use of Typed DataSets because you didn't think you could use a single transaction for multiple Typed DataSet updates without using the costly Transactionscope, I hope this article changes your mind. I have found Typed DataSets to be a powerful feature of the .NET Framework handling most of the underlying complexities of database interaction. Typed DataSets make updating your code easy as the underlying database schema changes with the evolution of your application. I hope you find these classes to be useful!

Points of Interest

Something I learned while developing this idea that surprised me was that the SelectCommandobject is not used by the Typed DataSet AdapterClass. I found it even more strange that the auto-generated code only placed the SelectCommandobject in the CommandObjectcollection and did not place any of the other Commandobjects in the collection. The reference to each of them was assigned to their respective command objects (Update, Insert, Delete).


  • 4th May, 2008: Initial post


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


About the Author

Web Developer
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
manoj kumar choubey4-Apr-12 22:29
membermanoj kumar choubey4-Apr-12 22:29 
Generalhelppppppppppppppppppppppp !!!!! Pin
a_sh5714-Feb-10 6:27
membera_sh5714-Feb-10 6:27 
Generalgreat Pin
Member 90314822-Nov-08 4:12
memberMember 90314822-Nov-08 4:12 
GeneralWondering if Singleton is ok Pin
lacombefr14-May-08 22:16
memberlacombefr14-May-08 22:16 
AnswerRe: Wondering if Singleton is ok Pin
drgbg15-May-08 5:25
memberdrgbg15-May-08 5:25 

The reference to being thread safe means the potential exists that a deadlock condition could occur if 2 different threads attempt to instantiate the SqlInterface class at the same time. From the research I have done on this, there seems to be a lot of confusion about how the .Net framework handles this.

Some articles say the framework deals with this gracefully choosing a victim thread that will get blocked while allowing the other thread to successfully initialize the SqlInterface instance. Other articles I read say this isn't true and that you must use synchronization, locking or a Mutex to solve the deadlock condition

From all my research, it seemed to me that using a Mutex would solve that problem and I just haven't had the time to update my article but I have at least done some of the research.

A singleton constructor was chosen so that transaction data (connection, command, transaction state) could be maintained after the intiating Typed Dataset Adapter completed it's Database activity. Then it can be passed over to the next Typed DataSet's adapter instance so it's activity would be contained within the same transaction.

Since each page refresh would receive the very same SqlInterface instance (regardless of how many threads are initialized) the same transaction data is made available to the page objects to interact with which will have the desired effect of keeping all of the Typed DataSet's Database interaction within the transaction initiated by the first Typed DataSet.

Unless you are using a multithreaded application, this should work just the way it stands. I am using this code myself and it works perfectly. Feel free to experiment with it and if you find a better way to do this, share it with us all on the Code Project!

The #1 Reply when developers programs don't work: "I thought I fixed that"

GeneralRe: Wondering if Singleton is ok Pin
lacombefr15-May-08 6:08
memberlacombefr15-May-08 6:08 
GeneralRe: Wondering if Singleton is ok Pin
drgbg15-May-08 11:25
memberdrgbg15-May-08 11:25 
GeneralRe: Wondering if Singleton is ok Pin
Member 406297825-Nov-08 6:52
memberMember 406297825-Nov-08 6:52 
GeneralYour singleton isn't thread safe Pin
Paul B.7-May-08 14:00
memberPaul B.7-May-08 14:00 

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 | Terms of Use | Mobile
Web02 | 2.8.150804.4 | Last Updated 4 May 2008
Article Copyright 2008 by drgbg
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid