Process database actions, such as save, update and delete on different kinds of objects (or object lists) as a transaction inside your data access layer using generics and a couple of helper objects
Assuming that you want to save into Payments and PaymentsDetails tables for a Client who has an account and ensuring that the balance in Accounts table is also updated.
This example shows how you can save, update and delete different objects of different types (or object lists) as a transaction in your DAL. It’s related to my previous post that you can find at. Using List(Of T) Generic in your data access layer (DAL)- Improved However, this could only process a list of the same type.
Explanation
- Create an enumeration (say Action) that you’ll use to specify an action to be performed on the list
- Create an interface (say ISQLData) for objects that are going to participate in a transaction to implement.
- Create a base class say DBConnect that implements ISQLData for other classes that will participate in the transaction to derive from.
- Create a helper class (say TransactionList) that will help to store object lists with action to be performed on the list
- Create a method (say DoTransactions) that processes the transactions
- Create objects such as Payments, PaymentDetails and Accounts that derives from DBConnect
- Define a list of the above objects and call DoTransactions to process them as a transaction.
Enumeration
Public Enum Action
Save
Update
Delete
End Enum
Interface
''' <summary>
''' Objects that will participate in transaction
''' must implement this interface
''' </summary>
''' <remarks></remarks>
Public Interface ISQLData
Property ConString() As String
Function SaveCommand() As SqlCommand
Function UpdateCommand() As SqlCommand
Function DeleteCommand() As SqlCommand
End Interface
Helper Objects and Methods
''' <summary>
''' Helper class that stores a list of objects
''' that implement ISQLData interface
''' </summary>
''' <typeparam name="T"></typeparam>
''' <remarks></remarks>
Public Class TransactionList(Of T As ISQLData)
#Region " Fields "
Private m_list As List(Of T)
Private m_Action As Action
#End Region
#Region " Properties "
Public Property List() As List(Of T)
Get
Return m_list
End Get
Set(ByVal value As List(Of T))
m_list = value
End Set
End Property
Public Property Action() As Action
Get
Return m_Action
End Get
Set(ByVal Value As Action)
m_Action = Value
End Set
End Property
#End Region
#Region " Constructors "
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal _list As List(Of T), ByVal _Action As Action)
MyClass.New()
Me.List = _list
Me.Action = _Action
End Sub
#End Region
#Region " Methods "
#End Region
End Class
''' <summary>
''' Base class that implements ISQLData
''' </summary>
''' <remarks></remarks>
Public MustInherit Class DBConnect : Implements ISQLData
#Region " Fields "
' Objects that will participate in transactions
' will use the same connection string, lets define it here
Private Shared m_ConString As String
#End Region
#Region " Properties "
Public Property ConString() As String Implements ISQLData.ConString
Get
Return m_ConString
End Get
Set(ByVal Value As String)
m_ConString = Value
End Set
End Property
#End Region
#Region " Methods "
Public Overridable Function SaveCommand() As SqlCommand Implements ISQLData.SaveCommand
Return Nothing
End Function
Public Overridable Function UpdateCommand() As SqlCommand Implements ISQLData.UpdateCommand
Return Nothing
End Function
Public Overridable Function DeleteCommand() As SqlCommand Implements ISQLData.DeleteCommand
Return Nothing
End Function
#End Region
End Class
''' <summary>
''' Performs all specified actions such as Save,
''' Update and Delete on a list of objects of type
''' TransactionList having a list of objects of type
''' IDBConnect and returns number of records affected.
''' </summary>
''' <typeparam name="T"></typeparam>
''' <param name="_List"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function DoTransactions(Of T As ISQLData)(ByVal _List As List(Of TransactionList(Of T))) As Integer
Dim records As Integer
Using conn As New SqlConnection()
Try
If _List Is Nothing OrElse _List.Count < 1 Then Throw New ArgumentException("List is not set or empty!")
'exit the loop as soon as an item with set connection string is found
For Each items As TransactionList(Of T) In _List
For Each list As T In items.List
If list IsNot Nothing Then
conn.ConnectionString = list.ConString()
Exit For
End If
Next
If Not String.IsNullOrEmpty(conn.ConnectionString) Then Exit For
Next
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction()
For Each items As TransactionList(Of T) In _List
Select Case items.Action
Case Action.Save
For Each list As T In items.List
Try
Using comm As SqlCommand = list.SaveCommand()
comm.Connection = conn
comm.Transaction = tran
If comm.ExecuteNonQuery > 0 Then records += 1
End Using
Catch ex As Exception
tran.Rollback()
Throw New ArgumentException("Error occured while saving, transaction canceled!" & vbCrLf & ex.Message)
End Try
Next
Case Action.Update
For Each list As T In items.List
Try
Using comm As SqlCommand = list.UpdateCommand()
comm.Connection = conn
comm.Transaction = tran
If comm.ExecuteNonQuery > 0 Then records += 1
End Using
Catch ex As Exception
tran.Rollback()
Throw New ArgumentException("Error occured while updating, transaction was canceled!" & vbCrLf & ex.Message)
End Try
Next
Case Action.Delete
For Each list As T In items.List
Try
Using comm As SqlCommand = list.DeleteCommand()
comm.Connection = conn
comm.Transaction = tran
If comm.ExecuteNonQuery > 0 Then records += 1
End Using
Catch ex As Exception
tran.Rollback()
Throw New ArgumentException("Error occured while deleting, transaction was canceled!" & vbCrLf & ex.Message)
End Try
Next
End Select
Next
tran.Commit()
End Using
Return records
Catch eX As SqlException
Throw eX
Catch eX As Exception
Throw eX
Finally
conn.Close()
End Try
End Using
End Function
''' <summary>
''' Construct a connection string in a way that eliminates SQL injection
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Function ConString() As String
Dim conBuilder As New SqlConnectionStringBuilder()
Dim serverName As String = "(local)"
Dim databaseName As String = "Accounting"
conBuilder.Clear()
conBuilder("Server") = serverName
conBuilder.InitialCatalog = databaseName
conBuilder("Integrated Security") = "SSPI"
conBuilder.PersistSecurityInfo = False
Return conBuilder.ConnectionString
End Function
Participating Objects (Payments, PaymentDetails, Accounts)
Public Class Payments : Inherits DBConnect
#Region " Fields "
Private m_ReceiptNo As Integer
Private m_AccountNo As Integer
Private m_PayDate As Date
#End Region
#Region " Properties "
Public Property ReceiptNo() As Integer
Get
Return m_ReceiptNo
End Get
Set(ByVal value As Integer)
m_ReceiptNo = value
End Set
End Property
Public Property AccountNo() As Integer
Get
Return m_AccountNo
End Get
Set(ByVal value As Integer)
m_AccountNo = value
End Set
End Property
Public Property PayDate() As Date
Get
Return m_PayDate
End Get
Set(ByVal value As Date)
m_PayDate = value
End Set
End Property
#End Region
#Region " Constructors "
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal _ConString As String)
MyClass.New()
Me.ConString = _ConString
End Sub
#End Region
#Region " Methods "
Public Overrides Function SaveCommand() As SqlCommand
Using comm As SqlCommand = New SqlCommand("uspInsertPayments")
comm.CommandType = CommandType.StoredProcedure
With comm.Parameters
.AddWithValue("@ReceiptNo", Me.ReceiptNo)
.AddWithValue("@AccountNo", Me.AccountNo)
.AddWithValue("@PayDate", Me.PayDate)
End With
Return comm
End Using
End Function
#End Region
End Class
Public Class PaymentDetails : Inherits DBConnect
#Region " Fields "
Private m_ReceiptNo As Integer
Private m_ProductNo As Integer
Private m_Price As Decimal
#End Region
#Region " Properties "
Public Property ReceiptNo() As Integer
Get
Return m_ReceiptNo
End Get
Set(ByVal value As Integer)
m_ReceiptNo = value
End Set
End Property
Public Property ProductNo() As Integer
Get
Return m_ProductNo
End Get
Set(ByVal value As Integer)
m_ProductNo = value
End Set
End Property
Public Property Price() As Decimal
Get
Return m_Price
End Get
Set(ByVal value As Decimal)
m_Price = value
End Set
End Property
#End Region
#Region " Constructors "
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal _ConString As String)
MyClass.New()
Me.ConString = _ConString
End Sub
#End Region
#Region " Methods "
Public Overrides Function SaveCommand() As SqlCommand
Using comm As SqlCommand = New SqlCommand("uspInsertPaymentDetails")
comm.CommandType = CommandType.StoredProcedure
With comm.Parameters
.AddWithValue("@ReceiptNo", Me.ReceiptNo)
.AddWithValue("@ProductNo", Me.ProductNo)
.AddWithValue("@Price", Me.Price)
End With
Return comm
End Using
End Function
#End Region
End Class
Public Class Accounts : Inherits DBConnect
#Region " Fields "
Private m_TranID As Integer
Private m_AccountNo As Integer
Private m_Amount As Decimal
Private m_Balance As Decimal
#End Region
#Region " Properties "
Public Property TranID() As Integer
Get
Return m_TranID
End Get
Set(ByVal value As Integer)
m_TranID = value
End Set
End Property
Public Property AccountNo() As Integer
Get
Return m_AccountNo
End Get
Set(ByVal value As Integer)
m_AccountNo = value
End Set
End Property
Public Property Amount() As Decimal
Get
Return m_Amount
End Get
Set(ByVal value As Decimal)
m_Amount = value
End Set
End Property
Public Property Balance() As Decimal
Get
Return m_Balance
End Get
Set(ByVal value As Decimal)
m_Balance = value
End Set
End Property
#End Region
#Region " Constructors "
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal _ConString As String)
MyClass.New()
Me.ConString = _ConString
End Sub
#End Region
#Region " Methods "
Public Overrides Function UpdateCommand() As SqlCommand
Using comm As SqlCommand = New SqlCommand("uspUpdateAccounts")
comm.CommandType = CommandType.StoredProcedure
With comm.Parameters
.AddWithValue("@TranID", Me.TranID)
.AddWithValue("@AccountNo", Me.AccountNo)
.AddWithValue("@Amount", Me.Amount)
.AddWithValue("@Balance", Me.Balance)
End With
Return comm
End Using
End Function
#End Region
End Class
User Interface Code
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try
Me.Cursor = Cursors.WaitCursor
Dim transactions As New List(Of TransactionList(Of DBConnect))
' Make sure that atleast one object(Payments in this case)
' sets the connection string
Dim oPayments As New Payments(ConString)
Dim lPayments As New List(Of DBConnect)
Dim oPaymentDetails As New PaymentDetails()
Dim lPaymentDetails As New List(Of DBConnect)
Dim oAccounts As New Accounts()
Dim lAccounts As New List(Of DBConnect)
' Set Payments list to save
With lPayments
.Add(New Payments() With {.ReceiptNo = 101, .AccountNo = 1001, .PayDate = Today()})
End With
' Set PaymentDetails list to save
With lPaymentDetails
.Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 1, .Price = 500})
.Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 2, .Price = 550})
.Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 3, .Price = 200})
.Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 4, .Price = 180})
End With
' Set Accounts list to update
With lAccounts
.Add(New Accounts() With {.TranID = 2, .AccountNo = 1001, .Amount = 800, .Balance = 8900})
End With
' Populate the transactions list
With transactions
.Add(New TransactionList(Of DBConnect)(lPayments, Action.Save))
.Add(New TransactionList(Of DBConnect)(lPaymentDetails, Action.Save))
.Add(New TransactionList(Of DBConnect)(lAccounts, Action.Update))
End With
' Process transactions
Dim records As Integer = Common.DoTransactions(transactions)
MessageBox.Show(String.Format("{0} record(s) processed!", records))
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Me.Cursor = Cursors.Default
End Try
End Sub
Source Code (DoTransactions.zip) attached
Wilson Kutegeka | Microsoft MVP - VB
Developer | Promoter | ClinicMaster Software
Cel: +256 772 609113 | Web: www.clinicmaster.net