' ___________________________________________________
'
' � Hi-Integrity Systems 2007. All rights reserved.
' www.hisystems.com.au - Toby Wicks
' ___________________________________________________
'
Option Strict On
Option Explicit On
Imports DatabaseObjects
Imports System.Data
Imports System.Diagnostics
''' --------------------------------------------------------------------------------
''' <summary>
''' Represents a database connection and provides a set of functions that work
''' in conjunction with classes implementing IDatabaseObjects and IDatabaseObject.
''' The Database class automatically generates and executes the required SQL
''' statements to perform common database operations such as saving, deleting
''' searching etc. based on the values returned via the IDatabaseObjects and
''' IDatabaseObject interfaces.
''' Typically, this class is only used when explicitly implementing the IDatabaseObjects
''' and IDatabaseObject interfaces rather than inheriting from DatabaseObjects (or
''' DatabaseObjectsEnumerable) and DatabaseObject.
''' </summary>
''' --------------------------------------------------------------------------------
'''
Public Class Database
Public Enum ConnectionType
SQLServer
MicrosoftAccess
MySQL
End Enum
Private Enum ConnectionDriver
OleDB
ODBC
End Enum
Private pobjByOrdinalLastCollection As IDatabaseObjects
Private pobjConnection As ConnectionController
Private pobjTransactions As TransactionsClass
''' --------------------------------------------------------------------------------
''' <summary>
''' Creates a new Database instance specifying the database to connect to and SQL
''' syntax to use with the database. Each function call of the Database class opens
''' and closes a connection. Therefore, connection pooling should be enabled
''' for optimal performance.
''' </summary>
'''
''' <param name="strConnectionString">
''' A database connection string to either a Microsoft Access, SQLServer, or MySQL
''' database. For example, 'Provider=Microsoft.Jet.OLEDB.4.0;Data
''' Source=northwind.mdb;Jet OLEDB:Database Password=;'.
''' </param>
'''
''' <param name="eConnectionType">
''' Indicates the SQL syntax to generate for the database specified in strConnectionString.
''' </param>
''' --------------------------------------------------------------------------------
'''
Public Sub New( _
ByVal strConnectionString As String, _
ByVal eConnectionType As ConnectionType)
pobjConnection = New ConnectionController(strConnectionString, eConnectionType)
pobjTransactions = New TransactionsClass(pobjConnection)
End Sub
''' --------------------------------------------------------------------------------
''' <summary>
''' Returns an instance of an object from the collection using a distinct value (see
''' IDatabaseObjects.DistinctFieldName). If the collection has implemented the
''' IDatabaseObjects.Subset function then the objDistinctValue need only be unique
''' within the collection's subset, not the entire database table.
''' </summary>
'''
''' <param name="objCollection">
''' The collection that contains the object.
''' </param>
'''
''' <param name="objDistinctValue">
''' The value that uniquely identifies the object within the collection. This is the value
''' of the field defined by the collection's IDatabaseObjects.DistinctFieldName function.
''' </param>
'''
''' <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
'''
''' <example> Loads a product using a product ID of 123
''' <code>
''' objProduct = objDatabase.Object(NorthwindDB.Products, 123)
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
'''
Public Function [Object]( _
ByVal objCollection As IDatabaseObjects, _
ByVal objDistinctValue As Object) As IDatabaseObject
Dim objSelect As SQL.SQLSelect = New SQL.SQLSelect
Dim objReader As IDataReader
Dim objSubset As SQL.SQLConditions
With objSelect
.Tables.Add(objCollection.TableName)
.Tables.Joins = objCollection.TableJoins(.Tables(0), .Tables)
.Where.Add(objCollection.DistinctFieldName, SQL.ComparisonOperator.EqualTo, objDistinctValue)
objSubset = objCollection.Subset
If Not objSubset Is Nothing AndAlso Not objSubset.IsEmpty Then
.Where.Add(objSubset)
End If
End With
pobjConnection.Start()
objReader = pobjConnection.Execute(objSelect)
If objReader.Read() Then
[Object] = ObjectFromDataReader(objCollection, objReader)
Else
Throw New ObjectDoesNotExistException(objCollection, objDistinctValue)
End If
objReader.Close()
pobjConnection.Finished()
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' Extracts the fields to save to the database from the objItem.SaveFields function.
''' The fields are then written to the database using either an SQL INSERT or UPDATE
''' depending on whether the object has already been saved. If the collection has
''' implemented IDatabaseObjects.KeyFieldName then objItem's key is also validated to
''' ensure it is not null and unique within the collection. If objCollection has
''' implemented IDatabaseObjects.Subset then objItem should exist within objCollection.
''' If not, a duplicate key error may occur if the obItem's key is being used in
''' another subset in the same table. If a record is being amended
''' (IDatabaseObject.IsSaved returns true) then the function will "AND" the collection's
''' IDatabaseObjects.Subset conditions and the objItem's IDatabaseObject.DistinctValue
''' value to create the WHERE clause in the UPDATE statement. Therefore, the
''' combination of the IDatabaseObjects.Subset and IDatabaseObject.DistinctValue
''' conditions MUST identify only one record in the table. Otherwise multiple records
''' will be updated with the same data. If data is only inserted and not amended
''' (usually a rare occurance) then this requirement is unnecessary.
''' </summary>
'''
''' <param name="objCollection">
''' The collection which contains or will contain the object to save.
''' </param>
'''
''' <param name="objItem">
''' The object to save to the database. The values saved to the database are extracted from the
''' SQLFieldValues object returned from IDatabaseObject.SaveFields.
''' </param>
'''
''' <example> Saves a product object (Me) to the database.
''' <code>
''' Public Sub Save()
'''
''' objDatabase.ObjectSave(NorthwindDB.Products, Me)
'''
''' End Sub
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
'''
Public Sub ObjectSave( _
ByVal objCollection As IDatabaseObjects, _
ByVal objItem As IDatabaseObject)
Dim objFieldValues As SQL.SQLFieldValues
Dim objUpdate As SQL.SQLUpdate
Dim objIdentityField As SQL.SQLAutoIncrementValue
Dim objInsert As SQL.SQLInsert
Dim objReader As IDataReader
Dim objSubset As SQL.SQLConditions
'Clear the ordinal collection if an object is being saved
pobjByOrdinalLastCollection = Nothing
objFieldValues = objItem.SaveFields
If objFieldValues Is Nothing Then
Throw New DatabaseObjectsException(Misc.TypeName(objItem) & " IDatabaseObject.SaveFields not implemented")
End If
'Add the distinct field value if it hasn't been added via the SaveFields sub
If Not objFieldValues.Exists(objCollection.DistinctFieldName) Then
If Not objCollection.DistinctFieldAutoIncrements Then
objFieldValues.Add(objCollection.DistinctFieldName, objItem.DistinctValue)
End If
End If
ItemKeyEnsureValid(objCollection, objItem, objFieldValues)
pobjConnection.Start()
If objItem.IsSaved Then
objUpdate = New SQL.SQLUpdate
objUpdate.TableName = objCollection.TableName
objUpdate.Fields = objFieldValues
objUpdate.Where.Add(objCollection.DistinctFieldName, SQL.ComparisonOperator.EqualTo, objItem.DistinctValue)
objSubset = objCollection.Subset
If Not objSubset Is Nothing AndAlso Not objSubset.IsEmpty Then
objUpdate.Where.Add(objSubset)
End If
pobjConnection.ExecuteNonQuery(objUpdate)
Else
objInsert = New SQL.SQLInsert
objInsert.TableName = objCollection.TableName
objInsert.Fields = objFieldValues
pobjConnection.ExecuteNonQuery(objInsert)
'get the auto increment value for the newly inserted record
If objCollection.DistinctFieldAutoIncrements Then
objIdentityField = New SQL.SQLAutoIncrementValue
objReader = pobjConnection.Execute(objIdentityField)
objReader.Read()
objItem.DistinctValue = objReader.Item(0)
objReader.Close()
End If
objItem.IsSaved = True
End If
pobjConnection.Finished()
End Sub
Private Sub ItemKeyEnsureValid( _
ByVal objCollection As IDatabaseObjects, _
ByVal objItem As IDatabaseObject, _
ByVal objFieldValues As SQL.SQLFieldValues)
Dim objReader As IDataReader
Dim objSelect As SQL.SQLSelect
Dim objKeyFieldValue As Object
Dim objSubset As SQL.SQLConditions
If objCollection.KeyFieldName <> Nothing Then
objKeyFieldValue = ItemKeyFieldValue(objCollection, objItem, objFieldValues)
EnsureKeyDataTypeValid(objKeyFieldValue)
If TypeOf objKeyFieldValue Is String Then
If DirectCast(objKeyFieldValue, String).Trim = Nothing Then
Throw New DatabaseObjectsException(Misc.TypeName(objItem) & " " & objCollection.KeyFieldName & " field is Null")
End If
End If
objSelect = New SQL.SQLSelect
With objSelect
.Tables.Add(objCollection.TableName)
.Fields.Add(objCollection.KeyFieldName)
.Where.Add(objCollection.KeyFieldName, SQL.ComparisonOperator.EqualTo, objKeyFieldValue)
objSubset = objCollection.Subset
If Not objSubset Is Nothing AndAlso Not objSubset.IsEmpty Then
.Where.Add(objSubset)
End If
If objItem.IsSaved Then
.Where.Add(objCollection.DistinctFieldName, SQL.ComparisonOperator.NotEqualTo, objItem.DistinctValue)
End If
End With
pobjConnection.Start()
objReader = pobjConnection.Execute(objSelect)
If objReader.Read Then
Throw New ObjectAlreadyExistsException(objItem, objKeyFieldValue.ToString)
End If
objReader.Close()
pobjConnection.Finished()
End If
End Sub
''' --------------------------------------------------------------------------------
''' <summary>
''' Returns an object from the collection using a unique key value.
''' The key must be unique within the collection. If the collection's
''' IDatabaseObjects.Subset has been implemented then the key need only be unique
''' within the subset specified, not the entire database table.
''' </summary>
'''
''' <param name="objCollection">
''' The collection which contains the object.
''' </param>
'''
''' <param name="objKey">
''' The key that identifies the object with the collection. The key is the value of
''' the field defined by the collection's IDatabaseObjects.KeyFieldName.
''' </param>
'''
''' <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
'''
''' <example>
''' <code>
''' Default Public ReadOnly Property Item(ByVal strProductCode As String) As Product
''' Get
'''
''' Return objDatabase.ObjectByKey(Me, strProductCode)
'''
''' End Get
''' End Property
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectByKey( _
ByVal objCollection As IDatabaseObjects, _
ByVal objKey As Object) As IDatabaseObject
Dim objReader As IDataReader
Dim objSelect As SQL.SQLSelect = New SQL.SQLSelect
Dim objSubset As SQL.SQLConditions
EnsureKeyDataTypeValid(objKey)
With objSelect
.Tables.Add(objCollection.TableName)
.Tables.Joins = objCollection.TableJoins(.Tables(0), .Tables)
.Where.Add(objCollection.KeyFieldName, SQL.ComparisonOperator.EqualTo, objKey)
objSubset = objCollection.Subset
If Not objSubset Is Nothing AndAlso Not objSubset.IsEmpty Then
.Where.Add(objSubset)
End If
End With
pobjConnection.Start()
objReader = pobjConnection.Execute(objSelect)
If objReader.Read Then
ObjectByKey = ObjectFromDataReader(objCollection, objReader)
Else
Throw New ObjectDoesNotExistException(objCollection, objKey)
End If
objReader.Close()
pobjConnection.Finished()
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' ObjectByOrdinalFirst returns the first object in the collection respectively
''' filtered and sorted by the collection's IDatabaseObjects.Subset and
''' IDatabaseObjects.OrderBy values. It differs from ObjectByOrdinal in that it only
''' loads the first record from the database table not the entire table.
''' </summary>
'''
''' <param name="objCollection">
''' The collection which contains the object.
''' </param>
'''
''' <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
'''
''' <example>
''' <code>
''' 'Ideal for loading default objects
''' Dim objDefaultSupplier As Supplier = objDatabase.ObjectByOrdinalFirst(objGlobalSuppliersInstance)
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectByOrdinalFirst( _
ByVal objCollection As IDatabaseObjects) As IDatabaseObject
Dim objReader As IDataReader
Dim objSelect As SQL.SQLSelect = New SQL.SQLSelect
With objSelect
'only select the first row of the recordset
.Top = 1
.Tables.Add(objCollection.TableName)
.Tables.Joins = objCollection.TableJoins(.Tables(0), .Tables)
.Where = objCollection.Subset
.OrderBy = objCollection.OrderBy
End With
pobjConnection.Start()
objReader = pobjConnection.Execute(objSelect)
If objReader.Read() Then
ObjectByOrdinalFirst = ObjectFromDataReader(objCollection, objReader)
Else
Throw New ObjectDoesNotExistException(objCollection, "TOP 1")
End If
objReader.Close()
pobjConnection.Finished()
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' ObjectByOrdinal returns an object from the collection at a specific ordinal
''' position. The ordinal position is related to the collection's
''' IDatabaseObjects.Subset and IDatabaseObjects.OrderBy values.
''' Typically, this function is used to enumerate through a collection of objects.
''' ObjectByOrdinal will load the collection's entire recordset on the first call
''' and on subsequent calls the preloaded objects are returned.
''' Avoid making alternate calls to ObjectByOrdinal for different tables, as the
''' entire recordset will be reloaded on each call.
''' To load the first object in a table without loading the entire table, use the
''' ObjectByOrdinalFirst function.
''' </summary>
'''
''' <param name="objCollection">
''' The collection that contains the object at the specified ordinal position.
''' </param>
'''
''' <param name="intIndex">
''' The ordinal position of the object. The ordinal position depends on the collection's
''' IDatabaseObjects.Subset and IDatabaseObjects.OrderBy values
''' </param>
'''
''' <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
'''
''' <example>
''' <code>
''' Default Public ReadOnly Property Item(ByVal intIndex As Integer) As Product
''' Get
'''
''' Return objDatabase.ObjectByOrdinal(Me, intIndex)
'''
''' End Get
''' End Property
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectByOrdinal( _
ByVal objCollection As IDatabaseObjects, _
ByVal intIndex As Integer) As IDatabaseObject
Static objObjects As IList
If Not pobjByOrdinalLastCollection Is objCollection Then
objObjects = ObjectsList(objCollection)
pobjByOrdinalLastCollection = objCollection
End If
Return DirectCast(objObjects(intIndex), IDatabaseObject)
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' Returns the number of items in the collection. If the collection's
''' IDatabaseObjects.Subset has been implemented then this function returns the
''' number of records within the subset, not the entire table.
''' </summary>
'''
''' <param name="objCollection">
''' The object for which the number of records are returned.
''' </param>
'''
''' <returns><see cref="Int32" /> (System.Int32)</returns>
'''
''' <example>
''' <code>
''' 'Return the number of items in this collection.
''' Public ReadOnly Property Count() As Integer
''' Get
'''
''' Return objDatabase.ObjectsCount(Me)
'''
''' End Get
''' End Property
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectsCount( _
ByVal objCollection As IDatabaseObjects) As Integer
Dim objReader As IDataReader
Dim objSelect As SQL.SQLSelect = New SQL.SQLSelect
Dim objSubset As SQL.SQLConditions
With objSelect
.Tables.Add(objCollection.TableName)
.Where = objCollection.Subset
.Fields.Add("", SQL.AggregateFunction.Count)
End With
pobjConnection.Start()
objReader = pobjConnection.Execute(objSelect)
objReader.Read()
ObjectsCount = CType(objReader.Item(0), Integer)
objReader.Close()
pobjConnection.Finished()
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' Returns whether the key exists within the collection. If the collection's
''' IDatabaseObjects.Subset has been set then only the subset is searched not the
''' entire table.
''' </summary>
'''
''' <param name="objCollection">
''' The collection to search within.
''' </param>
'''
''' <param name="objKey">
''' The key value to search by.
''' </param>
'''
''' <returns><see cref="Boolean" /> (System.Boolean)</returns>
'''
''' <example>
''' <code>
''' Public Function Exists(ByVal strProductCode As String) As Boolean
'''
''' Return objDatabase.ObjectExists(Me, strProductCode)
'''
''' End Function
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectExists( _
ByVal objCollection As IDatabaseObjects, _
ByVal objKey As Object) As Boolean
Dim objReader As IDataReader
Dim objSelect As SQL.SQLSelect = New SQL.SQLSelect
Dim objSubset As SQL.SQLConditions
EnsureKeyDataTypeValid(objKey)
With objSelect
.Tables.Add(objCollection.TableName)
'.Fields.Add objCollection.DistinctFieldName
.Where.Add(objCollection.KeyFieldName, SQL.ComparisonOperator.EqualTo, objKey)
objSubset = objCollection.Subset
If Not objSubset Is Nothing AndAlso Not objSubset.IsEmpty Then
.Where.Add(objSubset)
End If
End With
pobjConnection.Start()
objReader = pobjConnection.Execute(objSelect)
ObjectExists = objReader.Read
objReader.Close()
pobjConnection.Finished()
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' Deletes an object's database record. If the collection's IDatabaseObjects.Subset
''' has been implemented then the object must exist within the subset, otherwise the
''' object will not be deleted. If the object has not been saved to the database the
''' function will exit without executing an SQL DELETE command. After deleting the
''' database record the object is set to Nothing. The calling function should receive
''' the object ByRef for this to have any affect. Setting the object to Nothing
''' minimises the possibility of the deleted object being used in code after
''' ObjectDelete has been called.
''' </summary>
'''
''' <param name="objCollection">
''' The collection that contains the object to delete. If the item does not exist
''' within the collection then the object will not be deleted.
''' </param>
'''
''' <param name="objItem">
''' The object to delete. The calling function should receive this object ByRef
''' as the object is set to Nothing after deletion.
''' Reference Type: <see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)
''' </param>
'''
''' <example>
''' <code>
''' Public Sub Delete(ByRef objProduct As Product)
'''
''' objDatabase.ObjectDelete(Me, objProduct)
''' 'objProduct will now be Nothing
'''
''' End Sub
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
'''
Public Sub ObjectDelete( _
ByVal objCollection As IDatabaseObjects, _
ByRef objItem As IDatabaseObject)
If objItem.IsSaved Then
Dim objDelete As SQL.SQLDelete = New SQL.SQLDelete
Dim objSubset As SQL.SQLConditions
'Clear the ordinal collection if an object is being deleted
pobjByOrdinalLastCollection = Nothing
With objDelete
.TableName = objCollection.TableName
.Where.Add(objCollection.DistinctFieldName, SQL.ComparisonOperator.EqualTo, objItem.DistinctValue)
objSubset = objCollection.Subset
If Not objSubset Is Nothing AndAlso Not objSubset.IsEmpty Then
.Where.Add(objSubset)
End If
End With
pobjConnection.Start()
pobjConnection.ExecuteNonQuery(objDelete)
pobjConnection.Finished()
objItem.IsSaved = False
End If
'The function that calls ObjectDelete objItem MUST be ByRef for this to have any effect
objItem = Nothing
End Sub
''' --------------------------------------------------------------------------------
''' <summary>
''' Deletes all of the objects in the collection. If IDatabaseObjects.Subset
''' has been implemented then only the objects within the subset are deleted, not
''' the table's entire contents.
''' </summary>
'''
''' <param name="objCollection">
''' The collection from which all objects are to be deleted.
''' </param>
''' --------------------------------------------------------------------------------
'''
Public Sub ObjectsDeleteAll( _
ByVal objCollection As IDatabaseObjects)
Dim objDelete As SQL.SQLDelete = New SQL.SQLDelete
'Clear the ordinal collection if an object is being deleted
pobjByOrdinalLastCollection = Nothing
With objDelete
.TableName = objCollection.TableName
.Where = objCollection.Subset
End With
pobjConnection.Start()
pobjConnection.ExecuteNonQuery(objDelete)
pobjConnection.Finished()
End Sub
''' --------------------------------------------------------------------------------
''' <summary>
''' Returns an IList object containing all of the collection's associated child
''' objects. This function is useful when loading a set of objects for a subset or
''' for use with the IEnumerable interface.
''' </summary>
'''
''' <param name="objCollection">
''' The collection which contains the objects to load.
''' </param>
'''
''' <returns><see cref="Collections.IList" /> (System.Collections.IList)</returns>
'''
''' <example>
''' <code>
''' 'Can be used to provide an enumerator for use with the "For Each" clause
''' Private Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
'''
''' Return objDatabase.ObjectsList(objGlobalProductsInstance).GetEnumerator
'''
''' End Function
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectsList( _
ByVal objCollection As IDatabaseObjects) As IList
Dim objArrayList As IList = New ArrayList
Dim objSelect As SQL.SQLSelect = New SQL.SQLSelect
Dim objReader As IDataReader
With objSelect
.Tables.Add(objCollection.TableName)
.Tables.Joins = objCollection.TableJoins(.Tables(0), .Tables)
.Where = objCollection.Subset
.OrderBy = objCollection.OrderBy
End With
pobjConnection.Start()
objReader = pobjConnection.Execute(objSelect)
While objReader.Read
objArrayList.Add(ObjectFromDataReader(objCollection, objReader))
End While
objReader.Close()
pobjConnection.Finished()
Return objArrayList
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' Returns an IDictionary object. Each key/value pair contains a key and
''' the object associated with the key.
''' </summary>
'''
''' <param name="objCollection">
''' The collection which specifies the objects to load.
''' </param>
'''
''' <returns><see cref="Collections.IDictionary" /> (System.Collections.IDictionary)</returns>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectsDictionary( _
ByVal objCollection As IDatabaseObjects) As IDictionary
Return ObjectsDictionaryBase(objCollection)
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' Returns an IDictionary object. Each key/value pair contains a distinct
''' value and the object associated with the distinct value.
''' </summary>
'''
''' <param name="objCollection">
''' The collection which specifies the objects to load.
''' </param>
'''
''' <returns><see cref="Collections.IDictionary" /> (System.Collections.IDictionary)</returns>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectsDictionaryByDistinctValue( _
ByVal objCollection As IDatabaseObjects) As IDictionary
Return ObjectsDictionaryBase(objCollection, bKeyIsDistinctField:=True)
End Function
Private Function ObjectsDictionaryBase( _
ByVal objCollection As IDatabaseObjects, _
Optional ByVal bKeyIsDistinctField As Boolean = False) As IDictionary
'Returns an IDictionary with the key being either the DistinctField or KeyField
Dim objDictionary As IDictionary = New Hashtable
Dim objSelect As SQL.SQLSelect = New SQL.SQLSelect
Dim objReader As IDataReader
Dim strKeyField As String
With objSelect
.Tables.Add(objCollection.TableName)
.Tables.Joins = objCollection.TableJoins(.Tables(0), .Tables)
.Where = objCollection.Subset
.OrderBy = objCollection.OrderBy
End With
pobjConnection.Start()
objReader = pobjConnection.Execute(objSelect)
If bKeyIsDistinctField Then
strKeyField = objCollection.DistinctFieldName
Else
strKeyField = objCollection.KeyFieldName
End If
While objReader.Read
objDictionary.Add(objReader(strKeyField), ObjectFromDataReader(objCollection, objReader))
End While
objReader.Close()
pobjConnection.Finished()
Return objDictionary
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' Returns a collection of objects that match the specified search criteria.
''' This function utilises any subsets, ordering or table joins specified in the
''' collection. To add a set of conditions to the objSearchCriteria object with
''' higher precendance use the "Add(SQLConditions)" overloaded function as this will
''' wrap the conditions within parentheses.
''' </summary>
'''
''' <param name="objCollection">
''' The collection to search within.
''' </param>
'''
''' <param name="objSearchCriteria">
''' The criteria to search for within the collection. To add a set of conditions with
''' with higher precendance use the "Add(SQLConditions)" overloaded function as this
''' will wrap the conditions within parentheses.
''' </param>
'''
''' <returns><see cref="Collections.IList" /> (System.Collections.IList)</returns>
'''
''' <remarks>
''' The following wildcard characters are used when using the LIKE operator (extract
''' from Microsoft Transact-SQL Reference)
'''
'''
''' <font size="1">
''' <table width="659" border="1" cellspacing="1" cellpadding="4">
''' <tr>
''' <th width="16%" height="20">Wildcard character</th>
''' <th width="22%">Description</th>
''' <th width="62%">Example</th>
''' </tr>
''' <tr>
''' <td>%</td>
''' <td>Any string of zero or more characters.</td>
''' <td>WHERE title LIKE '%computer%' finds all book titles with the word
''' 'computer' anywhere in the book title.</td>
''' </tr>
''' <tr>
''' <td>_ (underscore)</td>
''' <td>Any single character.</td>
''' <td>WHERE au_fname LIKE '_ean' finds all four-letter first names that end
''' with ean (Dean, Sean, and so on).</td>
''' </tr>
''' </table>
''' </font>
''' </remarks>
'''
''' <example>
''' <code>
''' Public Function Search(ByVal objSearchCriteria As Object, ByVal eType As SearchType) As IList
'''
''' Dim objConditions As SQL.SQLConditions = New SQL.SQLConditions
'''
''' Select Case eType
''' Case SearchType.DescriptionPrefix
''' objConditions.Add("ProductName", SQL.ComparisonOperator.Like, objSearchCriteria & "%")
''' Case SearchType.Description
''' objConditions.Add("ProductName", SQL.ComparisonOperator.Like, "%" & objSearchCriteria & "%")
''' End Select
'''
''' Return objDatabase.ObjectsSearch(objGlobalProductsInstance, objConditions)
'''
''' End Function
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectsSearch( _
ByVal objCollection As IDatabaseObjects, _
ByVal objSearchCriteria As SQL.SQLConditions) As IList
Dim objReader As IDataReader
Dim objSelect As SQL.SQLSelect = New SQL.SQLSelect
Dim objResults As ArrayList = New ArrayList
If objSearchCriteria.IsEmpty Then
Throw New ArgumentException("Search criteria is empty")
End If
With objSelect
.Tables.Add(objCollection.TableName)
.Tables.Joins = objCollection.TableJoins(.Tables(0), .Tables)
.OrderBy = objCollection.OrderBy
.Where = objCollection.Subset
If Not objSearchCriteria Is Nothing Then
If .Where Is Nothing Then .Where = New SQL.SQLConditions
.Where.Add(objSearchCriteria)
End If
End With
pobjConnection.Start()
objReader = pobjConnection.Execute(objSelect)
While objReader.Read
objResults.Add(ObjectFromDataReader(objCollection, objReader))
End While
objReader.Close()
pobjConnection.Finished()
Return objResults
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' Loads an object from the current record of an IDataReader object.
''' </summary>
'''
''' <param name="objCollection">
''' The collection associated with the IDataReader object.
''' </param>
'''
''' <param name="objReader">
''' The data to be copied into a new IDatabaseObject object.
''' </param>
'''
''' <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectFromDataReader( _
ByVal objCollection As IDatabaseObjects, _
ByVal objReader As IDataReader) As IDatabaseObject
Dim strFieldName As String
Dim strTablePrefix As String
Dim objFieldValues As SQL.SQLFieldValues
'check that the distinct field name exists
If Not FieldExists(objReader, objCollection.DistinctFieldName) Then
Throw New DatabaseObjectsException("DistinctFieldName '" & objCollection.DistinctFieldName & "' is invalid.")
End If
objFieldValues = New SQL.SQLFieldValues
strTablePrefix = objCollection.TableName & "."
'Copy the recordset values into the SQL.SQLFieldValues object
For intIndex As Integer = 0 To objReader.FieldCount - 1
'If the recordset has been loaded with a join then it may be prefixed with
'the table name - this is the case with Microsoft Access
'If so remove the table name if the table prefix is the same as objCollection.TableName
'All of the other joined fields with tablename prefixes on the fields will remain. This is ok considering
'most of the time an inner join has been performed where the field names are equal in the 2 joined tables
strFieldName = objReader.GetName(intIndex)
If strFieldName.IndexOf(strTablePrefix) = 0 Then
objFieldValues.Add(strFieldName.Substring(strTablePrefix.Length), objReader(intIndex))
Else
objFieldValues.Add(strFieldName, objReader(intIndex))
End If
Next
Return ObjectFromFieldValues(objCollection, objFieldValues)
End Function
''' --------------------------------------------------------------------------------
''' <summary>
''' Loads an object from the values contained in an SQLFieldValues object.
''' This function is generally used from within an IDatabaseObject.Load function when
''' the IDatabaseObjects.TableJoins function has been implemented.
''' </summary>
'''
''' <param name="objCollection">
''' The collection associated with the field values.
''' </param>
'''
''' <param name="objFieldValues">
''' The data container from which to load a new object.
''' </param>
'''
''' <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
''' --------------------------------------------------------------------------------
'''
Public Function ObjectFromFieldValues( _
ByVal objCollection As IDatabaseObjects, _
ByVal objFieldValues As SQL.SQLFieldValues) As IDatabaseObject
Dim objItem As IDatabaseObject = objCollection.ItemInstance
objItem.IsSaved = True
objItem.DistinctValue = objFieldValues(objCollection.DistinctFieldName).Value
objItem.LoadFields(objFieldValues)
Return objItem
End Function
Private Function FieldExists( _
ByVal objReader As IDataReader, _
ByVal strFieldName As String) As Boolean
Dim bExists As Boolean
Dim strReaderFieldName As String
For intIndex As Integer = 0 To objReader.FieldCount - 1
strReaderFieldName = objReader.GetName(intIndex)
If strReaderFieldName.IndexOf("."c) >= 0 Then
strReaderFieldName = strReaderFieldName.Split("."c)(1)
End If
If String.Compare(strReaderFieldName, strFieldName, ignoreCase:=True) = 0 Then
bExists = True
Exit For
End If
Next
Return bExists
End Function
Private Sub EnsureKeyDataTypeValid(ByVal objKey As Object)
'If TypeOf objKey Is Object Then
' Throw New ArgumentInvalidDataTypeException(objKey)
'End If
End Sub
Private Function ItemKeyFieldValue( _
ByVal objCollection As IDatabaseObjects, _
ByVal objItem As IDatabaseObject, _
ByVal objFieldValues As SQL.SQLFieldValues) As Object
'On the rare occurance that the KeyField is the same as the DistinctField
'then the key value may not have been set in the Save and therefore be
'available in the objFieldValues collection. In which case the
'key has to be extracted from the objItem.DistinctField.
Dim objKeyFieldValue As Object
If String.Compare(objCollection.DistinctFieldName, objCollection.KeyFieldName, ignoreCase:=True) = 0 Then
objKeyFieldValue = objItem.DistinctValue
Else
objKeyFieldValue = objFieldValues(objCollection.KeyFieldName).Value
End If
Return objKeyFieldValue
End Function
Public ReadOnly Property Transactions() As TransactionsClass
Get
Return pobjTransactions
End Get
End Property
Friend ReadOnly Property Connection() As ConnectionController
Get
Return pobjConnection
End Get
End Property
''' --------------------------------------------------------------------------------
''' <summary>
''' Provides a mechanism for starting beginning, commiting and rolling back transactions.
''' </summary>
''' --------------------------------------------------------------------------------
Public Class TransactionsClass
Private pobjConnection As IDbConnection
Private pobjConnectionController As ConnectionController
Friend Sub New(ByVal objConnection As ConnectionController)
pobjConnectionController = objConnection
End Sub
''' --------------------------------------------------------------------------------
''' <summary>
''' Notifies that a transaction has begun and that all modifications to the database
''' are only committed after a call to Commit. Alternatively, if
''' Rollback is called then all changes are aborted. To execute other
''' statements for the transaction call the Execute and ExecuteNonQuery functions.
''' Because all changes to the database must be executed on the same connection
''' DatabaseObjects maintains an open connection until the Commit or Rollback functions
''' are called. When transactions are not being used connections are opened and closed
''' for each SQL statement executed i.e. (INSERT/UPDATE/SELECT...).
''' </summary>
'''
''' <example>
''' <code>
''' Public Sub Shadows Save()
'''
''' Mybase.ParentDatabase.Transactions.Begin()
'''
''' MyBase.Save
''' Me.Details.Save
'''
''' 'Execute any other statements here via
''' 'MyBase.ParentDatabase.Transactions.Execute()...
'''
''' Mybase.ParentDatabase.Transactions.Commit()
'''
''' End sub
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
Public Sub Begin()
pobjConnection = pobjConnectionController.BeginTransaction()
End Sub
''' --------------------------------------------------------------------------------
''' <summary>
''' Commits all statements that have been executed after the Begin() call.
''' The database connection is closed after the transaction has been committed.
''' </summary>
'''
''' <example>
''' <code>
''' Public Sub Shadows Save()
'''
''' Mybase.ParentDatabase.Transactions.Begin()
'''
''' MyBase.Save
''' Me.Details.Save
'''
''' 'Execute any other statements here via
''' 'MyBase.ParentDatabase.Transactions.Execute()...
'''
''' Mybase.ParentDatabase.Transactions.Commit()
'''
''' End sub
''' </code>
''' </example>
''' --------------------------------------------------------------------------------
Public Sub Commit()
pobjConnectionController.CommitTransaction()
End Sub
''' --------------------------------------------------------------------------------
''' <summary>
''' Rollsback all statements that have been executed after the Begin() call.
''' The database connection is closed after the transaction has been rolled back.
''' </summary>
''' --------------------------------------------------------------------------------
'''
Public Sub Rollback()
pobjConnectionController.RollbackTransaction()
End Sub
''' --------------------------------------------------------------------------------
''' <summary>
''' Allows an SQL statement to be executed on the current transaction connection.
''' If a transaction is not in progress and exception will occur.
''' </summary>
''' --------------------------------------------------------------------------------
Public Sub ExecuteNonQuery(ByVal objStatement As SQL.ISQLStatement)
pobjConnectionController.ExecuteNonQuery(objStatement)
End Sub
''' --------------------------------------------------------------------------------
''' <summary>
''' Allows an SQL statement to be executed on the current transaction connection.
''' If a transaction is not in progress and exception will occur.
''' </summary>
''' --------------------------------------------------------------------------------
Public Function Execute(ByVal objStatement As SQL.ISQLStatement) As IDataReader
Return pobjConnectionController.Execute(objStatement)
End Function
End Class
Friend Class ConnectionController
Private pobjConnection As IDbConnection
Private pbInTransactionMode As Boolean = False
Private pstrConnectionString As String
Private peConnectionType As ConnectionType
Private peConnectionDriver As ConnectionDriver
Friend Sub New( _
ByVal strConnectionString As String, _
ByVal eConnectionType As ConnectionType)
pstrConnectionString = strConnectionString
peConnectionType = eConnectionType
SQL.SQLStatement.DefaultConnectionType = eConnectionType
peConnectionDriver = ExtractConnectionDriver(strConnectionString)
End Sub
Friend Sub Start()
'If not in transaction mode then open a new connection
'If in transaction mode a connection should already be open
If Not Me.InTransactionMode Then
pobjConnection = CreateConnection()
pobjConnection.Open()
End If
End Sub
Friend Sub Finished()
'If not in transaction mode then close the connection
'If in transaction mode a connection should be left open
If Not Me.InTransactionMode Then
pobjConnection.Close()
End If
End Sub
Friend Function BeginTransaction() As IDbConnection
pbInTransactionMode = True
pobjConnection = CreateConnection()
pobjConnection.Open()
ExecuteNonQuery(New SQL.SQLBeginTransaction)
Return pobjConnection
End Function
Friend Sub CommitTransaction()
ExecuteNonQuery(New SQL.SQLCommitTransaction)
pobjConnection.Close()
pbInTransactionMode = False
End Sub
Friend Sub RollbackTransaction()
ExecuteNonQuery(New SQL.SQLRollbackTransaction)
pobjConnection.Close()
pbInTransactionMode = False
End Sub
Private ReadOnly Property InTransactionMode() As Boolean
Get
Return pbInTransactionMode
End Get
End Property
Friend Function Execute( _
ByVal objSQLStatement As SQL.ISQLStatement) As IDataReader
Return ExecuteInternal(pobjConnection, objSQLStatement)
End Function
Friend Sub ExecuteNonQuery( _
ByVal objSQLStatement As SQL.ISQLStatement)
ExecuteNonQueryInternal(pobjConnection, objSQLStatement)
End Sub
Private Function ExecuteInternal( _
ByVal objConnection As IDbConnection, _
ByVal objSQLStatement As SQL.ISQLStatement) As IDataReader
objSQLStatement.ConnectionType = peConnectionType
Dim strSQL As String = objSQLStatement.SQL
DebugSQL(strSQL)
With objConnection.CreateCommand
.CommandText = strSQL
Try
Return .ExecuteReader()
Catch ex As Exception
Throw New DatabaseObjectsException("Execute failed: " & strSQL)
End Try
End With
End Function
Private Sub ExecuteNonQueryInternal( _
ByVal objConnection As IDbConnection, _
ByVal objSQLStatement As SQL.ISQLStatement)
objSQLStatement.ConnectionType = peConnectionType
Dim strSQL As String = objSQLStatement.SQL
DebugSQL(strSQL)
With objConnection.CreateCommand
.CommandText = strSQL
Try
.ExecuteNonQuery()
Catch ex As Exception
Throw New DatabaseObjectsException("ExecuteNonQuery failed: " & strSQL)
End Try
End With
End Sub
Private Function CreateConnection() As IDbConnection
Select Case peConnectionDriver
Case ConnectionDriver.OleDB
Return New OleDb.OleDbConnection(pstrConnectionString)
Case ConnectionDriver.ODBC
Return New Odbc.OdbcConnection(pstrConnectionString)
End Select
End Function
Private Function ExtractConnectionDriver( _
ByVal strConnectionString As String) As ConnectionDriver
'This function searches for an occurance of 'Provider='
'If found then it is assumed to be an OLEDB connection otherwise an ODBC connection
Dim strProperties() As String = strConnectionString.Split(";"c)
Dim strPropertyValueArray() As String
Dim eConnectionDriver As ConnectionDriver = ConnectionDriver.ODBC
For Each strPropertyValue As String In strProperties
strPropertyValueArray = strPropertyValue.Split("="c)
If String.Compare(strPropertyValueArray(0).Trim, "Provider", ignoreCase:=True) = 0 Then
eConnectionDriver = ConnectionDriver.OleDB
Exit For
End If
Next
Return eConnectionDriver
End Function
Private Sub DebugSQL(ByVal strSQL As String)
#If DebugSQL Then
System.Diagnostics.Debug.WriteLine(strSQL)
#End If
End Sub
End Class
End Class
Submit an article or tip