I’ve been reading a book about Design Patterns. This particular book has Java sample code. It is written by Robert C Martin and is called Agile Software Development Principles, Patterns, and Practices.

I had been trying to wrap my brain around separating database interactions on forms from business logic and being able to test business logic separately from dealing with how the form is loading and saving the object information to the database.

This Proxy Design Pattern is meant to make that separation. Really it is a design pattern to separate boundaries of anything in code. So something like an API vs your business logic as well.

Using a really simple example, I’ll show how this pattern is meant to work.

Let’s just use a basic customer as an example. We will have a customer class that implements all the functionality for a customer. In this case, we will just have an ID, a name and an IsValid() method. IsValid will return true if the customer name and ID are filled with a non-zero long integer for ID and a non-zero length string for customer name.

'Class Module: Customer
Public CustName As String
Public CustID As Long

Public Function IsValid() As Boolean
   If Len(CustName)>0 And CustID<>0 Then IsValid = True Else IsValid = False
End Function

'Class Module: CustomerProxy
Private pCustomer As Customer

Public Sub Load(ID As Long)
   If pCustomer Is Nothing Then Set pCustomer = New Customer
   Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset("SELECT * FROM CustomerTable WHERE ID=" & ID)
   pCustomer.CustName = rs!CustName
   pCustomer.CustID = rs!ID
End Sub

Public Sub Save()
   Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset("UPDATE CustomerTable SET CustName='" & _
      pCustomer.CustName & "' WHERE ID=" & pCustomer.CustID)
End Sub

Public Function busCustomer() As Customer
   Set busCustomer = pCustomer
End Function

'Form Backend Code: frmCustomer
'It is bound to CustomerTable and we are assuming no new records and no deletions right now
' just loading and saving a Customer
Private proxyCustomer As CustomerProxy

Private Sub Form_Load()
   Set proxyCustomer = New CustomerProxy
   'It would probably be better to just pass the form recordset to the proxy
   ' now that I'm thinking about it.
   proxyCustomer.Load(Me.CustomerID)
End Sub

Private Sub btnSave_Click()
   proxyCustomer.busCustomer.CustID = Nz(Me.CustomerID,0)
   proxyCustomer.busCustomer.CustName = Nz(Me.CustomerName,"")
   If proxyCustomer.busCustomer.IsValid() Then
      proxyCustomer.Save
   Else
      MsgBox "Customer form has invalid data, please correct before saving"
   End If
End Sub

This is a pretty simple example, but hopefully you can see how you could easily re-use the Customer object and easily write tests against it to make sure the business logic works as intended.

The ProxyCustomer is what knows about the database and how to load and save the data, but actually, after I wrote this, I think it would make more sense for the ProxyCustomer object to piggyback off the recordset from the form to get the data and load the Customer object. Then all the loading and saving of the data from the table is done inside the form. The ProxyCustomer in that case would be passed the form’s recordset. The Load and Save functions could just load from or update the recordset of the form.

Anyway, the example is here to show how you can insert a Proxy class that acts as a helper to prepare the Customer business object in order to run business logic functions on it. In this case, that’s simply the IsValid function, but we could imagine adding more functions for the customer such as retrievePastOrder or getShippingAddress or findTaxRate.

Although those would all need more data likely from the database, that could all be loaded via the proxy and displayed or processed as needed.