Option Explicit

Public Function GetById(ByVal id As Long) As SqlResultRow
    'because, all my tables have an Id primary key.
End Function

Public Function GetAll() As SqlResult
End Function

Public Function Count() As Long
End Function

Public Sub Add(ByVal value As SqlResultRow)
End Sub

Public Sub Remove(ByVal id As Long)
End Sub

Public Sub Update(ByVal id As Long, ByVal value As SqlResultRow)
End Sub

Public Function NewItem(ByVal model As SqlResult, ByVal values As Dictionary) As SqlResultRow
    'creates a new item, out of a Scripting.Dictionary containing.
    'model: contains the field names.
    'values: field names as key, field values for values.
End Function

Dim description As String
description = row("description")

sql = "SELECT Foo, DateInserted FROM FooBar WHERE DateInserted > ?;"
Set result = cmd.QuickExecute(sql, Now - 30)
For Each row In result
    Debug.Print row.ToString ' prints a CSV list of the row's values
Next

Option Explicit
Private cmd As New SqlCommand
Implements IRepository

Public Function NewItem(ByVal model As SqlResult, ByVal values As Dictionary) As SqlResultRow

    Dim result As New SqlResultRow

    Dim items() As String
    ReDim items(LBound(values.items) To UBound(values.items))

    Dim i As Integer
    For i = LBound(values.items) To UBound(values.items)
        items(i) = values.items(i)
    Next

    Set NewItem = result.Mock(model, items)

End Function

Public Sub Add(ByVal value As SqlResultRow)

    Dim sql As String
    sql = "INSERT INTO Planning.CustomerGroups (Description, DateInserted) VALUES (?, ?);"

    cmd.QuickExecuteNonQuery sql, value("description"), Now

End Sub

Public Function GetAll() As SqlResult

    Dim sql As String
    sql = "SELECT Id, Description FROM Planning.CustomerGroups ORDER BY Id;"

    Set GetAll = cmd.QuickExecute(sql)

End Function

Public Function GetById(ByVal id As Long) As SqlResultRow

    Dim sql As String
    sql = "SELECT Id, Description FROM Planning.CustomerGroups WHERE Id = ?;"

    Set GetById = cmd.QuickSelectFirstRow(sql, id)

End Function

Public Sub Remove(ByVal id As Long)

    Dim sql As String
    sql = "DELETE FROM Planning.CustomerGroups WHERE Id = ?;"

    cmd.QuickExecuteNonQuery sql, id

End Sub

Public Sub Update(ByVal id As Long, ByVal value As SqlResultRow)

    Dim sql As String
    sql = "UPDATE Planning.CustomerGroups SET Description = ?, DateUpdated = ? WHERE Id = ?;"

    cmd.QuickExecuteNonQuery sql, value("description"), Now, value("id")

End Sub

Public Function Count() As Long

    Dim sql As String
    sql = "SELECT COUNT(*) FROM Planning.CustomerGroups;"

    Count = cmd.QuickSelectSingleValue(sql)

End Function

Private Sub IRepository_Add(ByVal value As SqlResultRow)
    Add value
End Sub

Private Function IRepository_Count() As Long
    IRepository_Count = Count
End Function

Private Function IRepository_GetAll() As SqlResult
    Set IRepository_GetAll = GetAll
End Function

Private Function IRepository_GetById(ByVal id As Long) As SqlResultRow
    Set IRepository_GetById = GetById(id)
End Function

Private Function IRepository_NewItem(ByVal model As SqlResult, ByVal values As Scripting.IDictionary) As SqlResultRow
    Set IRepository_NewItem = NewItem(model, values)
End Function

Private Sub IRepository_Remove(ByVal id As Long)
    Remove id
End Sub

Private Sub IRepository_Update(ByVal id As Long, ByVal value As SqlResultRow)
    Update id, value
End Sub

Option Explicit
Private FieldNames As List
Private Items As List
Implements IRepository
Implements IStringRepresentable

Public Sub SetModel(model As SqlResult)

    Set FieldNames = model.FieldNames
    Set Items = New List

    Dim row As SqlResultRow
    For Each row In model
        Items.Add row
    Next

End Sub

Private Function MockSqlResult() As SqlResult

    Dim result As New SqlResult

    Dim name As Variant
    For Each name In FieldNames
        result.AddFieldName name
    Next

    Dim row As SqlResultRow
    For Each row In Items
        result.AddValue row
    Next

    Set MockSqlResult = result

End Function

Public Function Create(model As SqlResult) As MockRepository

    Dim result As New MockRepository
    result.SetModel model

    Set Create = result

End Function

Public Sub Add(ByVal value As SqlResultRow)

    Dim newId As Long
    newId = Items.Count + 1
    If Items.Last("id") <= newId Then
        newId = Items.Last("id") + 1
    End If

    value("id") = newId
    Items.Add value

End Sub

Public Function GetAll() As SqlResult
    Set GetAll = MockSqlResult
End Function

Public Function GetById(ByVal id As Long) As SqlResultRow
    Set GetById = Items(id)
    Dim row As SqlResultRow
    For Each row In Items
        If row("id") = id Then
            Set GetById = row
            Exit Function
        End If
    Next
End Function

Public Sub Remove(ByVal id As Long)
    Items.Remove GetById(id)
End Sub

Public Sub Update(ByVal id As Long, ByVal value As SqlResultRow)

    Dim row As SqlResultRow
    Set row = GetById(id)

    Dim i As Integer
    For i = 1 To value.FieldCount
        If LCase(FieldNames(i)) <> "id" Then
            row(i - 1) = value(i - 1)
        End If
    Next

End Sub

Public Function Count() As Long
    Count = Items.Count
End Function

Public Function NewItem(ByVal values As Dictionary) As SqlResultRow

    Dim result As New SqlResultRow
    Dim i As Integer

    Dim model As New SqlResult
    For i = LBound(values.Keys) To UBound(values.Keys)
        model.AddFieldName values.Keys(i)
    Next

    For i = LBound(values.Items) To UBound(values.Items)
        result.AddValue values.Items(i)
    Next

    Set result.ParentResult = model
    Set NewItem = result

End Function

Public Function ToString() As String
    Dim result As String

    Dim Item As IStringRepresentable
    For Each Item In Items
        result = result & Item.ToString & vbNewLine
    Next

    ToString = result
End Function

Private Sub IRepository_Add(ByVal value As SqlResultRow)
    Add value
End Sub

Private Function IRepository_Count() As Long
    IRepository_Count = Count
End Function

Private Function IRepository_GetAll() As SqlResult
    Set IRepository_GetAll = GetAll
End Function

Private Function IRepository_GetById(ByVal id As Long) As SqlResultRow
    Set IRepository_GetById = Items(id)
End Function

Private Sub IRepository_Remove(ByVal id As Long)
    Remove id
End Sub

Private Sub IRepository_Update(ByVal id As Long, ByVal value As SqlResultRow)
    Update id, value
End Sub

Private Function IRepository_NewItem(ByVal model As SqlResult, ByVal values As Dictionary) As SqlResultRow
    Set IRepository_NewItem = NewItem(values)
End Function

Private Function IStringRepresentable_ToString() As String
    IStringRepresentable_ToString = ToString
End Function

Option Explicit

Public Sub TestMaintainCustomerGroups()

    Dim CustomerGroups As New MockRepository
    Set CustomerGroups = CustomerGroups.Create(GetMockCustomerGroupsModel)

    Dim Customers As New MockRepository
    Set Customers = Customers.Create(GetMockCustomersModel)

    Dim presenter As New CustomerGroupsPresenter
    Set presenter.CustomerGroupsRepo = CustomerGroups
    Set presenter.CustomersRepo = Customers

    presenter.Show

End Sub

Private Function GetMockCustomerGroupsModel() As SqlResult

    Dim model As New SqlResult
    model.AddFieldName "Id"
    model.AddFieldName "Description"

    Dim row As SqlResultRow
    Dim i As Integer
    For i = 1 To 10

        Set row = New SqlResultRow
        Set row.ParentResult = model

        row.AddValue i
        row.AddValue "Test" & i

        model.AddValue row

    Next

    Set GetMockCustomerGroupsModel = model

End Function

Private Function GetMockCustomersModel() As SqlResult

    Dim model As New SqlResult
    model.AddFieldName "Id"
    model.AddFieldName "Code"
    model.AddFieldName "Name"
    model.AddFieldName "CustomerGroupId"

    Dim row As SqlResultRow
    Dim i As Integer
    For i = 1 To 10

        Set row = New SqlResultRow
        Set row.ParentResult = model

        row.AddValue i
        row.AddValue 1000 + i
        row.AddValue "Customer" & i
        row.AddValue 1

        model.AddValue row

    Next

    Set GetMockCustomersModel = model

End Function