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
' ___________________________________________________
'
'  � Hi-Integrity Systems 2007. All rights reserved.
'  www.hisystems.com.au - Toby Wicks
' ___________________________________________________
'

Option Strict On
Option Explicit On

Namespace SQL

    Module Misc

        Public Function SQLConvertIdentifierName( _
            ByVal strIdentifierName As String, _
            ByVal eConnectionType As Database.ConnectionType) As String

            'This function places tags around a field name or table name to ensure it doesn't
            'conflict with a reserved word or if it contains spaces it is not misinterpreted 

            Select Case eConnectionType
                Case Database.ConnectionType.MicrosoftAccess, Database.ConnectionType.SQLServer
                    Return "[" & strIdentifierName.Trim & "]"
                Case Database.ConnectionType.MySQL
                    Return "`" & strIdentifierName.Trim & "`"
            End Select

        End Function

        Public Function SQLConvertValue( _
            ByVal objValue As Object, _
            ByVal eConnectionType As Database.ConnectionType) As String

            If SQLValueIsNull(objValue) Then

                Return "NULL"

            Else
                If TypeOf objValue Is Boolean Then

                    Dim bBoolean As Boolean = DirectCast(objValue, Boolean)

                    If bBoolean Then
                        Return "1"
                    Else
                        Return "0"
                    End If

                ElseIf TypeOf objValue Is Char Then

                    Dim chChar As Char = DirectCast(objValue, Char)

                    If chChar.Equals("'") Then
                        Return "''''"
                    Else
                        Return "'" & chChar & "'"
                    End If

                ElseIf TypeOf objValue Is DateTime Then

                    Dim dtDate As Date = DirectCast(objValue, DateTime)
                    Dim strDate As String

                    'If the date hasn't been set then set to the 1899-12-30 so that
                    'the date isn't set to 2001-01-01
                    If dtDate.Day = 1 And dtDate.Month = 1 And dtDate.Year = 1 Then
                        strDate = "1899-12-30"
                    Else
                        strDate = dtDate.Year & "-" & dtDate.Month & "-" & dtDate.Day
                    End If

                    If dtDate.Hour <> 0 Or dtDate.Minute <> 0 Or dtDate.Second <> 0 Then
                        strDate = strDate & " " & dtDate.Hour & ":" & dtDate.Minute & ":" & dtDate.Second
                    End If

                    Select Case eConnectionType
                        Case Database.ConnectionType.MicrosoftAccess
                            Return "#" & strDate & "#"
                        Case Database.ConnectionType.MySQL, Database.ConnectionType.SQLServer
                            Return "'" & strDate & "'"
                    End Select

                ElseIf TypeOf objValue Is String Then

                    Return "'" & DirectCast(objValue, String).Replace("'", "''") & "'"

                Else

                    Return CType(objValue, String)

                End If

            End If

        End Function

        Public Function SQLValueIsNull(ByVal objValue As Object) As Boolean

            If objValue Is Nothing Then
                Return True
            ElseIf objValue Is DBNull.Value Then
                Return True
            Else
                Return False
            End If

        End Function

        Public Function SQLConvertAggregate(ByVal eAggregate As SQL.AggregateFunction) As String

            Dim strAggregate As String

            Select Case eAggregate
                Case SQL.AggregateFunction.Average
                    strAggregate = "AVG"
                Case AggregateFunction.Count
                    strAggregate = "COUNT"
                Case AggregateFunction.Maximum
                    strAggregate = "MAX"
                Case AggregateFunction.Minimum
                    strAggregate = "MIN"
                Case AggregateFunction.StandardDeviation
                    strAggregate = "STDEV"
                Case AggregateFunction.Sum
                    strAggregate = "SUM"
                Case AggregateFunction.Variance
                    strAggregate = "VAR"
            End Select

            Return strAggregate

        End Function

        Public Function SQLConvertCompare(ByVal eCompare As SQL.ComparisonOperator) As String

            Dim strCompare As String

            Select Case eCompare
                Case ComparisonOperator.EqualTo
                    strCompare = "="
                Case ComparisonOperator.GreaterThan
                    strCompare = ">"
                Case ComparisonOperator.GreaterThanOrEqualTo
                    strCompare = ">="
                Case ComparisonOperator.LessThan
                    strCompare = "<"
                Case ComparisonOperator.LessThanOrEqualTo
                    strCompare = "<="
                Case ComparisonOperator.Like
                    strCompare = "LIKE"
                Case ComparisonOperator.NotLike
                    strCompare = "NOT LIKE"
                Case ComparisonOperator.NotEqualTo
                    strCompare = "<>"
                Case Else
                    Throw New ArgumentException
            End Select

            Return strCompare

        End Function

        Public Function SQLConvertLogicalOperator(ByVal eLogicalOperator As SQL.LogicalOperator) As String

            Dim strLogicalOperator As String

            Select Case eLogicalOperator
                Case LogicalOperator.And
                    strLogicalOperator = "AND"
                Case LogicalOperator.Or
                    strLogicalOperator = "OR"
            End Select

            Return strLogicalOperator

        End Function

        Public Function SQLFieldNameAndTablePrefix( _
            ByVal objTable As SQLSelectTable, _
            ByVal strFieldName As String, _
            ByVal eConnectionType As Database.ConnectionType) As String

            Dim strTablePrefix As String

            If Not objTable Is Nothing Then
                strTablePrefix = objTable.Name
                strTablePrefix = SQLConvertIdentifierName(strTablePrefix, eConnectionType) & "."
            End If

            Return strTablePrefix & SQLConvertIdentifierName(strFieldName, eConnectionType)

        End Function

        Public Function SQLConditionValue(ByVal objValue As Object) As Object

            If TypeOf objValue Is SQLFieldValue Then
                Dim objSQLFieldValue As SQLFieldValue = DirectCast(objValue, SQLFieldValue)
                Return objSQLFieldValue.Value
            Else
                Return objValue
            End If

        End Function

        Public Sub CompareValuePairAssertValid( _
            ByVal eCompare As SQL.ComparisonOperator, _
            ByVal objValue As Object)

            If Not TypeOf objValue Is String And (eCompare = ComparisonOperator.Like Or eCompare = ComparisonOperator.NotLike) Then
                Throw New DatabaseObjectsException("The LIKE operator cannot be used in conjunction with a non-string data type")
            ElseIf TypeOf objValue Is Boolean And Not (eCompare = ComparisonOperator.EqualTo Or eCompare = ComparisonOperator.NotEqualTo) Then
                Throw New DatabaseObjectsException("A boolean value can only be used in conjunction with the " & ComparisonOperator.EqualTo.ToString & " or " & ComparisonOperator.NotEqualTo.ToString & " operators")
            End If

        End Sub

        Public Sub SQLConvertBooleanValue( _
            ByRef objValue As Object, _
            ByRef eCompare As SQL.ComparisonOperator)

            'If a boolean variable set to true then use the NOT
            'operator and compare it to 0. ie. if the condition is 'field = true' then
            'SQL code should be 'field <> 0'
            '-1 is true in MSAccess and 1 is true in SQLServer.

            If TypeOf objValue Is Boolean Then
                If DirectCast(objValue, Boolean) = True Then
                    If eCompare = ComparisonOperator.EqualTo Then
                        eCompare = ComparisonOperator.NotEqualTo
                    Else
                        eCompare = ComparisonOperator.EqualTo
                    End If
                    objValue = False
                End If
            End If

        End Sub

    End Module

End Namespace

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
Web01 | 2.8.141015.1 | Last Updated 31 Jan 2007
Article Copyright 2005 by Toby Wicks
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid