Click here to Skip to main content
12,623,882 members (30,023 online)
Click here to Skip to main content
Articles » Database » Database » Databases » Downloads

Stats

87.7K views
3.4K downloads
63 bookmarked
Posted

Object-Oriented database design with the DatabaseObjects library

, 31 Jan 2007
Demonstrates creating object-oriented database systems with the DatabaseObjects library.
VB.NET
demo
source
Northwind
NorthwindDB
bin
Northwind.vbproj.user
AxInterop.SHDocVw.dll
DatabaseObjects.dll
Interop.SHDocVw.dll
Northwind.exe
NorthwindDatabase.dll
NorthwindDatabase.vbproj.user
Attributes
bin
Database
DatabaseObjects.vbproj.user
Misc
SQL
DatabaseObjects.dll
Data
mysql_northwind.zip
Documentation
DatabaseObjects.chm
VB6
demo
regsvr32.exe
source
Northwind
Northwind.vbg
NorthwindDB
ControlAnchor.cls
frmMain.frm
frmMain.frx
frmOrder.frm
frmOrder.frx
frmProducts.frm
frmProducts.frx
frmProductSearch.frm
frmProductSearch.frx
frmProductSearchExtended.frm
frmProductSearchExtended.frx
frmSuppliers.frm
frmSuppliers.frx
Northwind.exe
northwind.RES
Northwind.vbp
Categories.cls
Category.cls
Data.bas
LateBoundObject.cls
NorthwindDatabase.cls
NorthwindDatabase.OBJ
NorthwindDB.dll
NorthwindDB.vbp
Order.cls
OrderDetail.cls
OrderDetails.cls
Orders.cls
Product.cls
Products.cls
ProductSearch.cls
Supplier.cls
Suppliers.cls
bin
Database.cls
DBO.vbp
GlobalDatabase.cls
GlobalDatabaseObjects.cls
IDatabaseObject.cls
IDatabaseObjects.cls
IGlobalDatabaseObjects.cls
ISQLStatement.cls
modMisc.bas
SQLAlterTable.cls
SQLAutoIncrementValue.cls
SQLCondition.cls
SQLConditionFieldCompare.cls
SQLConditionInSelect.cls
SQLConditions.cls
SQLConditionSelect.cls
SQLCreateIndex.cls
SQLCreateTable.cls
SQLDelete.cls
SQLDropIndex.cls
SQLDropTable.cls
SQLFieldValue.cls
SQLFieldValues.cls
SQLIndexExists.cls
SQLIndexField.cls
SQLIndexFields.cls
SQLInsert.cls
SQLSelect.cls
SQLSelectField.cls
SQLSelectFields.cls
SQLSelectGroupByField.cls
SQLSelectGroupByFields.cls
SQLSelectOrderByField.cls
SQLSelectOrderByFields.cls
SQLSelectTable.cls
SQLSelectTableJoin.cls
SQLSelectTableJoinCondition.cls
SQLSelectTableJoinConditions.cls
SQLSelectTableJoins.cls
SQLSelectTables.cls
SQLTableExists.cls
SQLTableField.cls
SQLTableFields.cls
SQLUpdate.cls
SQLUpdateCopyField.cls
SQLUpdateCopyFields.cls
DatabaseObjects_vb6.dll
mysql_northwind.zip
images
templates
databaseobjects_classdiagram.jpg
logo.jpg
DatabaseObjects.vbproj.user
DatabaseObjects.dll
Database.cls
DBO.vbp
GlobalDatabase.cls
GlobalDatabaseObjects.cls
IDatabaseObject.cls
IDatabaseObjects.cls
IGlobalDatabaseObjects.cls
ISQLStatement.cls
modMisc.bas
SQLAlterTable.cls
SQLAutoIncrementValue.cls
SQLCondition.cls
SQLConditionFieldCompare.cls
SQLConditionInSelect.cls
SQLConditions.cls
SQLConditionSelect.cls
SQLCreateIndex.cls
SQLCreateTable.cls
SQLDelete.cls
SQLDropIndex.cls
SQLDropTable.cls
SQLFieldValue.cls
SQLFieldValues.cls
SQLIndexExists.cls
SQLIndexField.cls
SQLIndexFields.cls
SQLInsert.cls
SQLSelect.cls
SQLSelectField.cls
SQLSelectFields.cls
SQLSelectGroupByField.cls
SQLSelectGroupByFields.cls
SQLSelectOrderByField.cls
SQLSelectOrderByFields.cls
SQLSelectTable.cls
SQLSelectTableJoin.cls
SQLSelectTableJoinCondition.cls
SQLSelectTableJoinConditions.cls
SQLSelectTableJoins.cls
SQLSelectTables.cls
SQLTableExists.cls
SQLTableField.cls
SQLTableFields.cls
SQLUpdate.cls
SQLUpdateCopyField.cls
SQLUpdateCopyFields.cls
DatabaseObjects_vb6.dll
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><!-- InstanceBegin template="/templates/main.dwt" codeOutsideHTMLIsLocked="false" -->
<head>
<!-- InstanceBeginEditable name="doctitle" -->
<title>Database Objects</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<!-- InstanceBeginEditable name="head" -->
<!-- InstanceEndEditable -->
<link href="default.css" rel="stylesheet" type="text/css">
</head>

<body>
<h1>
<!-- InstanceBeginEditable name="header" -->
Quick Start<!-- InstanceEndEditable --></h1>
<!-- InstanceBeginEditable name="body" -->
<script language="JavaScript">document.linkColor = "black"</script>
<p>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 &quot;hook into&quot; the library the first class (or collection
  class) must implement the <strong>IDatabaseObjects</strong> interface while
  the second class must implement the <strong>IDatabaseObject</strong> interface.
  By implementing each of the <strong>IDatabaseObjects</strong>' and <strong>IDatabaseObject</strong> 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.</p>
<p>The diagram below depicts how a <strong>Products</strong> database table might
  be implemented using the library. Two classes would be required; a <strong>Products </strong>class
  that implements the <strong>IDatabaseObjects</strong> interface and a<strong> Product</strong> class
  that implements the <strong>IDatabaseObject</strong> 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 <strong>Count </strong>property
  in the <strong>Products</strong> class could call one of the predefined DatabaseObjects
  functions: <strong>ObjectsCount</strong>. This function creates an SQL statement
  using the value returned from <strong>IDatabaseObjects_TableName</strong> (in
  this case &quot;Products&quot;) to generate the following: <code>SELECT COUNT(*)
  FROM Products</code>. The SQL statement is then executed and the result returned.
  If the <strong>DBO.ObjectsCount</strong> function was called by passing a <strong>Customers</strong> class
  which had implemented <strong> IDatabaseObjects_TableName</strong> to return &quot;Customers&quot; then
  the <strong>DBO.ObjectsCount</strong> function would generate and execute the
  statement:<code> SELECT COUNT(*) FROM Customers</code>. This basic technique
  is used by the DatabaseObjects library and it's set of generic functions.</p>
<p>&nbsp;</p>
<p><img src="images/databaseobjects_classdiagram.jpg" width="479" height="764"></p>
<h3><strong>Code Example</strong></h3>
<p>This example demonstrates using the DatabaseObjects library with the <strong>Products</strong> 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: <em>C:\Program
  Files\Microsoft Visual Studio\VB98\nwind.mdb. </em>The following example assumes
  that the database exists at this location - although this can be modified.
  The database can also be downloaded <a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=EN">here</a> from
the Microsoft website. A MySQL version of the Northwind database is also bundled
  with the demonstration
program.</p>
<ol>
<li>Run <strong>Visual Basic 6</strong></li>
  <li>Create a new <strong>ActiveX DLL</strong></li>
  <li>Rename <strong>Project1</strong> to <strong>NorthwindDB</strong></li>
  <li>Rename <strong>Class1</strong> to <strong>Products</strong></li>
  <li>Add a new class, name it <strong>Product</strong></li>
  <li>Add the DBO library project
    <ol>
      <li>Select <strong>File &gt; Add Project</strong></li>
        <li>Select the <strong>Existing</strong> tab</li>
        <li>Navigate to the <strong>DBO.vbp</strong> project</li>
        <li>Select <strong>Open</strong></li>
    </ol>
  </li>
  <li>Select the <strong>NorthwindDB</strong> project</li>
  <li>Select <strong>Project &gt; References</strong></li>
  <li>Select the <strong>DBO</strong> library and the<strong> Microsoft ActiveX
      Data Objects 2.8 Library </strong>references</li>
</ol>
<h3><strong><br>
Product Class</strong></h3>
<ol>
  <li>Open the <strong>Product</strong> class</li>
  <li>At the top of the class type <strong>Implements IDatabaseObject</strong></li>
  <li>Select the <strong>IDatabaseObject</strong> from the object list (combo box at the top left)</li>
  <li>Select each  function (combo box at the top right) to automatically
      generate the <strong>IDatabaseObject </strong>functions. All of the functions
    must be implemented even if they are to be left blank.</li>
  <li>Fill in the remainder of the class so that it looks like the following:</li>
</ol>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td bgcolor="#f6f6f6"><pre><span class="comment">
'Product class</span> 
<span class="keyword">Implements</span> <span class="keyword"><a href="IDatabaseObject.htm">IDatabaseObject</a>

Public </span>Name <span class="keyword">As String<br>Public </span>UnitPrice<span class="keyword"> As Currency<br></span>
<span class="keyword">Private</span> plngProductID<span class="keyword"> As Long</span> 
<span class="comment">
'Implement the IDatabaseObject functions</span>

<span class="keyword">Private Property Let</span> <a href="IDatabaseObject_DistinctValue.htm">IDatabaseObject_DistinctValue</a>(<span class="keyword">ByVal</span> RHS <span class="keyword">As Variant</span>)
<span class="comment">
    '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 (<a href="IDatabaseObjects_DistinctFieldAutoIncrements.htm">IDatabaseObjects_DistinctFieldIsAnIdentityField</a>)

    'Store the Product ID
</span>    plngProductID = RHS

<span class="keyword">End Property</span>

<span class="keyword">Private Property Get</span> <a href="IDatabaseObject_DistinctValue.htm">IDatabaseObject_DistinctValue</a>() <span class="keyword">As Variant</span>
<span class="comment">
</span><span class="comment">    'Return the distinct value for the Product which in this case is the ProductID (plngProductID)</span>
    IDatabaseObject_DistinctValue = plngProductID

<span class="keyword">End Property</span>

<span class="keyword">Private Property Get</span> <a href="IDatabaseObject_IsSaved.htm">IDatabaseObject_IsSaved</a>() <span class="keyword">As Boolean</span>

<span class="comment">    '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
</span>    
    IDatabaseObject_IsSaved = plngProductID &lt;&gt; 0

<span class="keyword">End Property</span>

<span class="keyword">Private Property Let</span> <a href="IDatabaseObject_IsSaved.htm">IDatabaseObject_IsSaved</a>(<span class="keyword">ByVal</span> RHS <span class="keyword">As Boolean</span>)

<span class="keyword">End Property</span>

<span class="keyword">Private Sub</span> <a href="IDatabaseObject_Load.htm">IDatabaseObject_Load</a>(<span class="keyword">ByVal</span> objFields <span class="keyword">As</span> DBO.SQLFieldValues)

<span class="comment">    '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
</span>    Me.Name = objFields(&quot;ProductName&quot;)<br>    Me.UnitPrice = objFields(&quot;UnitPrice&quot;)<br>
<span class="keyword">End Sub</span>

<span class="keyword">Private Function </span><a href="IDatabaseObject_Save.htm">IDatabaseObject_Save</a>() <span class="keyword">As</span> DBO.SQLFieldValues

    <span class="keyword">Dim</span> objFields <span class="keyword">As</span> SQLFieldValues<br>    <span class="keyword">Set</span> objFields = <span class="keyword">New</span> SQLFieldValues

    objFields.Add &quot;ProductName&quot;, Me.Name<br>    objFields.Add &quot;UnitPrice&quot;, Me.UnitPrice

    <span class="keyword">Set</span> IDatabaseObject_Save = objFields

<span class="keyword">End Function</span></pre>
</td>
  </tr>
</table>
<h3><br>
Products Class</h3>
<ol><li>Open the <strong>Products</strong> class</li>
  <li>At the top of the class type <strong>Implements IDatabaseObjects</strong></li>
  <li>Just as before, select the <strong>IDatabaseObjects</strong> from the object
    list (combo box at the top left)</li>
  <li>Select each function (combo box at the top right) to automatically generate
    the <strong>IDatabaseObjects </strong>functions. All of the functions must
    be implemented.</li>
  <li>Fill in the remainder of the class so that it looks like the following:</li>
</ol>
<table width="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#f6f6f6">
  <tr>
    <td><pre><span class="comment">
'Products class</span>

<span class="keyword">Implements</span> <a href="IDatabaseObjects.htm">IDatabaseObjects</a><br>
<span class="keyword">Public Property Get</span> Enumerator() <span class="keyword">As</span> IUnknown 

<span class="comment">    'This property will allow a For Each enumeration to be used</span>
<span class="comment">    'colProducts must be declared static otherwise it will be released 
    'at the end of the function call and the enumerator will become invalid 
</span>
   <span class="keyword"> Static</span> colProducts <span class="keyword">As</span> Collection
    <span class="keyword">Set</span> colProducts = dbo.<a href="DatabaseObjects_ObjectsCollection.htm">ObjectsCollection</a>(Me)<br>
    <span class="keyword">Set</span> Enumerator = colProducts.[_NewEnum]

<span class="keyword">End Property</span><br>  <br>
<span class="comment">'Implement the IDatabaseObjects functions</span><span class="keyword">

Private Function</span> <a href="IDatabaseObjects_DistinctFieldAutoIncrements.htm">IDatabaseObjects_DistinctFieldAutoIncrements</a>() <span class="keyword">As Boolean</span>

    <span class="comment">'The ProductID field is an automatically incrementing field</span>
    IDatabaseObjects_DistinctFieldAutoIncrements = <span class="keyword">True</span>

<span class="keyword">End Function

Private Function</span> <a href="IDatabaseObjects_DistinctFieldName.htm">IDatabaseObjects_DistinctFieldName</a>() <span class="keyword">As String</span>
    
<span class="comment">    'The ProductID field uniquely identifies each product record in the table</span>
    IDatabaseObjects_DistinctFieldName = &quot;ProductID&quot;

<span class="keyword">End Function

Private Function </span><a href="IDatabaseObjects_ItemInstance.htm">IDatabaseObjects_ItemInstance</a>() <span class="keyword">As</span> dbo.IDatabaseObject
    
    <span class="comment">'Return a new instance of the class that is associated with this collection</span>
    <span class="keyword">Set</span> IDatabaseObjects_ItemInstance = <span class="keyword">New</span> Product

<span class="keyword">End Function

Private Function </span><a href="IDatabaseObjects_KeyFieldName.htm">IDatabaseObjects_KeyFieldName</a>() <span class="keyword">As String</span>
<span class="comment">
    'The ProductName field is a unique field within the product table 
    'and is used by the DBO.ObjectByKey function</span>
    IDatabaseObjects_KeyFieldName = &quot;ProductName&quot;

<span class="keyword">End Function

Private Function</span> <a href="IDatabaseObjects_OrderBy.htm">IDatabaseObjects_OrderBy</a>() <span class="keyword">As</span> DBO.SQLSelectOrderByFields

<span class="comment">    'When enumerating through the collection (using ObjectByOrdinal or ObjectsCollection)
    'then the  Product objects should be ordered by ProductName</span>    
<span class="comment">    'If this function returns Nothing then the records are not sorted</span>
    <span class="keyword">Set</span> IDatabaseObjects_OrderBy = <span class="keyword">New</span> SQLSelectOrderByFields
    IDatabaseObjects_OrderBy.Add &quot;ProductName&quot;, dboOrderAscending

<span class="keyword">End Function

Private Function </span><a href="IDatabaseObjects_TableName.htm">IDatabaseObjects_TableName</a>() <span class="keyword">As String</span>
    
    <span class="comment">'Return the database table that this collection uses</span>
    IDatabaseObjects_TableName = &quot;Products&quot;

<span class="keyword">End Function</span>
<span class="keyword">
Private Function</span> <a href="IDatabaseObjects_Subset.htm">IDatabaseObjects_Subset</a>() <span class="keyword">As</span> dbo.SQLConditions
     
<span class="comment">    'Leave this function blank to include all of the product records</span>

<span class="keyword">End Function

Private Function</span> <a href="IDatabaseObjects_TableJoins.htm">IDatabaseObjects_TableJoins</a>(<span class="keyword">ByVal</span> objPrimaryTable <span class="keyword">As</span> SQLSelectTable, <span class="keyword">ByVal</span> objTables <span class="keyword">As</span> dbo.SQLSelectTables) <span class="keyword">As</span> dbo.SQLSelectTableJoins
     
<span class="comment">    'Leave this function blank to not join to any tables </span>

<span class="keyword">End Function</span></pre></td>
  </tr>
</table>

<ol>
  <li>Enable the<strong> For Each</strong> enumeration
    <ol>
      <li> Select Tools &gt; Procedure Attributes</li>
      <li> Select <strong>Enumerator</strong> in the Name list</li>
      <li>Click Advanced</li>
      <li>Enter -4 for the 'Procedure ID'</li>
      <li>This will allow the <strong>For Each</strong> command to be used on
      the <strong>Products</strong> collection</li>
    </ol>
  </li>
</ol>
<h3>NorthwindDatabase Class</h3>
<ol>
  <li>Add a new class to the <strong>NorthwindDB</strong> project, name it <strong>NorthWindDatabase</strong></li>
  <li>Set the instancing property for the <strong>NorthWindDatabase</strong> class
    to <strong>6-GlobalMultiUse</strong></li>
  <li>Add the following code to the <strong>NorthWindDatabase</strong> class</li>
  <li>This will connect to the nwind.mdb database. The nwind.mdb database is
    available <a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=EN" target="_blank">here</a> at
    the Microsoft website.</li>
</ol>
<table width="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#f6f6f6">
  <tr>
    <td><pre>

<span class="keyword">Public Sub</span> Connect()

    <span class="keyword">Const</span> cstrDatabaseFilePath <span class="keyword">As String</span> = &quot;C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb&quot;

    Dim strConnection As String

<span class="keyword">  </span>  strConnection = _<br>        &quot;Data Source=&quot; &amp; cstrDatabaseFilePath &amp; &quot;;&quot; &amp; _<br>        &quot;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;&quot;

    DBO.Connect strConnection, dboConnectionTypeMicrosoftAccess

<span class="keyword">End Sub</span>

<span class="keyword">Public Property Get </span>Products() <span class="keyword">As</span> Products

    <span class="keyword">Set </span>Products =<span class="keyword"> New </span>Products

<span class="keyword">End Property</span></pre>
    </td>
  </tr>
</table>
<h3>Exe Project</h3>
<ol><li>Select <strong>File &gt; Add Project</strong></li>
  <li>Select <strong>Standard EXE</strong></li>
  <li>Rename <strong>Project1</strong> to <strong>DBOTest</strong></li>
  <li>Right click on<strong> DBOTest </strong>and select <strong>Set As Startup</strong></li>
  <li>Select the <strong>DBOTest</strong> project</li>
  <li>Select <strong>Project &gt; References</strong></li>
  <li>Select the<strong> NorthwindDB</strong> reference</li>
  <li>Add a text box to Form1</li>
  <li>Set the <strong>MultiLine</strong> property to true</li>
  <li>Set the <strong>ScrollBars</strong> property to <strong>2-Vertical</strong></li>
  <li>Clear the <strong>Text</strong> property</li>
  <li>Paste the following code into Form1:</li>
</ol>
<table width="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#f6f6f6">
  <tr>
    <td><pre><span class="keyword">
Private Sub</span> Form_Load()<br>    <br>    <span class="keyword">Dim </span>objProduct <span class="keyword">As</span> Product<br>    <br>    NorthwindDB.Connect

    <span class="keyword">For</span> <span class="keyword">Each</span> objProduct <span class="keyword">In</span> NorthWindDB.Products<br>        Me.Text1.Text = Me.Text1.Text &amp; objProduct.Name &amp; &quot;  -  &quot; &amp; FormatCurrency(objProduct.UnitPrice) &amp; vbCrLf<br><span class="keyword">    Next<br>    <br>End Sub</span>
</pre>
    </td>
  </tr>
</table>
<p>For more
  information and examples see the <a href="reference.htm">reference guide</a> and
  demonstration program. Both
are available at the website <a href="http://www.hisystems.com.au/databaseobjects">www.hisystems.com.au/databaseobjects</a> in
the downloads section.</p>
<!-- InstanceEndEditable -->

<p>&nbsp;</p>
<p align="center"><font size="1">�  Hi Integrity Systems 2005. All rights reserved.</font></p>
</body>

<!-- InstanceEnd --></html>

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

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.

A list of licenses authors might use can be found here

Share

About the Author

Toby Wicks
United States United States
No Biography provided

You may also be interested in...

Pro
Pro
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.161128.1 | Last Updated 31 Jan 2007
Article Copyright 2005 by Toby Wicks
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid