Object-Oriented database design with the DatabaseObjects library

Option Strict Off
Option Explicit On

Imports DatabaseObjects
Imports System.Collections

Public Class Suppliers
    Implements IDatabaseObjects
    Implements IEnumerable

    Default Public ReadOnly Property Item(ByVal strName As String) As Supplier
        Get

            Return NorthwindDB.Database.ObjectByKey(Me, strName)

        End Get
    End Property

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

            Return NorthwindDB.Database.ObjectByOrdinal(Me, intOrdinal)

        End Get
    End Property

    Public ReadOnly Property Count() As Integer
        Get

            Return NorthwindDB.Database.ObjectsCount(Me)

        End Get
    End Property

    Public Function Exists(ByVal strName As String) As Boolean

        Return NorthwindDB.Database.ObjectExists(Me, strName)

    End Function

    Public Sub Delete(ByRef objSupplier As Supplier)

        If objSupplier.IsDeletable Then
            NorthwindDB.Database.ObjectDelete(Me, objSupplier)
        Else
            Throw New ApplicationException("Supplier Undeletable: " & objSupplier.Name)
        End If

    End Sub

    Private Function DistinctFieldAutoIncrements() As Boolean Implements IDatabaseObjects.DistinctFieldAutoIncrements

        Return True

    End Function

    Private Function DistinctFieldName() As String Implements IDatabaseObjects.DistinctFieldName

        Return "SupplierID"

    End Function

    Private Function ItemInstance() As IDatabaseObject Implements IDatabaseObjects.ItemInstance

        Return New Supplier

    End Function

    Private Function KeyFieldName() As String Implements IDatabaseObjects.KeyFieldName

        Return "CompanyName"

    End Function

    Private Function OrderBy() As SQL.SQLSelectOrderByFields Implements IDatabaseObjects.OrderBy

        Dim objOrderByFields As SQL.SQLSelectOrderByFields = New SQL.SQLSelectOrderByFields

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

        Return objOrderByFields

    End Function

    Private Function Subset() As SQL.SQLConditions Implements IDatabaseObjects.Subset

        'Dim objConditions As SQL.SQLConditions = New SQL.SQLConditions

        'objConditions.Add("CompanyName", SQL.ComparisonOperator.Like, "M%")
        'objConditions.AddLogicalOperator(SQLSelect.LogicalOperator.Or)
        'objConditions.Add("CompanyName", SQL.ComparisonOperator.Like, "N%")

        'Return objConditions

    End Function

    Private Function TableName() As String Implements IDatabaseObjects.TableName

        Return "Suppliers"

    End Function

    Private Function TableJoins(ByVal objPrimaryTable As DatabaseObjects.SQL.SQLSelectTable, ByVal objTables As DatabaseObjects.SQL.SQLSelectTables) As DatabaseObjects.SQL.SQLSelectTableJoins Implements DatabaseObjects.IDatabaseObjects.TableJoins

    End Function

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

        Return NorthwindDB.Database.ObjectsList(Me).GetEnumerator

    End Function

End Class



Public Class Supplier
    Implements IDatabaseObject

    Private pintID As Integer
    Private pstrName As String

    Friend ReadOnly Property ID() As Integer
        Get

            'Friend will allow public access within the NorthwindDB project but will be
            'private outside of the NorthwindDB project
            Return pintID

        End Get
    End Property

    Public Property Name() As String
        Get

            Return pstrName

        End Get

        Set(ByVal Value As String)

            pstrName = Value.Trim

        End Set
    End Property

    Public ReadOnly Property IsDeletable() As Boolean
        Get

            'This checks the Products table to see if there are any references to this Supplier.
            'If there are then this supplier cannot be deleted.

            Dim objReader As IDataReader
            Dim objConnection As IDbConnection
            Dim objSelect As SQL.SQLSelect = New SQL.SQLSelect

            With objSelect
                .Fields.Add("", SQL.AggregateFunction.Count)
                .Tables.Add("Products")
                .Where.Add("SupplierID", SQL.ComparisonOperator.EqualTo, pintID)

                objConnection = NorthwindDB.CreateConnection
                objConnection.Open()

                With objConnection.CreateCommand
                    .CommandText = objSelect.SQL
                    objReader = .ExecuteReader
                End With

                objReader.Read()
                IsDeletable = (objReader(0) = 0)
                objReader.Close()
                objConnection.Close()
            End With

        End Get
    End Property

    Private Property DistinctValue() As Object Implements IDatabaseObject.DistinctValue
        Get

            Return pintID

        End Get

        Set(ByVal Value As Object)

            pintID = Value

        End Set
    End Property

    Private Property IsSaved() As Boolean Implements IDatabaseObject.IsSaved
        Get
            'This function can be implemented using a boolean variable to hold
            'whether this object has been saved or not. However, if the object is
            'using an Identity field the following shortcut can be used as it is
            'logically equivalent. The Property Let IsSaved does
            'not need to be implemented

            Return pintID <> 0

        End Get

        Set(ByVal Value As Boolean)

        End Set
    End Property

    Public Sub Save()

        NorthwindDB.Database.ObjectSave(NorthwindDB.Suppliers, Me)

    End Sub

    Private Sub LoadFields(ByVal objFields As SQL.SQLFieldValues) Implements IDatabaseObject.LoadFields

        pstrName = objFields("CompanyName").Value

    End Sub

    Private Function SaveFields() As SQL.SQLFieldValues Implements IDatabaseObject.SaveFields

        Dim objFields As SQL.SQLFieldValues = New SQL.SQLFieldValues

        objFields.Add("CompanyName", pstrName)

        Return objFields

    End Function

End Class