Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » Databases » Downloads
 
Add your own
alternative version

Object-Oriented database design with the DatabaseObjects library

, 31 Jan 2007
Demonstrates creating object-oriented database systems with the DatabaseObjects library.
databaseobjects_demo_net.zip
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
databaseobjects_demo_vb6.zip
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_src_net.zip
DatabaseObjects.vbproj.user
DatabaseObjects.dll
databaseobjects_src_vb6.zip
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
Option Strict Off
Option Explicit On

Imports DatabaseObjects
Imports System.Collections

Public Class Products
    Inherits DatabaseObjects.DatabaseObjects
    Implements IEnumerable

    'Allow other DLL projects to load a product using it's ProductID with something similar to:
    'Dim objproduct As Product = DirectCast(NorthwindDB.Products, IGlobalDatabaseObjects).Object(productid)
    Implements IGlobalDatabaseObjects

    Public Enum SearchType
        NamePrefix
        Name
    End Enum

    Private pobjCategory As Category

    Friend Sub New(ByVal objDatabase As Database)

        MyBase.New(objDatabase)

    End Sub

    Friend WriteOnly Property Category() As Category
        Set(ByVal Value As Category)
            'Friend will allow public access within the NorthwindDB project but will be
            'private outside of the NorthwindDB project

            pobjCategory = Value

        End Set
    End Property

    Default Public ReadOnly Property Item(ByVal strProductCode As String) As Product
        Get

            Return MyBase.ObjectByKey(strProductCode)

        End Get
    End Property

    Default Public ReadOnly Property Item(ByVal intOrdinal As Integer) As Product
        Get

            Return MyBase.ObjectByOrdinal(intOrdinal)

        End Get
    End Property

    Public ReadOnly Property Count() As Integer
        Get

            Return MyBase.ObjectsCount

        End Get
    End Property

    Public Function Add(Optional ByVal strProductName As String = "") As Product

        Add = ItemInstance()
        Add.Name = strProductName

    End Function

    Public Function Search(ByVal strName As String, ByVal eType As SearchType) As IList

        Dim objConditions As SQL.SQLConditions = New SQL.SQLConditions

        Select Case eType
            Case SearchType.NamePrefix
                objConditions.Add("ProductName", SQL.ComparisonOperator.Like, strName & "%")
            Case SearchType.Name
                objConditions.Add("ProductName", SQL.ComparisonOperator.Like, "%" & strName & "%")
        End Select

        Return MyBase.ObjectsSearch(objConditions)

    End Function

    Public Sub Delete(ByRef objProduct As Product)

        MyBase.ObjectDelete(objProduct)

    End Sub

    Public Function Exists(ByVal strProductName As String) As Boolean

        Return MyBase.ObjectExists(strProductName)

    End Function

    Protected Overrides Function DistinctFieldAutoIncrements() As Boolean

        Return True

    End Function

    Protected Overrides Function DistinctFieldName() As String

        Return "ProductID"

    End Function

    Protected Overrides Function ItemInstance() As IDatabaseObject

        Return New Product

    End Function

    Protected Overrides Function KeyFieldName() As String

        Return "ProductName"

    End Function

    Protected Overrides Function OrderBy() As SQL.SQLSelectOrderByFields

        Dim objOrderByFields As SQL.SQLSelectOrderByFields = New SQL.SQLSelectOrderByFields

        objOrderByFields.Add("ProductName", SQL.OrderBy.Ascending)

        Return objOrderByFields

    End Function

    Protected Overrides Function Subset() As SQL.SQLConditions

        'if the category filter has been set then this collection should only
        'contain products within the specified category. The Item, Count, Delete and Exists
        'functions will all reflect this change
        If Not pobjCategory Is Nothing Then
            Dim objConditions As SQL.SQLConditions = New SQL.SQLConditions
            objConditions.Add("CategoryID", SQL.ComparisonOperator.EqualTo, pobjCategory.ID)
            Return objConditions
        End If

    End Function

    Protected Overrides Function TableName() As String

        Return "Products"

    End Function

    Protected Overrides Function TableJoins(ByVal objPrimaryTable As DatabaseObjects.SQL.SQLSelectTable, ByVal objTables As DatabaseObjects.SQL.SQLSelectTables) As DatabaseObjects.SQL.SQLSelectTableJoins

        'Implementing this function is optional, but is useful when attempting to optimise loading speeds.
        'This function is used by the ObjectsList, Object, ObjectByKey, ObjectOrdinal and ObjectSearch functions.
        'If this function has been implemented the Search can also search fields in the joined table(s).
        'In this example, Products table will always be joined with the Supplier table. We could also join the Products
        'table to the Category table, however the Product.Category property is not used often enough to warrant
        'always joining the category table whenever loading a product. Of course, you can always join different
        'tables in different situations, for example you might want join to other tables when searching and to
        'not join to other tables in normal circumstances.

        Dim objTableJoins As SQL.SQLSelectTableJoins = New SQL.SQLSelectTableJoins

        With objTableJoins.Add(objPrimaryTable, SQL.SQLSelectTableJoin.Type.Inner, objTables.Add("Suppliers"))
            .Where.Add("SupplierID", SQL.ComparisonOperator.EqualTo, "SupplierID")
        End With

        'With objTableJoins.Add(objPrimaryTable, SQL.SQLSelectTableJoin.Type.Inner, objTables.Add("Categories"))
        '    .Where.Add("CategoryID", SQL.ComparisonOperator.EqualTo, "CategoryID")
        'End With

        Return objTableJoins

    End Function

    Private Function IGlobalDatabaseObjects_Object(ByVal objDistinctValue As Object) As IDatabaseObject Implements IGlobalDatabaseObjects.Object

        Return MyBase.Object(objDistinctValue)

    End Function

    Private Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator

        Return MyBase.ObjectsList.GetEnumerator

    End Function

End Class


Public Class Product
    Inherits DatabaseObject

    'Below I have demonstrated 2 methods for implementing object relationships with the
    'Category and Supplier properties. The Supplier property is the simplest method, however
    'it can cause speed degradation because it must load the associated Supplier object everytime a Product is loaded.
    'The speed degradation would be most noticable on a Product search screen or something similar.
    'The second method demonstrated with the Category object will only load the object when it the property
    'is explicity called - i.e. it will only store the CategoryID and load the associated Category object
    'when the property is explicity called. This method results in faster loading speeds and still provides
    'the necessary object relationships. Because this is a fairly common situation I have created the
    'LateBoundObject class which handles this neatly, and can be used as an alternative to the method
    'demonstrated below using the plngCategoryID and pobjCategory variables. This method is demonstrated
    'this using the Category2 property.


    Private pstrName As String
    Private pcurUnitPrice As Decimal
    Private pintUnitsInStock As Short
    Private pintUnitsOnOrder As Short

    Private pintCategoryID As Integer
    Private pobjCategory As Category

    'For a new product instance default to using the first category
    'Private pobjCategory2 As LateBoundObject = New NorthwindLateBoundObject(NorthwindDB.Categories, 0, bUseDefaultObject:=True)

    'Alternatively, for a new product instance default the category to Nothing
    'Private pobjCategory2 As LateBoundObject = New LateBoundObject

    Private pobjSupplier As Supplier

    Private pstrQuantityPerUnit As String
    Private pbDiscontinued As Boolean

    Public Sub New()

        MyBase.New(NorthwindDB.Products)

    End Sub

    Friend ReadOnly Property ID() As Integer
        Get

            Return MyBase.DistinctValue

        End Get
    End Property

    Public ReadOnly Property QuantityPerUnit() As String
        Get

            Return pstrQuantityPerUnit

        End Get
    End Property

    Public ReadOnly Property Discontinued() As Boolean
        Get

            Return pbDiscontinued

        End Get
    End Property

    Public ReadOnly Property UnitsInStock() As Short
        Get

            Return pintUnitsInStock

        End Get
    End Property

    Public ReadOnly Property IsInStock() As Boolean
        Get

            Return pintUnitsInStock > 0

        End Get
    End Property

    Public ReadOnly Property IsOnOrder() As Boolean
        Get

            Return pintUnitsOnOrder > 0

        End Get
    End Property

    Public Property Name() As String
        Get

            Return pstrName

        End Get

        Set(ByVal Value As String)

            If Value.Trim = Nothing Then
                Throw New ArgumentNullException
            Else
                pstrName = Value
            End If

        End Set
    End Property

    Public Property UnitPrice() As Decimal
        Get

            Return pcurUnitPrice

        End Get

        Set(ByVal Value As Decimal)

            If Value >= 0 Then
                pcurUnitPrice = Value
            Else
                Throw New ArgumentException(Value)
            End If

        End Set
    End Property

    Public Property Category() As Category
        Get

            If pobjCategory Is Nothing Then
                If pintCategoryID > 0 Then
                    pobjCategory = NorthwindDB.Database.Object(NorthwindDB.Categories, pintCategoryID)
                Else
                    pobjCategory = NorthwindDB.Categories(1)
                    pintCategoryID = pobjCategory.ID
                End If
            End If

            Return pobjCategory

        End Get

        Set(ByVal Value As Category)

            If Value Is Nothing Then
                Throw New ArgumentNullException
            End If

            pobjCategory = Value

            'Alternatively the ObjectDistinctValue function can be used to return the
            'same ID value. In this instance I have create a Friend property in the Category class
            'to make the code more readable.
            pintCategoryID = pobjCategory.ID

        End Set
    End Property

    'Public Property Category2() As Category
    '    Get

    '        Return pobjCategory2.Object

    '    End Get

    '    Set(ByVal Value As Category)

    '        If Value Is Nothing Then
    '            Throw New ArgumentNullException
    '        End If

    '        pobjCategory2.Object = Value

    '    End Set
    'End Property

    Public ReadOnly Property Supplier() As Supplier
        Get

            Return pobjSupplier

        End Get
    End Property

    Public Sub AddUnits(ByVal intQuantity As Integer)

        If intQuantity >= 0 Then
            pintUnitsInStock = pintUnitsInStock + intQuantity
        Else
            Throw New ArgumentException(intQuantity)
        End If

    End Sub

    Public Sub RemoveUnits(ByVal intQuantity As Integer)

        If intQuantity >= 0 Then
            If intQuantity > pintUnitsInStock Then
                Throw New ArgumentException("Cannot remove more units than are available")
            End If
            pintUnitsInStock = pintUnitsInStock - intQuantity
        Else
            Throw New ArgumentException(intQuantity)
        End If

    End Sub

    Public Overloads Sub Save()

        MyBase.Save()

    End Sub

    Protected Overrides Sub LoadFields(ByVal objFields As SQL.SQLFieldValues)
        'This function should copy all of the relevant field values from the database
        'into the object.

        pstrName = objFields("ProductName").Value
        pcurUnitPrice = objFields("UnitPrice").Value
        pintUnitsOnOrder = objFields("UnitsOnOrder").Value
        pintUnitsInStock = objFields("UnitsInStock").Value
        pstrQuantityPerUnit = objFields("QuantityPerUnit").Value
        pbDiscontinued = objFields("Discontinued").Value
        pintCategoryID = objFields("CategoryID").Value
        'pobjCategory2 = New NorthwindLateBoundObject(NorthwindDB.Categories, objFields("CategoryID").Value)

        If objFields.Exists("CompanyName") Then
            'load the object from the objFields because it contains all of the associated supplier fields
            pobjSupplier = NorthwindDB.Database.ObjectFromFieldValues(NorthwindDB.Suppliers, objFields)
        Else
            'load the supplier with an SQL call
            pobjSupplier = NorthwindDB.Database.Object(NorthwindDB.Suppliers, objFields("SupplierID").Value)
        End If

    End Sub

    Protected Overrides Function SaveFields() As SQL.SQLFieldValues
        'This function should copy all of the relevant field values from the object
        'into the database.

        Dim objFields As SQL.SQLFieldValues
        objFields = New SQL.SQLFieldValues

        objFields.Add("ProductName", pstrName)
        objFields.Add("UnitPrice", pcurUnitPrice)
        objFields.Add("UnitsOnOrder", pintUnitsOnOrder)
        objFields.Add("UnitsInStock", pintUnitsInStock)
        objFields.Add("QuantityPerUnit", Me.QuantityPerUnit)
        objFields.Add("Discontinued", Me.Discontinued)
        objFields.Add("SupplierID", pobjSupplier.ID)
        'Alternatively, you can cast to a IDatabaseObject and access the distinct value
        'objFields.Add("SupplierID", DirectCast(pobjSupplier, IDatabaseObject).DistinctValue)
        objFields.Add("CategoryID", pintCategoryID)
        'objFields.Add("CategoryID", pobjCategory2.DistinctValue)

        Return objFields

    End Function

End Class


Public Class ProductSearch

    'This class provides the ability to search on multiple fields on multiple criteria
    'for a product, as opposed to the Product.Search function which only provides searching
    'on 1 field.

    'This class also demonstrates how to optimise the objects so that rather than loading
    'referenced objects (in this case the product's Supplier object) via multiple SQL calls
    'all of the associated tables are joined and returned in one recordset. Have a look in the
    'Product's Load to see how the objects are loaded from the same
    'recordset, and in particular the ObjectFromFieldValues function.

    Private Enum TriState
        [Default] = -2
        [True] = True
        [False] = False
    End Enum

    Private pstrName As String
    Private peInStock As TriState = TriState.Default
    Private peOnOrder As TriState = TriState.Default
    Private peDiscontinued As TriState = TriState.Default

    Public WriteOnly Property Name() As String
        Set(ByVal Value As String)

            pstrName = Value.Trim

        End Set
    End Property

    Public WriteOnly Property InStock() As Boolean
        Set(ByVal Value As Boolean)

            peInStock = Value

        End Set
    End Property

    Public WriteOnly Property OnOrder() As Boolean
        Set(ByVal Value As Boolean)

            peOnOrder = Value

        End Set
    End Property

    Public WriteOnly Property Discontinued() As Boolean
        Set(ByVal Value As Boolean)

            peDiscontinued = Value

        End Set
    End Property

    Public Function Search() As IList

        Dim objConnection As IDbConnection
        Dim objProducts As IDatabaseObjects
        Dim lngIndex As Integer
        Dim objProduct As Product
        Dim objResults As IDataReader
        Dim objResultsList As IList = New ArrayList
        Dim objSelect As SQL.SQLSelect = New SQL.SQLSelect

        'The TableJoins function will join the Products and Suppliers tables so that the
        'results are returned in one recordset, rather than requiring multiple statements to read
        'each product's associated supplier record. See the Products' TableJoins
        'on how this is done.

        objProducts = NorthwindDB.Products
        objSelect.Tables.Add(objProducts.TableName)
        objSelect.Tables.Joins = objProducts.TableJoins(objSelect.Tables(0), objSelect.Tables)

        'search for the product name if it has been set
        If pstrName <> Nothing Then
            objSelect.Where.Add("ProductName", SQL.ComparisonOperator.Like, "%" & pstrName & "%")
        End If

        If peInStock <> TriState.Default Then
            If peInStock = TriState.True Then
                'select products that are in stock
                objSelect.Where.Add("UnitsInStock", SQL.ComparisonOperator.GreaterThan, 0)
            Else
                'select products that are not in stock
                objSelect.Where.Add("UnitsInStock", SQL.ComparisonOperator.EqualTo, 0)
            End If
        End If

        If peOnOrder <> TriState.Default Then
            If peOnOrder = TriState.True Then
                'select products that are on order
                objSelect.Where.Add("UnitsOnOrder", SQL.ComparisonOperator.GreaterThan, 0)
            Else
                'select products that are not on order
                objSelect.Where.Add("UnitsOnOrder", SQL.ComparisonOperator.EqualTo, 0)
            End If
        End If

        If peDiscontinued <> TriState.Default Then
            objSelect.Where.Add("Discontinued", SQL.ComparisonOperator.EqualTo, CBool(peDiscontinued))
        End If

        objSelect.OrderBy.Add("ProductName", SQL.OrderBy.Ascending)

        System.Diagnostics.Debug.WriteLine(objSelect.SQL)
        objConnection = NorthwindDB.CreateConnection
        objConnection.Open()

        With objConnection.CreateCommand
            .CommandText = objSelect.SQL
            objResults = .ExecuteReader()
        End With

        While objResults.Read()
            objProduct = NorthwindDB.Database.ObjectFromDataReader(NorthwindDB.Products, objResults)
            objResultsList.Add(objProduct)
        End While

        objResults.Close()
        objConnection.Close()

        Return objResultsList

    End Function

End Class

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 31 Jan 2007
Article Copyright 2005 by Toby Wicks
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid