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

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

  • Comments 20

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.Attach(o, o.IsDirty)
        End If

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

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

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
    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()

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

        Catch ex As Exception
        End Try

        'Merges added keys and any validation errors

    End If

    If Me.HasErrors Then
        'Display any errors if there are any
        MsgBox("Please correct the errors on this form.")
        If saved Then
            MsgBox("Your data was saved.")
            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.


Leave a Comment
  • Please add 2 and 7 and type the answer here:
  • Post
  • Awesome! Really appreciate this.

  • i am new to vb & using vb 2005

    i want to know about list view

    pl help me

  • Thanks for this, I'll try and find time to work through it and finally know what the 3 tiers of a 3-tier system are!

    BTW: If you visit and search for articles by "Beth Massi" you get nothing written... not sure thats a good thing!

  • Hi Rick,

    Re: Search

    Yes it's kind of lame, you have to search on "BethMassi" (one word) for it to work.

    I think it's looking at my username and not my display name. Sigh...

    Thanks for the note!


  • BEth,  I love all your tutorials.  In trying to lean LINQ, I am having a problem handling the results of a query being empty.  I see in this example and many other places that the check for entity.Count() = 0 is used.  I cant make it work with my quiries - instead - I get an error every time.  What am I missing?

  • So it turns out it has something to do with my code (Duh) being in a For each loop.

    I am trying to read an Access Database that I want to selectivly update an SQL Server DB.  I establish my connection to both - and as I read through the access data using a for each datareader, I check my SQL databse to see if the record exists.  If it does exist, I need to verify the records are the same.  If the record does not exist, I need to create a new record.

    I cant seem to make it work with any block (ie Try/Catch, For Each)

    Somehow the variable needs to be available outside the block.  Any ideas?

  • Hi Beth. Its me again.

    Anyway I would like to share this variant code I created which behaves like the SAVEALL method.

    On the BaseBusiness class I created 3 public property

       Public IsNew As Boolean

       Public IsEdit As Boolean

       Public IsDelete As Boolean

    I added a tracking function on client code to keep track of all the Inserts, Updates and Deletes on each entities/sub-entities.

    On the datalayer I just added these code to what SAVEALLORDERS implemented.

    If Order.IsNew then


    elseIf Order.Isedit then

    datacontext.orderdetails.InsertAllOnSubmit(Order.Orderdetails.Where(Function(a) a.IsNew))

    datacontext.orderdetails.AttachAll(Order.Orderdetails.Where(Function(a) a.IsEdit), True)

    dataconext.orderdetails.DeleteAllOnSubmit(Order.Orderdetails.Where(Function(a) a.IsDelete))

    ElseIf Order.IsDelete

    '...implement almost the same code as [delete] above

    end if

    These approach eliminates looping to each records.

    I'm not sure of its speed efficiency.



  • Hi Kent,

    Thanks for the feedback. Yep, there are many ways you can do this depending on your tracking strategy.



  • This is a 3 part post where Beth Massi, MSDN Community Program Manager for Visual Basic, constructs an

  • i am using vb 2008 and when i try to make new connection to database the message is

    event log file is full

    and connetion is not established

  • Please can you put all this new programming in to new video, as an add on the ‘Use DataSets in an N-Tier Application’ as I find it hard to follow.

  • Hi massi, great series of Linq to SQL NTier. Im working in C# and have problems when inserting orders detail, the run time is trying to insert the order detail and the product

  • hi

    please help me about this questions?

    1-how can i creat relation one-to many in vb6 with access file by 2 msflexgrid without use data form wizard?

    2-why when we use biblio.mdb file for creat one to many relation by data form wizard we can creat it but when we creat other file for example student info and we use data form wizard for this file, vb6 can not creat one -to many relation?

    thank you

  • Hi Trooper,

    I'm not sure what the problem is for you. The sample does not edit the product objects at all nor attach them to the DataContext. Make sure this is the case in your code as well.


  • Hi Mohammad,

    You may want to ask your questions in VB 6 forums. Try:



Page 1 of 2 (20 items) 12