| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Code - Category Common Class

Page history last edited by Dr. Ron Eaglin 15 years, 2 months ago
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.