Introduction
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
.
Background
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 TableAdapter
class 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 Class
and is what makes it possible for you to have a Transaction
that spans across multiple Typed DataSets without invoking a Distributed Transaction
like using TransactionScope
does.
Here is the Typed DataSet NameSpace
code block you'll need to paste into each Typed DataSet's Partial Class
file:
Imports System.Data.SqlClient
Namespace <TypedDataSetName>TableAdapters
Partial Public Class <TypedDataSetName>Adapter
Call Public Function GetReturnValue() As Integer
Return CInt(Me.Adapter.UpdateCommand.Parameters(0).Value)
End Function
Public Function GetAdapter() As SqlDataAdapter
Return (Me.Adapter)
End Function
Public ReadOnly Property SelectCommand() As SqlCommand
Get
Return Me.CommandCollection(0)
End Get
End Property
Public Property Transaction() As SqlTransaction
Get
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)
Else
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 Transaction
sharing 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 TableAdapter
methods you may use in this class to retrieve data from the database.
Placing all the Typed DataSet Fill
methods in the SqlInterface Class
provides 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 Transaction
participation.
Imports System.Data.SqlClient
Public Class SqlInterface
#Region " Singleton Constructor Methods "
Private Shared SqlInterfaceInstance As SqlInterface
Public Shared Function GetInstance() As SqlInterface
If (IsNothing(SqlInterfaceInstance)) Then
SqlInterfaceInstance = New SqlInterface()
End If
Return SqlInterfaceInstance
End Function
Private Sub New()
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"
Private Function BeginTransaction(ByVal TableAdapter As SqlDataAdapter) As Boolean
If (Not IsNothing(_Transaction)) Then Return False
_TableAdapter = TableAdapter
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
Else
Throw New Exception("A Connection could not be established _
because no Command Object has been initialized")
End If
_TransConnection.Open()
_Transaction = _TransConnection.BeginTransaction()
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
Private Sub EnlistAdapterInTransaction(ByVal Adapter As Object)
Adapter.SelectCommand.Transaction = Me._Transaction
Adapter.SelectCommand.Connection = Me._TransConnection
End Sub
Public Function CommitTransaction() As Boolean
If (Not IsNothing(_Transaction)) Then
_Transaction.Commit()
EndTransaction()
Return True
Else
EndTransaction()
Return False
End If
End Function
Public Function RollBackTransaction() As Boolean
If (Not IsNothing(_Transaction)) Then
_Transaction.Rollback()
EndTransaction()
Return True
Else
Return False
End If
End Function
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 "
Public Function GetSomeDataByKey(ByVal Key As Integer) As TdSomeDataDataTable
Dim TdAdapter As New TdSomeDataAdapter
If (Not IsNothing(Me._Transaction)) Then EnlistAdapterInTransaction(TdAdapter)
Return (TdAdapter.GetSomeData(Key))
End Function
#End Region
#Region " Public Class Properties "
Public ReadOnly Property Transaction(ByVal TableAdapter As SqlDataAdapter) _
As SqlTransaction
Get
If ((Me.UsingTransAction) AndAlso (IsNothing(_Transaction))) Then
Me.BeginTransaction(TableAdapter)
End If
Return (_Transaction)
End Get
End Property
Public Property UsingTransAction() As Boolean
Get
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 Class
in your general application code:
Private SQL As SqlInterface = SqlInterface.GetInstance
Public Sub Save()
Try
SQL.UsingTransAction = True
If (Me.HasChanges) Then
Dim TdAdapter As New MyTypedDataSetAdapter
If (SQL.UsingTransAction) Then TdAdapter.Transaction =
SQL.Transaction(TdAdapter.GetAdapter())
TdAdapter.Update(Param1, Param2, Param3)
End If
SQL.CommitTransaction()
Catch ex As Exception
SQL.RollBackTransaction()
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 Transaction
scope, 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 SelectCommand
object is not used by the Typed DataSet Adapter
Class. I found it even more strange that the auto-generated code only placed the SelectCommand
object in the CommandObject
collection and did not place any of the other Command
objects in the collection. The reference to each of them was assigned to their respective command objects (Update
, Insert
, Delete
).
History
- 4th May, 2008: Initial post
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.