Database Classes: Creating a Business Object Based on Data in a Database

By Ken Getz and Mike Gilbert

In this month's installment of "Class Act," we'll tackle the task of creating a database class, i.e. a class module that encapsulates database functionality. One of the best uses for class modules is to abstract something complex and make it simpler. This idea fits in well to the database development environment because data access methods can be difficult to understand. What developers building business solutions want is a way to program against business objects (like customers, invoices, etc.), not database objects. In this article we'll show you one way to create a simple business object that's based on data in a database.

Note: The sample code for this month's column is contained in an Excel 97 workbook (DCLASS.XLS) and uses Microsoft's new version 1.5 of ActiveX Data Objects (ADO). If you don't have ADO 1.5 installed on your computer, you can download it for free at http://www.microsoft.com/data/.

The Basic Architecture

The purpose of a database class is to wrap the processes involved in database access (opening a connection, reading from a table, saving changes, etc.) into a class and expose them as properties (the fields) and methods (the operations). As an example, we've created a class that encapsulates data from the Shippers table in Northwind.mdb — the sample database that ships with Microsoft Access. This is a simple table with three fields: SupplierID (an AutoNumber field), CompanyName, and Phone. Each of the fields is represented by a set of property procedures in the class module.

The class also features several methods, including Find, Save, Delete, Refresh, and MakeNew. These map directly to the database operations we mentioned earlier. Later, we'll explain how the code for these works.

Preparing the Class Module

The Shipper class module declares a number of private variables in its Declarations section (see FIGURE 1). The most important declaration is for the ADO Recordset object. The class uses this object to communicate with and control the database. While we've used ADO, you could have just as easily used another technology such as DAO, RDO, or the ODBC API.

FIGURE 1: The private variables of Shipper's Declaration section.

Option Explicit

' ADO objects.
Private prst As Recordset

' Base SQL statement for query.
Private Const conSQL = "SELECT * FROM Shippers"

' Connect string.
Private pstrConnect As String

' Private variables for field values.
Private plngID As Long
Private pstrCompany As String
Private pstrPhone As String

' Internal state variables.
Private pfModified As Boolean
Private pfAdding As Boolean

We've used a constant to store the base SQL query for creating the Recordset. Methods defined in the class will modify this, for example, to locate a particular shipper's record. Note that we've selected all the fields from the Shippers table using the SQL * operator. This is fine, because the table only has three fields. For performance reasons, you should only select those fields you're actually going to use in the class.

We declared the ADO connect string variable, pstrConnect, at the module level because we'll be setting it in the class' Initialize event and using it later on.

Three variables, plngID, pstrCompany, and pstrPhone, represent field values from the Shippers table. When the class reads data from the table it will store it in these variables. Likewise, as programs modify data, the changes will be stored in these variables until written back to the database.

Lastly, we declared two Boolean variables to hold state information, such as whether changes being saved apply to an existing record or a new one.

The Life Span of a Database Class

We designed the simple class described in this article to manipulate a single record in a database. As such, you might use several instances of the class in an application. As with any other class, start by creating a new instance:

' Declare the Shipper object.
Dim objShpr As Shipper

' Create a new instance of the class.
Set objShpr = New Shipper

When an instance of the class is created, several things must happen. First, the class instance must connect to a database. Second, it must create a blank Recordset so new records can be immediately added. Code in the Class_Initialize procedure takes care of this (see FIGURE 2).

FIGURE 2: Connecting to a database and creating a blank Recordset.

Private Sub Class_Initialize()

  ' Get the connect string from the Windows Registry.
  pstrConnect = GetSetting(gconAppName, gconKeyName, _
                           gconConnect)
    
  If Len(pstrConnect) Then
    ' Create a new ADO Recordset.
    Set prst = New Recordset
        
    ' Execute a query to open recordset with no records.
    prst.Open conSQL & " WHERE False", pstrConnect, _
              adOpenKeyset, adLockPessimistic
  Else
    ' No connect string info.
    Err.Raise vbObjectError + 1001, _
              "Shipper::Initialize", _
              "Connect string information not found."
  End If

End Sub

You'll notice that we've stored information about the connection in the Windows registry and retrieved it using the GetSetting function. You could also store information in global variables or objects. Another approach would be to pass it to the class instance as a property setting. The downside to this, however, is that you can't do it until after the instance is initialized.

Note: For the sample code to work on your computer, you'll need to provide connection information, either in the registry, or by modifying the source code, to use another method. Provided that you installed Microsoft Access in the default directory, you can use the DCLASS.REG file included with the sample code to create the required registry entry.

When a program is finished using the class instance, the Terminate event cleans up by dropping the connection:

Private Sub Class_Terminate()

  ' Drop the connection.
  If Not prst Is Nothing Then
    On Error Resume Next
    prst.Close
    Set prst = Nothing
  End If

End Sub

Retrieving Data

To use the class effectively, you must be able to locate a particular record based on some criteria. In the case of the Shipper class, this is done by specifying the shipper's ID number or name in the class' Find method.

The code that locates the record is shown in FIGURE 3. Notice that both arguments, ID and Company, are declared as Optional. This lets the programmer choose how to locate a given shipper.

FIGURE 3: Locating a particular record based on the shipper's ID number or name.

Public Function Find(Optional ID As Long = -1, _
  Optional Company As String = "") As Boolean
 
  Dim strWhere As String
    
  ' Determine criteria to use.
  If ID = -1 Then
    strWhere = "CompanyName = '" & Company & "'"
  Else
    strWhere = "ShipperID = " & ID
  End If
 
  ' Requery database based on supplied criteria.
  With prst
    .Close
    .Open conSQL & " WHERE " & strWhere, pstrConnect, _
          adOpenKeyset, adLockPessimistic
    ' Refresh and set return value.
    If .EOF Then
      Find = False
    Else
      Me.Refresh
      Find = True
    End If
  End With

End Function

When a record is found, the Find method calls the class' Refresh method. This method, shown in FIGURE 4, moves data from the current record in the Recordset to private variables in the class module. The IsValid property called by the Refresh method simply checks the Recordset's EOF and BOF properties to make sure a valid record exists in the Recordset.

FIGURE 4: Moving data from the current record in the Recordset to private variables in the class module.

Public Sub Refresh()
  ' If there is a current record, copy
  ' the field values to private variables.
    
  If Me.IsValid Then
    ' Put field values into variables.
    plngID = prst("ShipperID")
    pstrCompany = prst("CompanyName")
    pstrPhone = prst("Phone")
        
    ' Set state variables,
    pfAdding = False
    pfModified = False
  End If

End Sub

Once data is stored in the private variables, it can be accessed using the class' property procedures. Here's an example of the property procedures for the Company property:

Property Get Company() As String
  Company = pstrCompany
End Property

Property Let Company(strCompany As String)
  pstrCompany = strCompany
  pfModified = True
End Property

The pfModified flag is set to indicate that data has been changed by the Property Let procedure.

An example of how you would use the class to locate a record and print a property value (the telephone number in this case) to the Immediate window is shown in FIGURE 5. Note that we used a named parameter in the Find method to search on company name rather than the default, ShipperID.

FIGURE 5: Locating a record and printing a property value.

Sub FindShipper()

  Dim objShpr As Shipper
    
  ' Create a new class instance.
  Set objShpr = New Shipper
    
  With objShpr
    ' Find Federal Shipping.
    If .Find(Company:="Federal Shipping") Then
      ' Print the phone number.
      Debug.Print "Phone: " & .Phone
    Else
      ' Print error message.
      Debug.Print "Didn't find record."
    End If
  End With
    
  Set objShpr = Nothing

End Sub

Updating and Creating Records

Once you've located a record, and perhaps inspected the existing property values, you may want to update them and commit the changes back to the database. Changing the property values is accomplished by using property procedures to modify the private variables where the data is stored. As you saw earlier, we use a Boolean variable to denote when a property value is changed. If the program using the class wants to tell if a record has been changed, it can call the IsDirty property:

Property Get IsDirty() As Boolean
  ' Return value of modified flag.
  IsDirty = pfModified
End Property

You can use property procedures to validate changes to data, based on business rules. In the case of the Shippers table, the ShipperID field is an AutoNumber field and thus non-updateable. Therefore the Property Let procedure returns an error when called:

Property Let ID(lngID As Long)
  ' This is an AutoNumber field.
  Err.Raise vbObjectError + 1002, "Shipper::ID (Let)", _
            "Property is read-only."
End Property

After making changes to property values, a programmer can call the Save method to commit those changes to the database (see FIGURE 6). In essence, the Save method is just the converse of the Refresh method. Instead of copying data from the Recordset to private variables, it copies data from the variables to the Recordset. The only difference is the methods required to commit the changes to the database.

FIGURE 6: Calling the Save method to commit the changes to a database.

Public Sub Save()

  ' Only do this if values have changed.
  If pfModified Then
    With prst
      ' Call appropriate method.
      If pfAdding Then
        .AddNew
      Else
        ' ADO doesn't require an Edit method.
      End If
            
      ' Copy variables to field values.
      .Fields("CompanyName") = pstrCompany
      .Fields("Phone") = pstrPhone
            
      ' Update record.
      .Update
            
      ' If adding, get new AutoNumber value.
      If pfAdding Then
        .MoveLast
        plngID = .Fields("ShipperID")
      End If
    End With

  End If

End Sub

Finally, the Save method checks a Boolean variable, pfAdding, to see if the method is being called in response to a new record being created. This variable is set when the MakeNew method is called (see FIGURE 7). Then, after the newly created record is saved, the Save method calls the Recordset's MoveLast method to pick up the newly created AutoNumber value. Obviously, this would not be necessary if you didn't use AutoNumber fields in your tables.

FIGURE 7: Calling the MakeNew method.

Public Sub MakeNew()

  ' Clear private variables.
  plngID = 0
  pstrCompany = ""
  pstrPhone = ""
    
  ' Set state variables.
  pfAdding = True
  pfModified = False

End Sub

FIGURE 8 is an example of how you find use the Save method to modify a record. This example locates a shipper, changes the area code of the phone number, and saves the change.

FIGURE 8: Locating a shipper, changing the area code of the phone number, and saving the changes.

Sub ModifyShipper()

  Dim objShpr As Shipper
    
  ' Create new class instance.
  Set objShpr = New Shipper
    
  With objShpr
    ' Locate shipper.
    If .Find(ID:=3) Then
      ' Change area code.
      .Phone = "(425)" & Mid(.Phone, 6)
      .Save
    End If
  End With
    
  Set objShpr = Nothing

End Sub

Conclusion

We've created a very simple class to demonstrate the concept of using class modules to encapsulate database data. We are the first to admit that it's not terribly sophisticated, but we hope you can see the value in doing this. Now a developer can write code to control a business object (e.g. a shipper), rather than worry about how the program interacts with a database.

In future installments of "Class Act," we'll explore this concept further, showing you how to deal with multiple records and relationships between tables in a database.

Download source code for this article here.

Ken Getz is a Senior Consultant with MCW Technologies, a Microsoft Solution Provider focusing on Visual Basic and the Office and BackOffice suites. Mike Gilbert is Technical Product Manager for Office Developer Edition and VBA Licensing at Microsoft. They are also co-authors of VBA Developer's Handbook and Access 97 Developer's Handbook (with Paul Litwin), both for SYBEX.