SQL Transactions
by Martin Millar
Last updated : 13th September 2006
Background
In many applications there is a need to be able to insert data across multiple tables
as an atomic unit of work. In this example I will cover a simple page in a shopping
cart application that requires users to provide both a billing and a shipping address.
This information is stored across two separate tables (User and Address) as a parent-child
relationship. There would be little point in creating a new user in our database
if no address details were added. The Billing dept would not know where to send
the invoice and the Shipping department would not know where to deliver the goods.
Therefore we require that the new customer page will only succeed if:
- User details are inserted into the User table
- Billing address is inserted into the Address table with the new customer ID
- Shipping address is inserted into the Address table with the new customer ID
To achieve this we need to wrap all this work together as a transaction. If all
parts succeed we then commit this to the database otherwise everything needs to
be rolled back so that the database is in the same state that it was in prior to the
insert.
Strategy
To implement this functionality as an ATOMIC transaction we will use the SQLTransaction
class of the System.Data.SQLClient namespace. Just so you are aware, there are other
ways to implement transactions, notably TSQL in stored procedures and the System.Transactions namespace
that is in the new version of the DOT.NET framework.
I like System.Transactions coding but this does not work well with the SQL 2000
databases that I use. This is because it enlists the support of the MSDTC and this
adds
to the overhead. However it was designed to work well with SQL Server 2005
using promotable transactions.
Implementation
We will assume that a user has filled their shopping basket and the next stage for
new users is to register themselves and their billing and shipping information.
Again this is an all or nothing insert.
Step 1 - Architecture
We will create a User and Address class to hold the information that we collect
from the web form. As we will be holding multiple addresses. We will use a Generic
List collection to hold these.
User Class
Namespace Bracora.Articles.TransactionExample
Public Class User
Private _forename As String
Private _surname As String
Private _email As String
''' <summary>
''' Initializes a new instance of the User class.
''' </summary>
''' <param name="forename"></param>
''' <param name="surname"></param>
''' <param name="email"></param>
Public Sub New(ByVal forename As String, ByVal surname As String, ByVal email As String)
_forename = forename
_surname = surname
_email = email
End Sub
''' <summary>
''' Initializes a new instance of the User class.
''' </summary>
Public Sub New()
End Sub
Public Property Forename() As String
Get
Return _forename
End Get
Set(ByVal value As String)
If _forename = value Then
Return
End If
_forename = value
End Set
End Property
Public Property Surname() As String
Get
Return _surname
End Get
Set(ByVal value As String)
If _surname = value Then
Return
End If
_surname = value
End Set
End Property
Public Property Email() As String
Get
Return _email
End Get
Set(ByVal value As String)
If _email = value Then
Return
End If
_email = value
End Set
End Property
End Class
End Namespace
Address Class
Namespace Bracora.Articles.TransactionExample
Public Class Address
Public Const AT_BILL As String = "BILLADDRESS"
Public Const AT_SHIP As String = "SHIPADDRESS"
Private _userID As Integer
Private _address1 As String
Private _address2 As String
Private _address3 As String
Private _city As String
Private _postZipCode As String
Private _addressType As String
''' <summary>
''' Initializes a new instance of the Address class.
''' </summary>
''' <param name="userID"></param>
''' <param name="address1"></param>
''' <param name="address2"></param>
''' <param name="address3"></param>
''' <param name="city"></param>
''' <param name="postZipCode"></param>
''' <param name="addressType"></param>
Public Sub New(ByVal userID As Integer, ByVal address1 As String, ByVal address2 As String, ByVal address3 As String, ByVal city As String, ByVal postZipCode As String, ByVal addressType As String)
_userID = userID
_address1 = address1
_address2 = address2
_address3 = address3
_city = city
_postZipCode = postZipCode
_addressType = AddressType
End Sub
''' <summary>
''' Initializes a new instance of the Address class.
''' </summary>
''' <param name="address1"></param>
''' <param name="address2"></param>
''' <param name="address3"></param>
''' <param name="city"></param>
''' <param name="postZipCode"></param>
Public Sub New(ByVal address1 As String, ByVal address2 As String, ByVal address3 As String, ByVal city As String, ByVal postZipCode As String)
_address1 = address1
_address2 = address2
_address3 = address3
_city = city
_postZipCode = postZipCode
End Sub
''' <summary>
''' Initializes a new instance of the Address class.
''' </summary>
Public Sub New()
End Sub
Public Property userID() As Integer
Get
Return _userID
End Get
Set(ByVal value As Integer)
If _userID = Value Then
Return
End If
_userID = Value
End Set
End Property
Public Property Address1() As String
Get
Return _address1
End Get
Set(ByVal value As String)
If _address1 = value Then
Return
End If
_address1 = value
End Set
End Property
Public Property Address2() As String
Get
Return _address2
End Get
Set(ByVal value As String)
If _address2 = value Then
Return
End If
_address2 = value
End Set
End Property
Public Property Address3() As String
Get
Return _address3
End Get
Set(ByVal value As String)
If _address3 = value Then
Return
End If
_address3 = value
End Set
End Property
Public Property City() As String
Get
Return _city
End Get
Set(ByVal value As String)
If _city = value Then
Return
End If
_city = value
End Set
End Property
Public Property PostZipCode() As String
Get
Return _postZipCode
End Get
Set(ByVal value As String)
If _postZipCode = value Then
Return
End If
_postZipCode = value
End Set
End Property
Public Property AddressType() As String
Get
Return _addressType
End Get
Set(ByVal value As String)
If _addressType = value Then
Return
End If
_addressType = value
End Set
End Property
End Class
End Namespace
Step 2 - Data Access Code
There is a static class UserManager that we will use to perform the necessary database inserts.
The methods in this class will work on the User and Address objects mentioned above.
In the UserManager class we have the shared methods to handle the database insert
logic and the transactional part. We have a method called InsertUserAndAddresses
that controls the SQL Transaction. This method takes a populated User object
and a Generic List of Address objects.
For the InsertUser and InsertAddress functions to participate in our transaction
we need to pass a reference to the SQLConnection and the SQLTransaction that we
created in our controlling function InsertUserAndAddresses. These are then attached
to the actual SQLCommand objects that perform the inserts.
We wrap this functionality in a try/catch block so that we can easily rollback
the transaction if any errors occur. If all the inserts are successful we then
commit the transaction and it is saved in the database.
Static Data Access Class
Imports Bracora.Articles
Imports System.Collections.Generic
Imports System.Data.SqlClient
Namespace Bracora.Articles.TransactionExample
Public NotInheritable Class UserManager
'static class
'not to be instantiated
Private Sub New()
End Sub
Public Shared Function NewConn() As SqlConnection
Return New SqlConnection(ConfigurationManager.ConnectionStrings("Articles").ConnectionString)
End Function
Public Shared Sub InsertUserAndAddresses(ByVal newUser As TransactionExample.User, _
ByVal newAddress As List(Of TransactionExample.Address))
Dim userID As Int32 = 0
Dim Tran As SqlTransaction
Using conn As SqlConnection = NewConn()
conn.Open()
Tran = conn.BeginTransaction
Try
' Try to add the user
userID = InsertUser(newUser, Tran, conn)
Dim AddressCount As Int32 = 0
For i As Int32 = 0 To newAddress.Count - 1
newAddress(i).userID = userID
InsertAddress(newAddress(i), Tran, conn)
Next
Tran.Commit()
Catch ex As Exception
Tran.Rollback()
' re-throw back to the ui
Throw ex
End Try
End Using
End Sub
Public Shared Function InsertUser(ByVal newUser As TransactionExample.User, _
ByVal sqlTran As SqlTransaction, _
ByVal sqlConn As SqlConnection) As Int32
Dim UserID As Object = Nothing
Dim Sql As String
Sql = "INSERT INTO [User] (" & _
"[Forename],[Surname],[Email]) " & _
"VALUES(@Forename,@Surname,@Email);SELECT SCOPE_IDENTITY();"
Using cmd As New SqlCommand
cmd.Connection = sqlConn
cmd.Transaction = sqlTran
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = Sql
cmd.Parameters.Add(New SqlParameter("Forename", newUser.Forename))
cmd.Parameters.Add(New SqlParameter("Surname", newUser.Surname))
cmd.Parameters.Add(New SqlParameter("Email", newUser.Email))
userID = cmd.ExecuteScalar
If userID IsNot Nothing Then
Return Convert.ToInt32(userID)
End If
End Using
End Function
Public Shared Sub InsertAddress(ByVal newAddress As TransactionExample.Address, _
ByVal sqlTran As SqlTransaction, _
ByVal sqlConn As SqlConnection)
Dim Sql As String
Sql = "INSERT INTO [Address] (" & _
"[UserID],[Address1],[Address2],[Address3],[City],[PostZipCode],[AddressType]) " & _
"VALUES(@UserID,@Address1,@Address2,@Address3,@City,@PostZipCode,@AddressType)"
Using cmd As New SqlCommand
cmd.Connection = sqlConn
cmd.Transaction = sqlTran
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = Sql
cmd.Parameters.Add(New SqlParameter("UserID", newAddress.userID))
cmd.Parameters.Add(New SqlParameter("Address1", newAddress.Address1))
cmd.Parameters.Add(New SqlParameter("Address2", newAddress.Address2))
cmd.Parameters.Add(New SqlParameter("Address3", newAddress.Address3))
cmd.Parameters.Add(New SqlParameter("City", newAddress.City))
cmd.Parameters.Add(New SqlParameter("PostZipCode", newAddress.PostZipCode))
cmd.Parameters.Add(New SqlParameter("AddressType", newAddress.AddressType))
cmd.ExecuteNonQuery()
End Using
End Sub
End Class
End Namespace
Step 3 - User Interface
When the user populates the form fields and hits submit we create a User object
and a List of Address Objects. We then call the transactional method InsertuserAndAddresses.
If successful, we would move onto the next stage in the checkout process. Otherwise
we display an error message back to the user.
Submit Code
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
' you would want to validate prior to inserting
' we assume all necessary fields are complete
Dim NewUser As New TransactionExample.User
NewUser.Forename = Me.txtForename.Text.Trim
NewUser.surname = Me.txtSurname.Text.Trim
NewUser.Email = Me.txtEmail.Text.Trim
Dim NewAddress As New Generic.List(Of TransactionExample.Address)
Dim tmpAddress As TransactionExample.Address
tmpAddress = New TransactionExample.Address
tmpAddress.Address1 = Me.txtBillAddress1.Text.Trim
tmpAddress.Address2 = Me.txtBillAddress2.Text.Trim
tmpAddress.Address3 = Me.txtBillAddress3.Text.Trim
tmpAddress.City = Me.txtBillCity.Text.Trim
tmpAddress.PostZipCode = Me.txtBillZip.Text.Trim
tmpAddress.AddressType = Bracora.Articles.TransactionExample.Address.AT_BILL
NewAddress.Add(tmpAddress)
' check to see if we need to use the same address for shipping
If Me.chkUseBilling.Checked Then
tmpAddress = New TransactionExample.Address
tmpAddress.Address1 = Me.txtBillAddress1.Text.Trim
tmpAddress.Address2 = Me.txtBillAddress2.Text.Trim
tmpAddress.Address3 = Me.txtBillAddress3.Text.Trim
tmpAddress.City = Me.txtBillCity.Text.Trim
tmpAddress.PostZipCode = Me.txtBillZip.Text.Trim
tmpAddress.AddressType = Bracora.Articles.TransactionExample.Address.AT_SHIP
NewAddress.Add(tmpAddress)
Else
tmpAddress = New TransactionExample.Address
tmpAddress.Address1 = Me.txtShipAddress1.Text.Trim
tmpAddress.Address2 = Me.txtShipAddress2.Text.Trim
tmpAddress.Address3 = Me.txtShipAddress3.Text.Trim
tmpAddress.City = Me.txtShipCity.Text.Trim
tmpAddress.PostZipCode = Me.txtShipZip.Text.Trim
tmpAddress.AddressType = Bracora.Articles.TransactionExample.Address.AT_SHIP
NewAddress.Add(tmpAddress)
End If
Try
TransactionExample.UserManager.InsertUserAndAddresses(NewUser, NewAddress)
ClearFields(Page.Form)
Catch ex As Exception
' display errr in UI
Me.litError.Text = "<div class=""error"">There was a problem with the update. " _
& "If this persists then please contact the administrator.<br />" _
& ex.Message & "</div>"
End Try
End Sub
If you wish to test the rollback of the transaction simply change the SQL statement
within the InsertAddress function. This will then fail and the successful User Insert
will be rolled back.
Summary
There are many occasions where you will need to handle workflow as a transactional
piece of work against a database. Hopefully this article has given you a feel for
how you can perform such a task using the SQLTransaction object.
About the author
Martin Millar is an solutions architect for a large consultancy firm based in
Melbourne, Australia. Previously he worked as a senior applications developer
for a large Telecoms firm in the UK. He specialises in web development using ASP.NET and SQL Server
and is a Microsoft Certified Solutions Developer(MCSD). In his spare time he
runs a fledgling web design company called bracora at
http://www.bracora.com.
Comments