Quick Start for VB6

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 represent each database record in the table. For the classes to "hook into" the library the first class (or collection class) must implement the IDatabaseObjects interface while the second class must implement the IDatabaseObject interface. By implementing each of the IDatabaseObjects' and IDatabaseObject 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 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 IDatabaseObjects_TableName to return "Customers" then the DBO.ObjectsCount function would generate and execute the statement: SELECT COUNT(*) FROM Customers This basic technique is used by the DatabaseObjects library and it's set of generic functions.

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 modified. The database can also be downloaded here from the Microsoft website.

1. Run Visual Basic 6
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 a unique field within the product table 'and is 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 'If this function returns Nothing then the records are not sorted 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 'Leave this function blank to not join to any tables 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. 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

For more information and examples see the reference guide and demonstration program. Both are available here.