File Watcher BEGIN/ROLLBACK functionality

[neil.interactit]

Hi there,

I have a watcher with the following (pseudo)code ...

Code: Select all

    Private Sub ComCareSalesOrderImport(ByVal FileName As String)
        Dim salesOrder As JiwaSales.SalesOrder.SalesOrder = JiwaApplication.Manager.Instance.BusinessLogicFactory.CreateBusinessLogic(Of JiwaSales.SalesOrder.SalesOrder)(Nothing)
        '...
        Do While Not csvParser.EndOfData
            '...
            salesOrder.CreateNew(JiwaSales.SalesOrder.SalesOrder.NewSalesOrderTypes.e_NewSalesOrder, debtor.DebtorID)
            '...
            For Each txn In txns
                '...
                salesOrder.SalesOrderLines.AddInventoryItem(inventory.InventoryID, JiwaSales.SalesOrder.SalesOrderLineCollection.SalesOrderLineInventorySeedTypes.e_SalesOrderLineInventoryID, newKey)
                '...
            Next
            If salesOrder.SalesOrderLines.Count > 0 Then salesOrder.Save()
        Loop
    End Sub


The trouble is, if there is a failure due to either erroneous CSV data or because prerequisites haven't been entered (typically a debtor not yet created in Jiwa), then the import fails (with error reporting) AT THAT POINT, however all previous CSV lines have succeeded, leaving Jiwa partially updated by the batch. The client requirement is that on any failure the whole batch is to be ignored ... they will then remedy the error (create the debtor, etc) and drop the CSV in again. So I need some sort of BEGIN/ROLLBACK functionality.

I am considering the following ...

Code: Select all

    Private Sub ComCareSalesOrderImport(ByVal FileName As String)
        Dim salesOrders = New List(Of JiwaSales.SalesOrder.SalesOrder)
        '...
        Do While Not csvParser.EndOfData
            Dim salesOrder As JiwaSales.SalesOrder.SalesOrder = JiwaApplication.Manager.Instance.BusinessLogicFactory.CreateBusinessLogic(Of JiwaSales.SalesOrder.SalesOrder)(Nothing)
            '...
            salesOrder.CreateNew(JiwaSales.SalesOrder.SalesOrder.NewSalesOrderTypes.e_NewSalesOrder, debtor.DebtorID)
            '...
            For Each txn In txns
                '...
                salesOrder.SalesOrderLines.AddInventoryItem(inventory.InventoryID, JiwaSales.SalesOrder.SalesOrderLineCollection.SalesOrderLineInventorySeedTypes.e_SalesOrderLineInventoryID, newKey)
                '...
            Next
            If salesOrder.SalesOrderLines.Count > 0 Then salesOrders.Add(salesOrder)
        Loop
        For Each salesOrder As JiwaSales.SalesOrder.SalesOrder In salesOrders
            salesOrder.Save()
        Next
    End Sub


This will (well, should) work - as the error handling will gracefully exit (with reporting) before any .Save()s. Is this the best approach, or is there built in functionality for this that I am missing (or simply a better approach)?

Cheers,
Neil

[Mike.Sheen]

Hi Neil,

If you start a new transaction before the batch, the sales order (and everything else) will use your started transaction automatically and then you just catch any exceptions and then perform a rollback - or if no exceptions enountered you do a commit.

We follow this pattern in all of our SQL updates in our business logic :

Code: Select all

Dim weStartedTransaction As Boolean

With JiwaApplication.Manager.Instance.Database
   Try
      If .SQLTransaction Is Nothing Then
         ' flag that we created the transaction, so we know if we should be the ones to .commit or .rollback later.
         .BeginNewTransaction()
         weStartedTransaction = True
      End If
      
      ... Do some stuff here
      
      If weStartedTransaction Then
         .Commit()
      End If

   Catch ex As Exception      
      If weStartedTransaction AndAlso Not JiwaApplication.Manager.Instance.Database.SQLTransaction Is Nothing AndAlso Not JiwaApplication.Manager.Instance.Database.SQLTransaction.Connection Is Nothing Then
         JiwaApplication.Manager.Instance.Database.SQLTransaction.Rollback()
         JiwaApplication.Manager.Instance.Database.SQLTransaction.Dispose()
         JiwaApplication.Manager.Instance.Database.SQLTransaction = Nothing
      End If
      Throw ' Only need to throw if you want to signal to the caller there was a problem, or rather than throw you can deal with the exception here
   End Try
End With


Mike

[neil.interactit]

Hi Mike,

Thanks! Works a treat. I refactored slightly with a touch of IOC so that I could use this approach for all the watchers without repeating the .SQLTransaction code.

Code: Select all

    Public Delegate Sub FileImportDelegate(FileName As String)

    Private Shared Sub WithRollback(DoFileImport As FileImportDelegate, ByVal FileName As String)
        Dim weStartedTransaction As Boolean
        With JiwaApplication.Manager.Instance.Database
            Try
                If .SQLTransaction Is Nothing Then
                    ' flag that we created the transaction, so we know if we should be the ones to .commit or .rollback later.
                    .BeginNewTransaction()
                    weStartedTransaction = True
                End If

                DoFileImport(FileName)

                If weStartedTransaction Then
                    .Commit()
                End If

            Catch ex As System.Exception
                If weStartedTransaction AndAlso Not JiwaApplication.Manager.Instance.Database.SQLTransaction Is Nothing AndAlso Not JiwaApplication.Manager.Instance.Database.SQLTransaction.Connection Is Nothing Then
                    JiwaApplication.Manager.Instance.Database.SQLTransaction.Rollback()
                    JiwaApplication.Manager.Instance.Database.SQLTransaction.Dispose()
                    JiwaApplication.Manager.Instance.Database.SQLTransaction = Nothing
                End If
                Throw ' Only need to throw if you want to signal to the caller there was a problem, or rather than throw you can deal with the exception here
            End Try
        End With
    End Sub


And now I can leave the actual watcher code unchanged, and simply update the call from:

Code: Select all

AddHandler ComCareSalesOrderImportWatcher.FileImport, AddressOf ComCareSalesOrderImport

to:

Code: Select all

AddHandler ComCareSalesOrderImportWatcher.FileImport, Sub(FileName As String) WithRollback(AddressOf ComCareSalesOrderImport, FileName)

Beautiful! Thanks again.
Neil.

[Mike.Sheen]

Hi Mike,
Thanks! Works a treat.
...
Beautiful! Thanks again.
Neil.

Hi Neil,

I'm glad to have helped, and I'm even more glad our efforts in designing the framework of Jiwa are now being utilised for producing scalable, robust solutions!.

Validation is a sweet reward indeed

Mike