Refactor to n-tier

Question

I am a self taught vb6 programmer who uses DAO. Below is an example of a typical piece of code that I could churn out:

Sub cmdMultiplier_Click()  'Button on form, user interface ' 
  dim Rec1 as recordset
  dim strSQL as string

  strSQL = "select * from tblCustomers where ID = " & CurrentCustomerID  'inline SQL '
  set rec1 = GlobalDataBase.openrecordset(strSQL)    ' Data access '

  if rec1.bof <> true or rec1.eof <> true then
    if rec1.fields("Category").value = 1 then
      PriceMultiplier = 0.9         ' Business Logic ' 
    else
      priceMultiplier = 1
    end if
 end if
End Sub

Please pretend that the above is the entire source code of a CRUD application. I know this design is bad, everything is mixed up together. Ideally it should have three distinct layers, user interface, business logic and data access. I sort-of get why this is desirable but I don't know how it's done and I suspect that's why I don't fully get why such a separation is good. I think I'd be a lot further down the road if someone could refactor the above ridiculously trivial example into 3 tiers.

Answer 1

a trivial example, yes, but with all the basic elements - they just belong in 3 different classes (see below). The main reason for this is the "separation of concerns" principle, i.e. the GUI is only concerned with GUI things, the Biz Logic layer is only concerned with the business rules, and the data-access layer is only concerned with data representations. This allows each layer to be maintained independently and reused across applications:

'in Form class - button handler
Sub cmdMultiplier_Click()
    PriceMultiplier = ComputePriceMultiplier(CurrentCustomerId)
End Sub

'in Biz Logic class
Function ComputePriceMultiplier(custId as Integer) as Double
    Dim cust as Customer = GetCustomer(custId)
    if cust.Category = 1 then   'please ignore magic number, real code uses enums
        return 0.9
    end if
    return 1
End Function

'in Data Access Layer class
Function GetCustomer(custId as Integer) as Customer
    Dim cust as Customer = New Customer    'all fields/properties to default values
    Dim strSQL as String = "select * from tblCustomers where ID = " & custId
    set rec1 = GlobalDataBase.openrecordset(strSQL)    ' Data access '
    if rec1.bof <> true or rec1.eof <> true then
        cust.SetPropertiesFromRecord(rec1)
    end if
    return cust
End Function

[a 'real' application would cache the current customer, have constants or stored procedures for the customer query, etc.; ignored for brevity]

Contrast this with your original everything-in-the-button-handler example (which is appallingly common in VB code because it is so easy to do it that way) - if you needed the price-multiplier rule in another application, you'd have to copy, paste, and edit the code into that application's button-handler. Now there would be two places to maintain the same business rule, and two places where the same customer query was executed.