I needed a way of attaching multiple repositories to a presenter. Given I already had an IRepository abstraction, coming up with an IUnitOfWork seemed the next logical step.

Here's how this code helped solve the presenter-that-isn't-a-presenter problem - I simply add the two repositories I need, and the presenter can call methods on both UnitOfWork.Repository("ExcludedOrders") and UnitOfWork.Repository("Orders"), so I could get rid of the OrderHeaderPresenter empty shell:

I needed a way of attaching multiple repositories to a presenter. Given I already had an IRepository abstraction, coming up with an IUnitOfWork seemed the next logical step.

Here's how this code helped solve the presenter-that-isn't-a-presenter problem - I simply add the two repositories I need, and the presenter can call methods on both UnitOfWork.Repository("ExcludedOrders") and UnitOfWork.Repository("Orders"), so I could get rid of the OrderHeaderPresenter empty shell:

The UoW opens a connection upon being created, and initiates a transaction too. Because I wanted the client code to be able to commit multiple transactions using the same UoW instance, I'm initiating a new transaction every time I Commit or Rollback.

The UoW opens a connection upon being created and, since it's a unit of work, initiates a transaction. Because I wanted the client code to be able to commit multiple transactions using the same UoW instance, I'm initiating a new transaction every time I Commit or Rollback.

Option Explicit

Private Const CONNECTION_STRING As String = "DRIVER={MySQL ODBC 5.1 Driver};UID=;PWD=;SERVER=;DATABASE=;PORT=;"

Private repositories As New Dictionary
Private adoConnection As New ADODB.Connection
Private cmd As New SqlCommand

Implements IUnitOfWork

Private Sub Class_Initialize()
    adoConnection.ConnectionString = CONNECTION_STRING
    adoConnection.Open
    adoConnection.BeginTrans
End Sub

Private Sub Class_Terminate()
    On Error GoTo ErrHandler
    
    Set repositories = Nothing
    If Not adoConnection Is Nothing Then
        If adoConnection.State = adStateOpen Then
            adoConnection.Close
        End If
        Set adoConnection = Nothing
    End If
    
    Exit Sub
    
ErrHandler:
    Err.Raise Err.number, Err.source, Err.description, Err.HelpFile, Err.HelpContext
End Sub

Public Sub AddRepository(ByVal key As String, ByRef repo As IRepository)
    repo.SetConnection adoConnection
    repositories.Add key, repo
End Sub

Public Property Get Repository(ByVal key As String) As IRepository
    Set Repository = repositories(key)
End Property

Public Sub Commit()
    On Error GoTo ErrHandler
    
    adoConnection.CommitTrans
    adoConnection.BeginTrans
    
    Exit Sub
    
ErrHandler:
    Err.Raise Err.number, Err.source, Err.description, Err.HelpFile, Err.HelpContext
End Sub

Public Sub Rollback()
    On Error GoTo ErrHandler
    
    adoConnection.RollbackTrans
    adoConnection.BeginTrans
    
    Exit Sub
    
ErrHandler:
    Err.Raise Err.number, Err.source, Err.description, Err.HelpFile, Err.HelpContext
End Sub

Private Sub IUnitOfWork_AddRepository(ByVal key As String, ByRef repo As IRepository)
    AddRepository key, repo
End Sub

Private Sub IUnitOfWork_Commit()
    Commit
End Sub

Private Property Get IUnitOfWork_Repository(ByVal key As String) As IRepository
    Set IUnitOfWork_Repository = Repository(key)
End Property

Private Sub IUnitOfWork_Rollback()
    Rollback
End Sub

Public Sub MaintainExcludedOrders()
    
    On Error GoTo ErrHandler
    If IsBusy Then Exit Sub

    Dim orders As New OrderHeaderRepository
    Dim exclOrders As New ExcludedOrdersRepository
    
    Dim uow As New UnitOfWork
    uow.AddRepository "ExcludedOrders", exclOrders
    uow.AddRepository "Orders", orders
    
    Dim presenter As New ExcludedOrdersPresenter
    Set presenter.UnitOfWork = uow
    
    SetupSimplePresenter presenter, _
                         GetResourceString("ExcludedOrdersTitle"), _
                         GetResourceString("ExcludedOrdersInstructionsText"), _
                         CRUD
    
    presenter.Show

CleanExit:
    Set uow = Nothing
    Set presenter = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.description, vbCritical, GENERIC_ERR_MSG
    Resume CleanExit
End Sub

Option Explicit

Private Const CONNECTION_STRING As String = "DRIVER={MySQL ODBC 5.1 Driver};UID=;PWD=;SERVER=;DATABASE=;PORT=;"

Private repositories As New Dictionary
Private adoConnection As New ADODB.Connection
Private cmd As New SqlCommand

Implements IUnitOfWork

Private Sub Class_Initialize()
    adoConnection.ConnectionString = CONNECTION_STRING
    adoConnection.Open
    adoConnection.BeginTrans
End Sub

Private Sub Class_Terminate()
    On Error GoTo ErrHandler
    
    Set repositories = Nothing
    If Not adoConnection Is Nothing Then
        If adoConnection.State = adStateOpen Then
            adoConnection.RollbackTrans 'rollback any uncommitted changes
            adoConnection.Close
        End If
        Set adoConnection = Nothing
    End If
    
    Exit Sub
    
ErrHandler:
    Err.Raise Err.number, Err.source, Err.description, Err.HelpFile, Err.HelpContext
End Sub

Public Sub AddRepository(ByVal key As String, ByRef repo As IRepository)
    repo.SetConnection adoConnection
    repositories.Add key, repo
End Sub

Public Property Get Repository(ByVal key As String) As IRepository
    Set Repository = repositories(key)
End Property

Public Sub Commit()
    On Error GoTo ErrHandler
    
    adoConnection.CommitTrans
    adoConnection.BeginTrans
    
    Exit Sub
    
ErrHandler:
    Err.Raise Err.number, Err.source, Err.description, Err.HelpFile, Err.HelpContext
End Sub

Public Sub Rollback()
    On Error GoTo ErrHandler
    
    adoConnection.RollbackTrans
    adoConnection.BeginTrans
    
    Exit Sub
    
ErrHandler:
    Err.Raise Err.number, Err.source, Err.description, Err.HelpFile, Err.HelpContext
End Sub

Private Sub IUnitOfWork_AddRepository(ByVal key As String, ByRef repo As IRepository)
    AddRepository key, repo
End Sub

Private Sub IUnitOfWork_Commit()
    Commit
End Sub

Private Property Get IUnitOfWork_Repository(ByVal key As String) As IRepository
    Set IUnitOfWork_Repository = Repository(key)
End Property

Private Sub IUnitOfWork_Rollback()
    Rollback
End Sub

Public Sub MaintainExcludedOrders()
    
    On Error GoTo ErrHandler
    If IsBusy Then Exit Sub

    Dim orders As New OrderHeaderRepository
    Dim exclOrders As New ExcludedOrdersRepository
    
    Dim uow As New UnitOfWork
    uow.AddRepository "ExcludedOrders", exclOrders
    uow.AddRepository "Orders", orders
    
    Dim presenter As New ExcludedOrdersPresenter
    Set presenter.UnitOfWork = uow
    
    SetupSimplePresenter presenter, _
                         GetResourceString("ExcludedOrdersTitle"), _
                         GetResourceString("ExcludedOrdersInstructionsText"), _
                         CRUD
    
    presenter.Show

CleanExit:
    Set uow = Nothing
    Set presenter = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.description, vbCritical, GENERIC_ERR_MSG
    Resume CleanExit
End Sub

One concern I have, is that the cleanup code must explicitly Set presenter.UnitOfWork = Nothing for the Set uow = Nothing instruction to call Class_Terminate and close the connection; the above CleanExit code doesn't do that, and Class_Terminate doesn't get called. This works as expected:

CleanExit:
    Set presenter.UnitOfWork = Nothing
    Set uow = Nothing
    Set presenter = Nothing
    Exit Sub

I'd like suggestions for a cleaner way of... cleaning up :)