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!
Beth is a Program Manager on the Visual Studio Community Team at Microsoft and is responsible for producing and managing content for business application developers, driving community features and team participation onto MSDN Developer Centers (http://msdn.com), and helping make Visual Studio one of the best developer tools in the world. She also produces regular content on her blog (http://blogs.msdn.com/bethmassi), Channel 9, and a variety of other developer sites and magazines. As a community champion and a long-time member of the Microsoft developer community she also helps with the San Francisco East Bay .NET user group and is a frequent speaker at various software development events. Before Microsoft, she was a Senior Architect at a health care software product company and a Microsoft Solutions Architect MVP. Over the last decade she has worked on distributed applications and frameworks, web and Windows-based applications using Microsoft development tools in a variety of businesses. She loves teaching, hiking, mountain biking, and driving really fast.
