ContactEditor – приложение Excel VBA db: библиотека хранилища и MVP

В предыдущем посте я представил свою библиотеку хранилища. Здесь я хотел бы сосредоточиться на интеграции библиотеки с компонентом MVP.

Отображение функциональных классов

ФигFunctionalClassMapping

Прежде всего, на рисунке показан новый член библиотеки хранилища, DataCompositeManager диспетчер данных, не обсуждаемый в предыдущем посте. Поскольку в пользовательской форме отображается одна запись, загруженная из DataRecordModel и данные загружаются из базы данных в DataTabelModelдополнительный код необходим для передачи данных между двумя классами модели. Для этого DataCompositeManager был введен менеджер, отвечающий за межмодельные переводы.


DataCompositeManager

'@Folder "ContactEditor.Storage.Manager"
'@ModuleDescription "Composite class incorporating one Table and one Record model with backends. Record submodel is used to represent a row from the Table."
'@PredeclaredId
'@IgnoreModule ProcedureNotUsed
'@Exposed
Option Explicit

Private Type TDataCompositeManager
    RecordModel As DataRecordModel
    RecordStorage As IDataRecordStorage
    TableModel As DataTableModel
    TableStorage As IDataTableStorage
End Type
Private this As TDataCompositeManager


Private Sub Class_Initialize()
    Set this.RecordModel = New DataRecordModel
    Set this.TableModel = New DataTableModel
End Sub

Private Sub Class_Terminate()
    Set this.RecordModel = Nothing
    Set this.TableModel = Nothing
End Sub

Public Property Get Record() As Scripting.Dictionary
    Set Record = this.RecordModel.Record
End Property

Public Property Get RecordModel() As DataRecordModel
    Set RecordModel = this.RecordModel
End Property

Public Property Get TableModel() As DataTableModel
    Set TableModel = this.TableModel
End Property

Public Property Get FieldNames() As Variant
    FieldNames = this.TableModel.FieldNames
End Property

Public Property Get Values() As Variant
    Values = this.TableModel.Values
End Property

Public Property Get IDs() As Variant
    IDs = this.TableStorage.GetIds
End Property

Public Sub InitRecord(ByVal ClassName As String, ByVal ConnectionString As String, ByVal TableName As String)
    Set this.RecordStorage = DataRecordFactory.CreateInstance(ClassName, this.RecordModel, ConnectionString, TableName)
End Sub

Public Sub InitTable(ByVal ClassName As String, ByVal ConnectionString As String, ByVal TableName As String)
    Set this.TableStorage = DataTableFactory.CreateInstance(ClassName, this.TableModel, ConnectionString, TableName)
End Sub

Public Sub LoadDataIntoModel()
    this.TableStorage.LoadDataIntoModel
    this.RecordStorage.LoadDataIntoModel
End Sub

Public Sub SaveDataFromModel()
    this.RecordStorage.SaveDataFromModel
    this.TableStorage.SaveDataFromModel
End Sub

Public Sub SaveRecordDataToRecordStorage()
    this.RecordStorage.SaveDataFromModel
End Sub

Public Sub LoadRecordFromTable(ByVal RecordId As String)
    this.TableModel.CopyRecordToDictionary this.RecordModel.Record, RecordId
    this.RecordModel.RecordIndex = this.TableModel.RecordIndexFromId(RecordId)
    this.RecordModel.IsNotDirty
End Sub

Public Sub UpdateRecordToTable()
    this.TableModel.UpdateRecordFromDictionary this.RecordModel.Record
End Sub

На рисунке также показаны компоненты MVP: КонтактыEditorModel (Модель), КонтактыEditorForm (Просмотр) и Связаться с редактором (Ведущий). КонтактыEditorModel инкапсулирует экземпляр DataCompositeManager и выставляет напоказ.


КонтактыEditorModel

'@Folder "ContactEditor.Forms.Contact Editor"
'@IgnoreModule ProcedureNotUsed
'@Exposed
Option Explicit

Public Enum DataPersistenceMode
    DataPersistenceDisabled
    DataPersistenceOnApply
    DataPersistenceOnExit
End Enum

Private Type TContactEditorModel
    RecordTableManager As DataCompositeManager
    PersistenceMode As DataPersistenceMode
    SuppressEvents As Boolean
End Type
Private this As TContactEditorModel


Private Sub Class_Initialize()
    Set this.RecordTableManager = New DataCompositeManager
    this.SuppressEvents = False
End Sub

Private Sub Class_Terminate()
    Set this.RecordTableManager = Nothing
End Sub

Public Property Get RecordTableManager() As DataCompositeManager
    Set RecordTableManager = this.RecordTableManager
End Property

Public Property Get PersistenceMode() As DataPersistenceMode
    PersistenceMode = this.PersistenceMode
End Property

Public Property Let PersistenceMode(ByVal Mode As DataPersistenceMode)
    this.PersistenceMode = Mode
End Property

Public Property Get SuppressEvents() As Boolean
    SuppressEvents = this.SuppressEvents
End Property

Public Property Let SuppressEvents(ByVal Mode As Boolean)
    this.SuppressEvents = Mode
End Property

КонтактыEditorForm является Немодальный, и он определяет несколько настраиваемых событий, обрабатываемых Presenter.


КонтактыEditorForm

'@Folder "ContactEditor.Forms.Contact Editor"
Option Explicit

'''' To avoid issues, populate ComboBox.List with array of strings,
'''' cast if necessary (ComboBox.List column elements used for
'''' ComboBox.Value must have the same type as ComboBox.Value,
'''' otherwise expect runtime errors and glitches.

Implements IDialogView

Public Event FormLoaded()
Public Event LoadRecord(ByVal RecordId As String)
Public Event ApplyChanges()
Public Event FormConfirmed()
Public Event FormCancelled(ByRef Cancel As Boolean)

Private Type TView
    Model As ContactEditorModel
    IsCancelled As Boolean
End Type
Private this As TView


Private Function OnCancel() As Boolean
    Dim cancelCancellation As Boolean: cancelCancellation = False
    RaiseEvent FormCancelled(cancelCancellation)
    If Not cancelCancellation Then Me.Hide
    OnCancel = cancelCancellation
End Function


Private Sub id_Change()
    If this.Model.SuppressEvents Then Exit Sub
    RaiseEvent LoadRecord(id.Value)
End Sub


Private Sub OkButton_Click()
    Me.Hide
    RaiseEvent FormConfirmed
End Sub


Private Sub CancelButton_Click()
    '@Ignore FunctionReturnValueDiscarded
    OnCancel
End Sub


Private Sub ApplyButton_Click()
    RaiseEvent ApplyChanges
End Sub


Private Sub UpdateDisabledRadio_Click()
    this.Model.PersistenceMode = DataPersistenceMode.DataPersistenceDisabled
End Sub


Private Sub UpdateOnApplyRadio_Click()
    this.Model.PersistenceMode = DataPersistenceMode.DataPersistenceOnApply
End Sub


Private Sub UpdateOnExitRadio_Click()
    this.Model.PersistenceMode = DataPersistenceMode.DataPersistenceOnExit
End Sub


Private Sub IDialogView_ShowDialog(ByVal viewModel As Object)
    Set this.Model = viewModel
    Me.Show vbModeless
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = Not OnCancel
    End If
End Sub


Private Sub UserForm_Activate()
    InitializeId
    InitializeAge
    InitializeGender
    InitializeTableUpdating
    RaiseEvent FormLoaded
End Sub


Private Sub InitializeGender()
    Dim listValues() As Variant
    listValues = Array("male", "female")
    Gender.Clear
    Gender.List = listValues
End Sub


Private Sub InitializeAge()
    Dim listValues(18 To 80) As Variant
    Dim AgeValue As Long
    For AgeValue = 18 To 80
        listValues(AgeValue) = CStr(AgeValue)
    Next AgeValue
    
    Age.Clear
    Age.List = listValues
End Sub


Private Sub InitializeId()
    id.Clear
    id.List = this.Model.RecordTableManager.IDs
End Sub


Private Sub InitializeTableUpdating()
    UpdateDisabledRadio.Value = True
End Sub


Private Sub FirstName_Change()
    If this.Model.SuppressEvents Then Exit Sub
    this.Model.RecordTableManager.RecordModel.SetField "FirstName", FirstName.Value
End Sub


Private Sub LastName_Change()
    If this.Model.SuppressEvents Then Exit Sub
    this.Model.RecordTableManager.RecordModel.SetField "LastName", LastName.Value
End Sub


Private Sub Age_Change()
    If this.Model.SuppressEvents Then Exit Sub
    this.Model.RecordTableManager.RecordModel.SetField "Age", Age.Value
End Sub


Private Sub Gender_Change()
    If this.Model.SuppressEvents Then Exit Sub
    this.Model.RecordTableManager.RecordModel.SetField "Gender", Gender.Value
End Sub


Private Sub Email_Change()
    If this.Model.SuppressEvents Then Exit Sub
    this.Model.RecordTableManager.RecordModel.SetField "Email", Email.Value
End Sub


Private Sub Country_Change()
    If this.Model.SuppressEvents Then Exit Sub
    this.Model.RecordTableManager.RecordModel.SetField "Country", Country.Value
End Sub


Private Sub Domain_Change()
    If this.Model.SuppressEvents Then Exit Sub
    this.Model.RecordTableManager.RecordModel.SetField "Domain", Domain.Value
End Sub

Связаться с редактором инициализирует DataCompositeManager член КонтактыEditorModel и инициирует последующие операции в обработчиках событий.


Связаться с редактором

'@Folder "ContactEditor.Forms.Contact Editor"
Option Explicit

'@MemberAttribute VB_VarHelpID, -1
Private WithEvents view As ContactEditorForm

Private Type TPresenter
    Model As ContactEditorModel
    Dialog As IDialogView
End Type
Private this As TPresenter


Public Sub Show(ByVal TableBackEnd As String)
    Set view = New ContactEditorForm
    Set this.Dialog = view
    InitializeModel TableBackEnd
    
    '''' Loads data from the backends into the Model
    this.Model.RecordTableManager.LoadDataIntoModel
    
    this.Dialog.ShowDialog this.Model
End Sub


Private Sub ApplyChanges()
    this.Model.RecordTableManager.SaveRecordDataToRecordStorage
    Select Case this.Model.PersistenceMode
        Case DataPersistenceMode.DataPersistenceOnApply
            this.Model.RecordTableManager.UpdateRecordToTable
            this.Model.RecordTableManager.SaveDataFromModel
        Case DataPersistenceMode.DataPersistenceOnExit
            this.Model.RecordTableManager.UpdateRecordToTable
        Case DataPersistenceMode.DataPersistenceDisabled
            Exit Sub
    End Select
End Sub


Private Sub view_ApplyChanges()
    ApplyChanges
End Sub


Private Sub view_FormLoaded()
    LoadFormFromModel
End Sub


Private Sub LoadFormFromModel()
    this.Model.SuppressEvents = True
        
    Dim FieldName As Variant
    Dim FieldIndex As Long
    Dim FieldNames As Variant: FieldNames = this.Model.RecordTableManager.FieldNames
    For FieldIndex = LBound(FieldNames) To UBound(FieldNames)
        FieldName = FieldNames(FieldIndex)
        view.Controls(FieldName).Value = CStr(this.Model.RecordTableManager.RecordModel.GetField(FieldName))
    Next FieldIndex

    this.Model.SuppressEvents = False
End Sub


Private Sub view_LoadRecord(ByVal RecordId As String)
    If this.Model.RecordTableManager.RecordModel.IsDirty Then
        Dim SaveChanges As Boolean
        SaveChanges = MsgBox("Apply unsaved changes?", vbYesNo + vbExclamation + vbDefaultButton2)
        If SaveChanges Then ApplyChanges
    End If
    this.Model.RecordTableManager.LoadRecordFromTable RecordId
    LoadFormFromModel
End Sub


Private Sub view_FormCancelled(ByRef Cancel As Boolean)
    'setting Cancel to True will leave the form open
    Cancel = MsgBox("Cancel this operation?", vbYesNo + vbExclamation) = vbNo
    If Not Cancel Then
        ' modeless form was cancelled and is now hidden.
        ' ...
        Set view = Nothing
    End If
End Sub


Private Sub view_FormConfirmed()
    'form was okayed and is now hidden.
    '...
    If this.Model.PersistenceMode <> DataPersistenceDisabled Then
        this.Model.RecordTableManager.UpdateRecordToTable
        this.Model.RecordTableManager.SaveDataFromModel
    Else
        this.Model.RecordTableManager.SaveRecordDataToRecordStorage
    End If
    Set view = Nothing
End Sub


'@Description "Instantiates model and binds it to the desired backends."
Private Sub InitializeModel(ByVal TableBackEnd As String)
    Set this.Model = New ContactEditorModel
    
    Dim ClassName As String
    Dim TableName As String
    Dim ConnectionString As String
    
    '''' Binds TableModel to its backend
    Select Case TableBackEnd
        Case "ADODB"
            ClassName = "ADODB"
            TableName = "Contacts"
            ConnectionString = "sqlite:"
        Case "Worksheet"
            ClassName = "Worksheet"
            TableName = "Contacts"
            ConnectionString = ThisWorkbook.Name & "!" & Contacts.Name
        Case "CSV"
            ClassName = "CSV"
            TableName = "Contacts.xsv!sep=,"
            ConnectionString = ThisWorkbook.Path
    End Select
    this.Model.RecordTableManager.InitTable ClassName, ConnectionString, TableName
    
    '''' Binds RecordModel to its backend
    ClassName = "Worksheet"
    TableName = vbNullString
    ConnectionString = ThisWorkbook.Name & "!" & ContactBrowser.Name
    this.Model.RecordTableManager.InitRecord ClassName, ConnectionString, TableName
End Sub

1 ответ
1

Я понял, что было бы предпочтительнее разместить все операции, связанные с DataCompositeManager экземпляр в одном модуле (Presenter). Напоминаю, что причина, по которой я также держал такие операции в пределах CONTROL_Change обработчики в форме code-behind: я думал, что для каждого экземпляра элемента управления в Presenter потребуется атрибут уровня модуля WithEvents. Я просто понял, что могу сохранить CONTROL_Change обработчики в форме code-behind и передают пары control_name / new_value в Presenter для дальнейшей обработки.

В КонтактыEditorForm, Я должен был определить событие настраиваемой формы:

Public Event FieldChanged(ByVal FieldName As String, ByVal NewValue As Variant)

и изменили обработчики

Private Sub FIELDNAME_Change()
    If this.Model.SuppressEvents Then Exit Sub
    this.Model.RecordTableManager.RecordModel.SetField "FIELDNAME", FIELDNAME.Value
End Sub

в (см. обработчик id_Change)

Private Sub FIELDNAME_Change()
    If this.Model.SuppressEvents Then Exit Sub
    RaiseEvent FieldChanged("FIELDNAME", FIELDNAME.Value)
End Sub

Тогда в пределах Связаться с редактором, Я мог бы сделать

Private Sub view_FieldChanged(ByVal FieldName As String, ByVal NewValue As Variant)
    this.Model.RecordTableManager.RecordModel.SetField FieldName, NewValue
End Sub

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *