Object-Oriented database design with the DatabaseObjects library

• Download demo project (VB6) - 361 Kb
• Download demo project (.NET) - 602 Kb
• Download source (VB6) - 130 Kb
• Download source (.NET) - 125 Kb

Introduction

The DatabaseObjects library was designed to aid in creating object-oriented database systems quickly and easily. It achieves this with a set of generic functions and interfaces that automatically generate all of the necessary SQL statements to load, save, search, delete and enumerate a database, sourced from either Microsoft Access, SQL Server or MySQL. The code examples below use the VB6 version of the library, however a fully functional .NET version of the library is also available. Demonstration programs for both VB6 and .NET are also available to help you get started.

Concept

Implementing a database table using classes and the DatabaseObjects library involves creating two classes. The first class, represents a database table, while the second class represents each database record in the table. For the classes to "hook into" the library, the first class (or the collection class) must implement the IDatabaseObjects interface while the second class must implement the IDatabaseObject interface. By implementing each of the IDatabaseObjects' and IDatabaseObject's functions, the collection class can specify which table it is tied to, the table's unique field, whether it should only represent a subset of the table, how the table should be sorted, if there are any related tables, etc., while the second class can specify how a database record is to be copied to and from the class. With the interfaces implemented, the DatabaseObjects library can then automatically generate the appropriate SQL statements for the common database functions such as inserting a new record, updating an existing record, searching through a table, enumerating through a set of records, returning the number of records in a table, etc.

The diagram below depicts how a Products database table might be implemented using the library. Two classes would be required; a Products class that implements the IDatabaseObjects interface, and a Product class that implements the IDatabaseObject interface. Once the interfaces have been implemented, the library can then be used with the DatabaseObjects library's set of predefined, generic functions to automatically generate and execute the necessary SQL statements. For example, the Count property in the Products class could call one of the predefined DatabaseObjects functions: ObjectsCount. This function creates an SQL statement using the value returned from IDatabaseObjects_TableName (in this case "Products") to generate the following: SELECT COUNT(*) FROM Products. The SQL statement is then executed and the result returned. If the DBO.ObjectsCount function was called by passing a Customers class which had implemented the IDatabaseObjects_TableName, to return "Customers", then the DBO.ObjectsCount function would generate and execute the statement: SELECT COUNT(*) FROM Customers. Using this basic technique, the DatabaseObjects library can automatically generate the appropriate SQL for most situations.

Code Example

This example demonstrates using the DatabaseObjects library with the Products table from Microsoft's Northwind database. The Microsoft Access version of the Northwind database is included with Visual Basic 6 and by default is located at: C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb. The following example assumes that the database exists at this location - although this can be changed. The database can also be downloaded here from the Microsoft website. A MySQL version of the Northwind database is also bundled with the demonstration program.

1. Run Visual Basic
2. Create a new ActiveX DLL
3. Rename Project1 to NorthwindDB
4. Rename Class1 to Products
5. Add a new class, name it Product
6. Add the DBO library project
   1. Select File > Add Project
   2. Select the Existing tab
   3. Navigate to the DBO.vbp project
   4. Select Open
7. Select the NorthwindDB project
8. Select Project > References
9. Select the DBO library and the Microsoft ActiveX Data Objects 2.8 Library references

Product Class

1. Open the Product class
2. At the top of the class, type Implements IDatabaseObject
3. Select the IDatabaseObject from the object list (combo box at the top left)
4. Select each function (combo box at the top right) to automatically generate the IDatabaseObject functions. All of the functions must be implemented even if they are to be left blank.
5. Fill in the remainder of the class so that it looks like the following:

'Product class
Implements IDatabaseObject

Public Name As String
Public UnitPrice As Currency

Private plngProductID As Long 

'Implement the IDatabaseObject functions

Private Property Let IDatabaseObject_DistinctValue(ByVal RHS As Variant)

    'This function is called when the Product is loaded or 
    'this is a new object which has not yet been saved and is identified
    'by an identity or autoincrement field 
    '(IDatabaseObjects_DistinctFieldIsAnIdentityField)

    'Store the Product ID
    plngProductID = RHS

End Property

Private Property Get IDatabaseObject_DistinctValue() As Variant

    'Return the distinct value for the Product which 
    'in this case is the ProductID (plngProductID)
    IDatabaseObject_DistinctValue = plngProductID

End Property

Private Property Get IDatabaseObject_IsSaved() As Boolean

    'Return whether the object has been saved to the database
    'If the object is new then plngProductID will be 0

    'This property is essentially used to determine whether to 
    'perform either an INSERT or UPDATE SQL command
    
    IDatabaseObject_IsSaved = plngProductID <> 0

End Property

Private Property Let IDatabaseObject_IsSaved(ByVal RHS As Boolean)

End Property

Private Sub IDatabaseObject_Load(ByVal objFields As DBO.SQLFieldValues)

    'objFields will be populated with all of the fields 
    'from 1 record of the Products' table
    'Copy the fields from the database (via objFields) 
    'and store them in the appropriate variables
    Me.Name = objFields("ProductName")
    Me.UnitPrice = objFields("UnitPrice")

End Sub

Private Function IDatabaseObject_Save() As DBO.SQLFieldValues

    Dim objFields As SQLFieldValues
    Set objFields = New SQLFieldValues

    objFields.Add "ProductName", Me.Name
    objFields.Add "UnitPrice", Me.UnitPrice

    Set IDatabaseObject_Save = objFields

End Function

Products Class

1. Open the Products class
2. At the top of the class type Implements IDatabaseObjects
3. Just as before, select the IDatabaseObjects from the object list (combo box at the top left)
4. Select each function (combo box at the top right) to automatically generate the IDatabaseObjects functions. All of the functions must be implemented.
5. Fill in the remainder of the class so that it looks like the following:

'Products class

Implements IDatabaseObjects

Public Property Get Enumerator() As IUnknown 

    'This property will allow a For Each enumeration to be used
    'colProducts must be declared static otherwise it will be released 
    'at the end of the function call and the enumerator will become invalid 

    Static colProducts As Collection
    Set colProducts = dbo.ObjectsCollection(Me)

    Set Enumerator = colProducts.[_NewEnum]

End Property
  

'Implement the IDatabaseObjects functions

Private Function IDatabaseObjects_DistinctFieldAutoIncrements() As Boolean

    'The ProductID field is an automatically incrementing field
    IDatabaseObjects_DistinctFieldAutoIncrements = True

End Function

Private Function IDatabaseObjects_DistinctFieldName() As String
    
    'The ProductID field uniquely identifies each product record in the table
    IDatabaseObjects_DistinctFieldName = "ProductID"

End Function

Private Function IDatabaseObjects_ItemInstance() As dbo.IDatabaseObject
    
    'Return a new instance of the class that is associated with this collection
    Set IDatabaseObjects_ItemInstance = New Product

End Function

Private Function IDatabaseObjects_KeyFieldName() As String

    'The ProductName field is also unique 
    'within the product table and can be
    'used by the DBO.ObjectByKey function
    IDatabaseObjects_KeyFieldName = "ProductName"

End Function

Private Function IDatabaseObjects_OrderBy() _
                 As DBO.SQLSelectOrderByFields

    'When enumerating through the collection 
    '(using ObjectByOrdinal or ObjectsCollection)
    'then the Product objects should be ordered by ProductName    
    Set IDatabaseObjects_OrderBy = New SQLSelectOrderByFields
    IDatabaseObjects_OrderBy.Add "ProductName", dboOrderAscending

End Function

Private Function IDatabaseObjects_TableName() As String
    
    'Return the database table that this collection uses
    IDatabaseObjects_TableName = "Products"

End Function

Private Function IDatabaseObjects_Subset() As dbo.SQLConditions
     
    'Leave this function blank to include all of the product records

End Function

Private Function IDatabaseObjects_TableJoins(ByVal objPrimaryTable As _
        SQLSelectTable, ByVal objTables As dbo.SQLSelectTables) _
        As dbo.SQLSelectTableJoins
     
End Function

1. Enable the For Each enumeration
   1. Select Tools > Procedure Attributes
   2. Select Enumerator in the Name list
   3. Click Advanced
   4. Enter -4 for the 'Procedure ID'
   5. Click OK
   6. This will allow the For Each command to be used on the Products collection

NorthwindDatabase Class

1. Add a new class to the NorthwindDB project, name it NorthWindDatabase
2. Set the instancing property for the NorthWindDatabase class to 6 - GlobalMultiUse
3. Add the following code to the NorthWindDatabase class
4. This will connect to the nwind.mdb database. The nwind.mdb database is available here at the Microsoft website.

Public Sub Connect()

    Const cstrDatabaseFilePath As String = _
          "C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"

    Dim strConnection As String

    strConnection = _
        "Data Source=" & cstrDatabaseFilePath & ";" & _
        "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;"
 
    DBO.Connect strConnection, dboConnectionTypeMicrosoftAccess

End Sub

Public Property Get Products() As Products

    Set Products = New Products

End Property

EXE Project

1. Select File > Add Project
2. Select Standard EXE
3. Rename Project1 to DBOTest
4. Right click on DBOTest and select Set As Startup
5. Select the DBOTest project
6. Select Project > References
7. Select the NorthwindDB reference
8. Add a text box to Form1
9. Set the MultiLine property to True
10. Set the ScrollBars property to 2 - Vertical
11. Clear the Text property
12. Paste the following code into Form1:

Private Sub Form_Load()

    Dim objProduct As Product
    
    NorthwindDB.Connect

    For Each objProduct In NorthWindDB.Products
        Me.Text1.Text = Me.Text1.Text & objProduct.Name _
                        & "  -  " & _
                        FormatCurrency(objProduct.UnitPrice) _
                        & vbCrLf
    Next    
End Sub

That's it! Run the program and a list of products and their prices will be displayed in the text box sorted in ascending order - all using just classes! And feel free to add more fields to the Product class, change the ordering in IDatabaseObjects_OrderBy, or use some of the other library functions. The VB6 and .NET demonstration programs also include a number of additional examples; including master/detail table relationships with Northwind's Orders and Order Details tables, building complex search queries using the SQLSelect class, and improving load times with table joins.

The .NET version also includes some additional MustInherit/abstract classes: DatabaseObjects, DatabaseObjectsEnumerable, and DatabaseObject which still implement the IDatabaseObjects and IDatabaseObject interfaces but provide a more intuitive and simplified interface to the library. In addition, the demonstration includes a copy of the Northwind database for use with MySQL. If you are using .NET version 2, then the NorthwindDatabase project also includes a LateboundObject generic version (see references to DOT_NET_VERSION_2 in the code).

For more information and examples see the reference guide and demonstration program available at the website http://www.hisystems.com.au/databaseobjects in the downloads section.

History

• 31 Jan 2007 - updated downloads