LINQ to SQL N-Tier Smart Client - Part 3 Database Transactions

In my previous posts this week I showed how to build a simple distributed application with a Windows client, a WCF hosted middle-tier and a data access layer that used LINQ to SQL:

LINQ to SQL N-Tier Smart Client - Part 1 Building the Middle-Tier

LINQ to SQL N-Tier Smart Client - Part 2 Building the Client

After sleeping on the design I realized that there's a scenario that we may want to handle. When we built the connected client-server version of the application (using the connected DataContext), because the DataContext is tracking all our changes (updates/inserts/and deletes) when we call SubmitChanges these updates are all processed in one single database transaction.

This may or may not be required for your application and in the case of Orders/OrderDetails it's okay to allow the updates and inserts and then the deletes to be processed in separate transactions. However what if we were working with drug interactions in a medical application or other data that needs to provide this level of integrity?

It's easy to make these modifications to our n-tier application we built. All we need to do is attach ALL the changes that we want processed in a single database transaction to one instance of the DataContext. To do this first we need to modify our service to accept all our changes. This can end up putting more data on the wire which we discussed in Part 1 so you need to evaluate your scenarios carefully. In our case I'm only pulling up open orders for a particular customer ID so the data set is relatively small.

First add the following interface on our WCF service:

<ServiceContract()> _
Public Interface IOMSService
.
.
<OperationContract()> _
Function SaveAllOrders(ByRef orders As OrderList, _
                       ByVal deletedOrders As IEnumerable(Of Order), _
                       ByVal deletedDetails As IEnumerable(Of OrderDetail)) As Boolean

End Interface

Next add the implementation to the OMSDataManager class in the data access layer to go ahead and attach all the changes to a single DataContext and submit all the changes at once. Note that the validation is performed exactly as before (when SubmitChanges is called).

Public Shared Function SaveAllOrders(ByRef orders As IEnumerable(Of Order), _
                                     ByVal deletedOrders As IEnumerable(Of Order), _
                                     ByVal deletedDetails As IEnumerable(Of OrderDetail)) As Boolean

    Dim hasOrders = (orders IsNot Nothing AndAlso orders.Count > 0)
    Dim hasDeletedOrders = (deletedOrders IsNot Nothing AndAlso deletedOrders.Count > 0)
    Dim hasDeletedDetails = (deletedDetails IsNot Nothing AndAlso deletedDetails.Count > 0)

    If (Not hasOrders) AndAlso (Not hasDeletedOrders) AndAlso (Not hasDeletedDetails) Then
        Return False 'nothing at all to save
    End If

    Dim db As New OMSDataContext

    For Each o In orders
        'Insert/update orders and details
        If o.OrderID = 0 Then
            db.Orders.InsertOnSubmit(o)
        Else
            db.Orders.Attach(o, o.IsDirty)
        End If

        For Each d In o.OrderDetails
            If d.IsDirty Then
                If d.OrderDetailID = 0 Then
                    db.OrderDetails.InsertOnSubmit(d)
                Else
                    db.OrderDetails.Attach(d, True)
                End If
            End If
        Next
    Next

    If hasDeletedOrders Then
        'Delete orders and related details
        db.Orders.AttachAll(deletedOrders, False)
        db.Orders.DeleteAllOnSubmit(deletedOrders)



        For Each o In deletedOrders
             For Each detail In o.OrderDetails
                db.OrderDetails.DeleteOnSubmit(detail)
            Next
        Next
    End If

    If hasDeletedDetails Then
        'Now delete the order details that were passed in
        ' (these order parents were not deleted, just the details)
        db.OrderDetails.AttachAll(deletedDetails, False)
        db.OrderDetails.DeleteAllOnSubmit(deletedDetails)
    End If

    Try
        'There's one database transaction for all records that are attached.
        'Since we attached all updates/inserts/deletes 
        ' they will all be processed in one transaction.
        db.SubmitChanges(ConflictMode.ContinueOnConflict)

        'Reset the IsDirty flag
        For Each o In orders
            o.IsDirty = False
            For Each d In o.OrderDetails
                d.IsDirty = False
            Next
        Next
    Catch ex As ChangeConflictException
        'TODO: Conflict Handling
        Throw
        Return False
    End Try

    Return True
End Function

We can then modify our form to call this new operation. On the client form I just added a new method called SaveAll. Note that the same simple change tracking is being used.

Private Sub SaveAll()
    'Push any pending edits on the BindingSources to the BindingList
    Me.Validate()
    Me.OrderBindingSource.EndEdit()
    Me.OrderDetailsBindingSource.EndEdit()
    Dim saved = False

    'Only save changes if there are some and they are valid
    If Me.HasChanges AndAlso Me.ValidateOrders() Then

        Dim saveOrders = Me.Orders.ToArray()
        Dim delOrders = Me.DeletedOrders.ToArray()
        Dim delDetails = Me.DeletedDetails.ToArray()

        Try
            If saveOrders.Count > 0 OrElse delOrders.Count > 0 OrElse delDetails.Count > 0 Then
                'Update/insert orders/details
                If proxy.SaveAllOrders(saveOrders, delOrders, delDetails) Then
                    Me.DeletedDetails.Clear()
                    Me.DeletedOrders.Clear()
                    saved = True
                End If
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        'Merges added keys and any validation errors
        Me.MergeOrdersList(saveOrders)

    End If

    If Me.HasErrors Then
        'Display any errors if there are any
        Me.DisplayErrors()
        MsgBox("Please correct the errors on this form.")
    Else
        If saved Then
            MsgBox("Your data was saved.")
        Else
            MsgBox("Your data was not saved.")
        End If
    End If
End Sub

So now when we make updates, inserts and deletes to our Orders and OrderDetails then we can save them all in a single database transaction.

I've uploaded the latest version of the application onto Code Gallery with the modifications.

Enjoy!