I need to build a little UI to allow easy maintenance of some data stored in a MySQL database, and the end user wants to do that in Excel (I know, I know).
The code I came up with comes pretty close to what I'd call Model-View-Presenter (MVP).
I can get my form shown like this:
set x = new CustomerGroupsPresenter
set x.Command = new SqlCommand
x.Show
Obviously this code uses the SqlCommand ADODB wrapper that I've written earlier (for VB6), reviewable here.
Here's the form's code-behind:
Public Event CloseCommand(ByVal sender As CustomerGroupsView)
Public Event EditCustomerGroupCommand(ByVal id As Long, ByVal description As String)
Public Event AddCustomerGroupCommand()
Public Event DeleteCustomerGroupCommand(ByVal id As Long)
Option Explicit
Private Sub AddCustomerGroupButton_Click()
RaiseEvent AddCustomerGroupCommand
End Sub
Private Sub CloseButton_Click()
RaiseEvent CloseCommand(Me)
End Sub
Private Sub CustomerGroupsList_Change()
DeleteCustomerGroupButton.Enabled = Not (CustomerGroupsList.ListIndex = -1)
End Sub
Private Sub CustomerGroupsList_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim selectedRecord() As String
selectedRecord = Split(CustomerGroupsList.List(CustomerGroupsList.ListIndex), StringFormat("\t"))
Dim id As Long
id = CLng(selectedRecord(0))
Dim description As String
description = selectedRecord(1)
RaiseEvent EditCustomerGroupCommand(id, description)
End Sub
Private Sub DeleteCustomerGroupButton_Click()
Dim selectedRecord() As String
selectedRecord = Split(CustomerGroupsList.List(CustomerGroupsList.ListIndex), StringFormat("\t"))
Dim id As Long
id = CLng(selectedRecord(0))
RaiseEvent DeleteCustomerGroupCommand(id)
End Sub
..and the Presenter class:
Private cmd As SqlCommand
Private WithEvents vwCustomerGroups As CustomerGroupsView
Option Explicit
Public Property Get Command() As SqlCommand
Set Command = cmd
End Property
Public Property Set Command(ByRef value As SqlCommand)
Set cmd = value
End Property
Public Sub Show()
Set vwCustomerGroups = New CustomerGroupsView
RefreshCustomerGroups
vwCustomerGroups.Show vbModal
End Sub
Private Function View() As CustomerGroupsView
Set View = vwCustomerGroups
End Function
Private Sub RefreshCustomerGroups()
vwCustomerGroups.CustomerGroupsList.Clear
Dim sql As String
sql = "SELECT Id, Description FROM Planning.CustomerGroups ORDER BY Id;"
Dim groups As SqlResult
Set groups = cmd.QuickExecute(sql)
groups.ValueSeparator = StringFormat("\t")
Dim group As SqlResultRow
For Each group In groups
vwCustomerGroups.CustomerGroupsList.AddItem group.ToString
Next
End Sub
Private Sub vwCustomerGroups_AddCustomerGroupCommand()
Dim description As String
description = InputBox("Please enter a description for the new CustomerGroup:", "Edit", "(new customer group)")
If StrPtr(description) = 0 Or description = vbNullString Then Exit Sub
Dim sql As String
sql = "INSERT INTO Planning.CustomerGroups (Description, DateInserted) VALUES (?, ?);"
Dim success As Boolean
success = cmd.QuickExecuteNonQuery(sql, description, Now)
If Not success Then MsgBox "Insert operation failed!", vbExclamation, "Warning"
RefreshCustomerGroups
End Sub
Private Sub vwCustomerGroups_CloseCommand(ByVal sender As CustomerGroupsView)
sender.Hide
End Sub
Private Sub vwCustomerGroups_DeleteCustomerGroupCommand(ByVal id As Long)
Dim sql As String
sql = "SELECT COUNT(*) FROM Planning.Customers WHERE CustomerGroupId = ?;"
Dim childRecords As Long
childRecords = CLng(cmd.QuickSelectSingleValue(sql, id))
If childRecords > 0 Then
MsgBox StringFormat("This CustomerGroup has {0} customer(s) associated to it and cannot be deleted.", childRecords), vbExclamation, "FK Constraint violation!"
Exit Sub
End If
If MsgBox(StringFormat("Delete CustomerGroup #{0}?\n(this cannot be undone!)", id), vbExclamation + vbYesNo, "Please confirm") = vbNo Then Exit Sub
sql = "DELETE FROM Planning.CustomerGroups WHERE Id = ?;"
Dim success As Boolean
success = cmd.QuickExecuteNonQuery(sql, id)
If Not success Then MsgBox "Delete operation failed!", vbExclamation, "Warning"
RefreshCustomerGroups
End Sub
Private Sub vwCustomerGroups_EditCustomerGroupCommand(ByVal id As Long, ByVal description As String)
Dim newDescription As String
newDescription = InputBox(StringFormat("Please enter a new description for CustomerGroup ID#{0}:", id), "Edit", description)
If StrPtr(newDescription) = 0 Then Exit Sub
Dim sql As String
sql = "UPDATE Planning.CustomerGroups SET Description = ?, DateUpdated = ? WHERE Id = ?;"
Dim success As Boolean
success = cmd.QuickExecuteNonQuery(sql, newDescription, Now, id)
If Not success Then MsgBox "Update operation failed!", vbExclamation, "Warning"
RefreshCustomerGroups
End Sub
I feel like I could move the SQL outside the Presenter class and into some data service class.. but would that be overkill?
Besides the missing error-handling, what's there to say about this code?
View()function, I forgot to remove it... it's just a relic from a previous approach / dead code now. \$\endgroup\$SqlResultRowimplements anIStringRepresentableinterface that exposes aToStringmethod - should I not declaregroup as IStringRepresentable? \$\endgroup\$