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
Submit an article or tip