Compartilhar via

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

  • Artigo

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!

Comments

  • Anonymous
    April 16, 2008
    Awesome! Really appreciate this.

  • Anonymous
    April 20, 2008
    i am new to vb & using vb 2005 i want to know about list view pl help me

  • Anonymous
    April 22, 2008
    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 blogs.msdn.com and search for articles by "Beth Massi" you get nothing written... not sure thats a good thing!

  • Anonymous
    April 22, 2008
    The comment has been removed

  • Anonymous
    April 22, 2008
    The comment has been removed

  • Anonymous
    April 22, 2008
    The comment has been removed

  • Anonymous
    April 23, 2008
    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 datacontext.Orders.InsertOnSubmit(Order) 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. Rgrds, Kent

  • Anonymous
    April 23, 2008
    Hi Kent, Thanks for the feedback. Yep, there are many ways you can do this depending on your tracking strategy. Cheers, -B

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

  • Anonymous
    April 27, 2008
    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

  • Anonymous
    May 01, 2008
    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.

  • Anonymous
    May 03, 2008
    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

  • Anonymous
    May 07, 2008
    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

  • Anonymous
    May 09, 2008
    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. -B

  • Anonymous
    May 09, 2008
    The comment has been removed

  • Anonymous
    May 11, 2008
    This sample shows you one way to build an n-tier application using LINQ to SQL classes and a disconnected DataContext.

  • Anonymous
    May 20, 2008
    The comment has been removed

  • Anonymous
    May 23, 2008
    Hello Everyone!  I need help on a project that I am working on; I am a new VB 2005 user and this is my first project.  I have a search form where I have 6 comboboxes and a From date textbox and a To date textbox I have also added a datagridview. I want to be able to create a search by selecting any value from the comboboxes and populate the data into the datagrid. I have CmbPriority, Cbmstatus, Cmbdistrict, Cmbapplication, Cmbgroup, Cmbassignee, FromDatetxt, and ToDatetxtx Any help will be greatly appreciated I have the following, but some how is not working what am I missing. Any help will be greatly appreciated

Private Sub get_searchtable_records() Dim conn As New SqlClient.SqlConnection Dim testsearch_connection As New SqlClient.SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=SupportDesk;server=local") searchtable_string = "SELECT * FROM CallLog " & trimstring 'MsgBox(searchtable_string) searchtable_command = New SqlClient.SqlCommand(searchtable_string, testsearch_connection) 'here is where you would check the boxes and add parameters If CmbSrPriority.Text <> "" Then searchtable_command.Parameters.Add(New SqlClient.SqlParameter("@Priorityid&quot;, SqlDbType.VarChar, 50)).Value = Me.CmbSrPriority.Text End If If CmbSrStatus.Text <> "" Then searchtable_command.Parameters.Add(New SqlClient.SqlParameter("@Statusid&quot;, SqlDbType.VarChar, 50)).Value = Me.CmbSrStatus.Text End If If CmbSrDistrict.Text <> "" Then searchtable_command.Parameters.Add(New SqlClient.SqlParameter("@Districtnumberid&quot;, SqlDbType.VarChar, 50)).Value = Me.CmbSrDistrict.Text End If If CmbSrApplication.Text <> "" Then searchtable_command.Parameters.Add(New SqlClient.SqlParameter("@Applicationid&quot;, SqlDbType.VarChar, 50)).Value = Me.CmbSrApplication.Text End If If CmbSrGroup.Text <> "" Then searchtable_command.Parameters.Add(New SqlClient.SqlParameter("@Groupid&quot;, SqlDbType.VarChar, 50)).Value = Me.CmbSrGroup.Text End If If CmbSrAssignee.Text <> "" Then searchtable_command.Parameters.Add(New SqlClient.SqlParameter("@Assigneeid&quot;, SqlDbType.VarChar, 50)).Value = Me.CmbSrAssignee.Text End If If TextBoxSrFromdate.Text <> "" Then searchtable_command.Parameters.Add(New SqlClient.SqlParameter("@datecreated&quot;, SqlDbType.DateTime, 50)).Value = Me.TextBoxSrFromdate.Text End If If TextBoxSrToDate.Text <> "" Then searchtable_command.Parameters.Add(New SqlClient.SqlParameter("@dateend&quot;, SqlDbType.DateTime, 50)).Value = Me.TextBoxSrToDate.Text End If searchtable_dataadapter = New SqlClient.SqlDataAdapter(searchtable_command) If searchtable_command.Connection.State = ConnectionState.Closed Then searchtable_command.Connection.Open() End If searchtable_table.Clear() 'clear table to not show duplicates after refilling searchtable_dataadapter.Fill(searchtable_table) searchtable_command.Connection.Close() Me.DataGridView1.DataSource = searchtable_table End Sub

  • Anonymous
    July 07, 2008
    At the end of June I run workshops with 2 ISVs both looking to completely re-develop their applications.

  • Anonymous
    July 07, 2008
    At the end of June I run workshops with 2 ISVs both looking to completely re-develop their applications.