Wednesday, 13 September 2006

SQL Transactions

Posted by Martin at 9/13/2006 03:32:00 pm

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:

  1. User details are inserted into the User table
  2. Billing address is inserted into the Address table with the new customer ID
  3. 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.

frm_highlight01 

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.

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )