Imports Microsoft.VisualBasic
Imports System.Data
Public Class CategoryCommon
Inherits DatabaseCommon
' *** This is a Class wrapper for the table Categories ***
' *** This class should inherit from Database Table ***
#Region "Declarations"
Dim isModified As Boolean
Dim myCategoryID As Long
Dim myCategoryNameText As String
Dim myGroupID As Long
Dim myDescriptionText As String
Dim myParentCategoryID As Long
Dim myParentCodeID As Long
Dim myDeletedBit As Boolean
Dim myLastUpdUserID As Long
Dim myLastUpdDate As Date
Dim myEffectiveDate As Date
Dim myExpirationDate As Date
Dim myCategoryYear As String
#End Region
#Region "Properties"
Public Property CategoryID() As Long
Get
Return myCategoryID
End Get
Set(ByVal value As Long)
myCategoryID = value
isModified = True
End Set
End Property
Public Property CategoryNameText() As String
Get
Return myCategoryNameText
End Get
Set(ByVal value As String)
myCategoryNameText = value
isModified = True
End Set
End Property
Public Property CategoryGroupID() As Long
Get
Return myGroupID
End Get
Set(ByVal value As Long)
myGroupID = value
isModified = True
End Set
End Property
Public Property DescriptionText() As String
Get
Return myDescriptionText
End Get
Set(ByVal value As String)
myDescriptionText = value
isModified = True
End Set
End Property
Public Property ParentCategoryID() As Long
Get
Return myParentCategoryID
End Get
Set(ByVal value As Long)
myParentCategoryID = value
isModified = True
End Set
End Property
Public Property ParentCodeID() As Long
Get
Return myParentCodeID
End Get
Set(ByVal value As Long)
myParentCodeID = value
isModified = True
End Set
End Property
Public Property DeletedBit() As Boolean
Get
Return myDeletedBit
End Get
Set(ByVal value As Boolean)
myDeletedBit = value
isModified = True
End Set
End Property
Public Property LastUpdUserID() As Long
Get
Return myLastUpdUserID
End Get
Set(ByVal value As Long)
myLastUpdUserID = value
isModified = True
End Set
End Property
Public Property LastUpdDate() As Date
Get
Return myLastUpdDate
End Get
Set(ByVal value As Date)
myLastUpdDate = value
isModified = True
End Set
End Property
Public Property EffectiveDate() As Date
Get
Return myEffectiveDate
End Get
Set(ByVal value As Date)
myEffectiveDate = value
isModified = True
End Set
End Property
Public Property ExpirationDate() As Date
Get
Return myExpirationDate
End Get
Set(ByVal value As Date)
myExpirationDate = value
isModified = True
End Set
End Property
Public Property CategoryYear() As String
Get
Return myCategoryYear
End Get
Set(ByVal value As String)
myCategoryYear = value
isModified = True
End Set
End Property
#End Region
Public Function InsertCategoryPropertyList() As ArrayList
Dim ht As New ArrayList
ht.Add("@CategoryNameText")
ht.Add("@GroupID")
ht.Add("@DescriptionText")
ht.Add("@ParentCategoryID")
ht.Add("@ParentCodeID")
ht.Add("@DeletedBit")
ht.Add("@LastUpdUserID")
ht.Add("@LastUpdDate")
ht.Add("@EffectiveDate")
ht.Add("@ExpirationDate")
ht.Add("@CategoryYear")
Return ht
End Function
Public Function UpdateCategoryPropertyList() As ArrayList
Dim ht As New ArrayList
ht.Add("@CategoryID")
ht.Add("@CategoryNameText")
ht.Add("@GroupID")
ht.Add("@DescriptionText")
ht.Add("@ParentCategoryID")
ht.Add("@ParentCodeID")
ht.Add("@DeletedBit")
ht.Add("@LastUpdUserID")
ht.Add("@LastUpdDate")
ht.Add("@EffectiveDate")
ht.Add("@ExpirationDate")
ht.Add("@CategoryYear")
Return ht
End Function
Public Function CategorySave() As Boolean
' *** saves to the database, returns true on success ***
If Not CategoryValidate() Then Exit Function
isModified = False
Dim ht As New Hashtable
ht.Add("@CategoryNameText", CategoryNameText)
ht.Add("@GroupID", CategoryGroupID)
ht.Add("@DescriptionText", DescriptionText)
ht.Add("@ParentCategoryID", ParentCategoryID)
ht.Add("@ParentCodeID", ParentCodeID)
ht.Add("@DeletedBit", DeletedBit)
ht.Add("@LastUpdUserID", LastUpdUserID)
If IsDateOK(LastUpdDate) Then ht.Add("@LastUpdDate", LastUpdDate) Else ht.Add("@LastUpdDate", Date.Today)
If IsDateOK(EffectiveDate) Then ht.Add("@EffectiveDate", EffectiveDate) Else ht.Add("@EffectiveDate", Date.Today)
If IsDateOK(ExpirationDate) Then ht.Add("@ExpirationDate", ExpirationDate) Else ht.Add("@ExpirationDate", Nothing)
ht.Add("@CategoryYear", CategoryYear)
Dim ds As New DataSet
ds = ExecuteProcedure("spInsertCategories", ht, InsertCategoryPropertyList())
If Not ds Is Nothing Then
CategoryID = ds.Tables(0).Rows(0).Item("Category")
End If
End Function
Public Function CategoryValidate() As Boolean
Dim isValid As Boolean = True
' This returns a boolean, whether validation was successful
' if [business rule] then
' isValid = false
' addValidationMessage("Message Here") ' end if
Return isValid
End Function
Public Function CategoryRetrieveAll(ByVal OrderBy As String) As DataSet
' *** retrieves from the database ***
' *** returns true on success ***
Dim ht As New Hashtable
ht.Add("@OrderBy", OrderBy)
Return ExecuteProcedure("spGetAllCategories", ht)
End Function
Public Function CategoryRetrieve() As Boolean
' *** retrieves from the database ***
' *** returns true on success ***
' *** set the dr to the row to import from the table ***
If CategoryID = Nothing Then Return False
Dim ht As New Hashtable
ht.Add("@CategoryID", CategoryID)
Dim ds As New DataSet
ds = ExecuteProcedure("spGetCategories", ht)
Return CategoryFillFromDataSet(ds)
End Function
Public Function CategoryRetrieveByField(ByVal fieldName As String, ByVal FieldValue As String, Optional ByVal ReturnDataSet As Boolean = True) As DataSet
Dim sql As String
sql = "SELECT * FROM Categories WHERE " + fieldName + " = '" + FieldValue + "'ORDER BY CASE WHEN PATINDEX('%[^0-9]%',DescriptionText)=0 THEN " & _
"'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz' ELSE DescriptionText END, CASE WHEN PATINDEX('%[^0-9]%', DescriptionText)>0 THEN -1 ELSE " & _
"CAST(DescriptionText AS int) END"
Dim ds As New DataSet
ds = ExecuteSQL(sql)
CategoryFillFromDataSet(ds)
If ReturnDataSet Then Return ds Else Return Nothing
End Function
Public Function RetrieveAllCodesForCategory() As DataSet
Dim sql As String = ""
sql = "select * from codes where categoryID = " + CategoryID
Return ExecuteSQL(sql)
End Function
Public Function CategoryRetrieveByField(ByVal fieldNameA As String, ByVal FieldValueA As String, ByVal fieldNameB As String, ByVal FieldValueB As String, Optional ByVal ReturnDataSet As Boolean = True) As DataSet
Dim sql As String
sql = "SELECT * FROM Categories WHERE " + fieldNameA + " = '" + FieldValueA + "' AND " + fieldNameB + " = '" + FieldValueB + "'"
Dim ds As New DataSet
ds = ExecuteSQL(sql)
CategoryFillFromDataSet(ds)
If ReturnDataSet Then Return ds Else Return Nothing
End Function
Private Sub CategoryFillFromDataRow(ByVal dr As DataRow)
On Error Resume Next ' If data fields is null just moves to next field
CategoryID = dr.Item("CategoryID")
CategoryNameText = dr.Item("CategoryNameText")
CategoryGroupID = dr.Item("GroupID")
DescriptionText = dr.Item("DescriptionText")
ParentCategoryID = dr.Item("ParentCategoryID")
ParentCodeID = dr.Item("ParentCodeID")
DeletedBit = dr.Item("DeletedBit")
LastUpdUserID = dr.Item("LastUpdUserID")
LastUpdDate = dr.Item("LastUpdDate")
EffectiveDate = dr.Item("EffectiveDate")
ExpirationDate = dr.Item("ExpirationDate")
CategoryYear = dr.Item("CategoryYear")
End Sub
Public Function CategoryUpdate() As Boolean
' *** retrievs from the database ***
' *** returns true on success ***
If Not isModified Then Return True ' No need to update
If Not CategoryValidate() Then Return False
Dim ht As New Hashtable
ht.Add("@CategoryID", CategoryID)
ht.Add("@CategoryNameText", CategoryNameText)
ht.Add("@GroupID", CategoryGroupID)
ht.Add("@DescriptionText", DescriptionText)
ht.Add("@ParentCategoryID", ParentCategoryID)
ht.Add("@ParentCodeID", ParentCodeID)
ht.Add("@DeletedBit", DeletedBit)
ht.Add("@LastUpdUserID", LastUpdUserID)
If IsDateOK(LastUpdDate) Then ht.Add("@LastUpdDate", LastUpdDate) Else ht.Add("@LastUpdDate", Date.Today)
If IsDateOK(EffectiveDate) Then ht.Add("@EffectiveDate", EffectiveDate) Else ht.Add("@EffectiveDate", Date.Today)
If IsDateOK(ExpirationDate) Then ht.Add("@ExpirationDate", ExpirationDate) Else ht.Add("@ExpirationDate", Nothing)
ht.Add("@CategoryYear", CategoryYear)
Dim ds As New DataSet
ds = ExecuteProcedure("spUpdateCategories", ht, UpdateCategoryPropertyList())
If ds Is Nothing Then
' Code for empty dataset here
End If
End Function
Private Function CategoryFillFromDataSet(ByVal ds As DataSet) As Boolean
If ds Is Nothing Then Return False
Dim dr As DataRow
If ds Is Nothing Then Return False
If ds.Tables.Count = 0 Then Return False
If ds.Tables(0).Rows.Count = 0 Then Return False
dr = ds.Tables(0).Rows(0)
CategoryFillFromDataRow(dr)
Return True
End Function
End Class
Comments (0)
You don't have permission to comment on this page.