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:
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
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.