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.

Product Class

Select the IDatabaseObject from the object list (combo box at the top left)

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.

Fill in the remainder of the class so that it looks like the following:

'Product class
Implements IDatabaseObject
Public Name AsStringPublic UnitPrice As Currency
Private plngProductID AsLong'Implement the IDatabaseObject functions
PrivatePropertyLet IDatabaseObject_DistinctValue(ByVal RHS AsVariant)
'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
EndPropertyPrivatePropertyGet IDatabaseObject_DistinctValue() AsVariant'Return the distinct value for the Product which
'in this case is the ProductID (plngProductID)
IDatabaseObject_DistinctValue = plngProductID
EndPropertyPrivatePropertyGet IDatabaseObject_IsSaved() AsBoolean'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 <> 0EndPropertyPrivatePropertyLet IDatabaseObject_IsSaved(ByVal RHS AsBoolean)
EndPropertyPrivateSub 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")
EndSubPrivateFunction 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
EndFunction

Products Class

Open the Products class

At the top of the class type Implements IDatabaseObjects

Just as before, select the IDatabaseObjects from the object list (combo box at the top left)

Select each function (combo box at the top right) to automatically generate the IDatabaseObjects functions. All of the functions must be implemented.

Fill in the remainder of the class so that it looks like the following:

'Products class
Implements IDatabaseObjects
PublicPropertyGet 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]
EndProperty'Implement the IDatabaseObjects functions
PrivateFunction IDatabaseObjects_DistinctFieldAutoIncrements() AsBoolean'The ProductID field is an automatically incrementing field
IDatabaseObjects_DistinctFieldAutoIncrements = TrueEndFunctionPrivateFunction IDatabaseObjects_DistinctFieldName() AsString'The ProductID field uniquely identifies each product record in the table
IDatabaseObjects_DistinctFieldName = "ProductID"EndFunctionPrivateFunction IDatabaseObjects_ItemInstance() As dbo.IDatabaseObject
'Return a new instance of the class that is associated with this collection
Set IDatabaseObjects_ItemInstance = New Product
EndFunctionPrivateFunction IDatabaseObjects_KeyFieldName() AsString'The ProductName field is also unique
'within the product table and can be
'used by the DBO.ObjectByKey function
IDatabaseObjects_KeyFieldName = "ProductName"EndFunctionPrivateFunction 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
EndFunctionPrivateFunction IDatabaseObjects_TableName() AsString'Return the database table that this collection uses
IDatabaseObjects_TableName = "Products"EndFunctionPrivateFunction IDatabaseObjects_Subset() As dbo.SQLConditions
'Leave this function blank to include all of the product records
EndFunctionPrivateFunction IDatabaseObjects_TableJoins(ByVal objPrimaryTable As _
SQLSelectTable, ByVal objTables As dbo.SQLSelectTables) _
As dbo.SQLSelectTableJoins
EndFunction

Enable the For Each enumeration

Select Tools > Procedure Attributes

Select Enumerator in the Name list

Click Advanced

Enter -4 for the 'Procedure ID'

Click OK

This will allow the For Each command to be used on the Products collection

NorthwindDatabase Class

Add a new class to the NorthwindDB project, name it NorthWindDatabase

Set the instancing property for the NorthWindDatabase class to 6 - GlobalMultiUse

Add the following code to the NorthWindDatabase class

This will connect to the nwind.mdb database. The nwind.mdb database is available here at the Microsoft website.

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).

History

31 Jan 2007 - updated downloads

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

Funnily enough, I just added in transaction support a few weeks ago for the .NET version of the library. If you are interesting in using the VB6 version then I can add in support for that if you wish. I am going to have to add it in at some stage soon anyway.

I've posted the new code on my website www.hisystems.com.au. Just go to the downloads section. I've submitted the code for update on CodeProject but that may take a day or two. Please let me know if you have any questions.