Imports System.Data
Imports System.Text
Imports System.Windows.Forms
Imports System.Xml

Public Class clsWork
    Implements ICloneable

    Private m_guidWorkID As Guid
    Private m_objComposer As clsComposer
    Private m_objBook As clsBook
    Private m_intPageNumber As Integer
    Private m_strWorkName As String
    Private m_strOpusInfo As String
    Private m_objInstrumentation As clsInstrumentation
    Private m_objHymnTuneName As clsHymnTuneName
    Private m_blnObjectAlreadyPersisted As Boolean

    Public Sub New()
        m_guidWorkID = Guid.NewGuid
        m_objComposer = Nothing
        m_objBook = Nothing
        m_intPageNumber = 0
        m_strWorkName = ""
        m_strOpusInfo = ""
        m_objInstrumentation = Nothing
        m_objHymnTuneName = Nothing
        m_blnObjectAlreadyPersisted = False
    End Sub

    Public Sub New(ByVal objComposer As clsComposer, ByVal objInstrumentation As clsInstrumentation, ByVal objBook As clsBook, ByVal intPageNumber As Integer, ByVal strWorkName As String, ByVal strOpusInfo As String, ByVal objHymnTuneName As clsHymnTuneName)
        m_guidWorkID = Guid.NewGuid
        m_objComposer = objComposer
        m_objInstrumentation = objInstrumentation
        m_objBook = objBook
        m_intPageNumber = intPageNumber
        m_strWorkName = strWorkName
        m_strOpusInfo = strOpusInfo
        m_objHymnTuneName = objHymnTuneName
        m_blnObjectAlreadyPersisted = False
    End Sub

    Public Sub New(ByVal guidWorkID As Guid)
        'm_guidWorkID = guidWorkID

        Dim ldbcmdCommand As New SqlClient.SqlCommand
        ldbcmdCommand.CommandText = "sel_work"

        ldbcmdCommand.CommandType = CommandType.StoredProcedure
        ' Set up parameter for stored procedure 
        Dim prmWorkID As New SqlClient.SqlParameter
        prmWorkID.ParameterName = "@work_id"
        prmWorkID.SqlDbType = SqlDbType.UniqueIdentifier
        'prmWorkID.Size = 5
        prmWorkID.Value = guidWorkID
        ldbcmdCommand.Parameters.Add(prmWorkID)

        Dim ldbdsWorks As DataSet = MyGlobals.g_objDatabaseLayer.ExecuteDataSet(ldbcmdCommand)

        If ldbdsWorks.Tables(0).Rows.Count > 0 Then
            Dim ldbrwRow As DataRow
            ldbrwRow = ldbdsWorks.Tables(0).Rows(0)

            PopulateFromDBRow(ldbrwRow)

            m_blnObjectAlreadyPersisted = True
            MyGlobals.AddCachedObject(Me, Me.WorkID.ToString)
        Else
            m_guidWorkID = Guid.NewGuid
        End If
        ldbdsWorks.Clear()
    End Sub

    Public Sub New(ByVal ldbrwRow As DataRow)
        PopulateFromDBRow(ldbrwRow)

        m_blnObjectAlreadyPersisted = True
        MyGlobals.AddCachedObject(Me, Me.WorkID.ToString)
    End Sub

    Private Sub PopulateFromDBRow(ByVal ldbrwRow As DataRow)
        m_guidWorkID = CType(ldbrwRow.Item("work_id"), Guid)

        If ldbrwRow.IsNull("composer_id") Then
            m_objComposer = Nothing
        Else
            Dim guidComposerID As Guid = CType(ldbrwRow.Item("composer_id"), Guid)
            If MyGlobals.CachedObjectExists(guidComposerID.ToString) Then
                m_objComposer = CType(MyGlobals.CachedObjectRetrieve(guidComposerID.ToString), clsComposer)
            Else
                m_objComposer = New clsComposer(guidComposerID)
            End If
        End If

        If ldbrwRow.IsNull("book_id") Then
            m_objBook = Nothing
        Else
            Dim guidBookID As Guid = CType(ldbrwRow.Item("book_id"), Guid)
            If MyGlobals.CachedObjectExists(guidBookID.ToString) Then
                m_objBook = CType(MyGlobals.CachedObjectRetrieve(guidBookID.ToString), clsBook)
            Else
                m_objBook = New clsBook(guidBookID)
            End If
        End If

        m_intPageNumber = CType(ldbrwRow.Item("page_number"), Integer)
        m_strWorkName = CType(ldbrwRow.Item("work_name"), String)
        m_strOpusInfo = CType(ldbrwRow.Item("opus_info"), String)

        If ldbrwRow.IsNull("instrumentation_id") Then
            m_objInstrumentation = Nothing
        Else
            Dim guidInstrumentationID As Guid = CType(ldbrwRow.Item("instrumentation_id"), Guid)
            If MyGlobals.CachedObjectExists(guidInstrumentationID.ToString) Then
                m_objInstrumentation = CType(MyGlobals.CachedObjectRetrieve(guidInstrumentationID.ToString), clsInstrumentation)
            Else
                m_objInstrumentation = New clsInstrumentation(guidInstrumentationID)
            End If
        End If

        If ldbrwRow.IsNull("hymn_tune_name_id") Then
            m_objHymnTuneName = Nothing
        Else
            Dim guidHymnTuneNameID As Guid = CType(ldbrwRow.Item("hymn_tune_name_id"), Guid)
            If MyGlobals.CachedObjectExists(guidHymnTuneNameID.ToString) Then
                m_objHymnTuneName = CType(MyGlobals.CachedObjectRetrieve(guidHymnTuneNameID.ToString), clsHymnTuneName)
            Else
                m_objHymnTuneName = New clsHymnTuneName(guidHymnTuneNameID)
            End If
        End If
    End Sub

    Public Function Delete() As Boolean
        Try
            MyGlobals.RemoveCachedObject(Me.WorkID.ToString)

            Dim ldbcmdCommand As New SqlClient.SqlCommand
            ldbcmdCommand.CommandText = "del_work"

            ldbcmdCommand.CommandType = CommandType.StoredProcedure
            ' Set up parameter for stored procedure 
            Dim prmWorkID As New SqlClient.SqlParameter
            prmWorkID.ParameterName = "@work_id"
            prmWorkID.SqlDbType = SqlDbType.UniqueIdentifier
            'prmWorkID.Size = 5
            prmWorkID.Value = m_guidWorkID
            ldbcmdCommand.Parameters.Add(prmWorkID)

            Dim lintRowsAffected As Integer = MyGlobals.g_objDatabaseLayer.ExecuteNonQuery(ldbcmdCommand)
            If lintRowsAffected > 0 Then
                Return True
            Else
                Return False
            End If
        Catch
            Return False
        End Try

    End Function

    Public Function Save() As Boolean
        Try
            If Not m_objComposer Is Nothing Then
                If Not m_objComposer.ItemAlreadyPersisted Then
                    m_objComposer.Save()
                End If
            End If
            If Not m_objBook Is Nothing Then
                If Not m_objBook.ItemAlreadyPersisted Then
                    m_objBook.Save()
                End If
            End If
            If Not m_objHymnTuneName Is Nothing Then
                If Not m_objHymnTuneName.ItemAlreadyPersisted Then
                    m_objHymnTuneName.Save()
                End If
            End If
            If Not m_objInstrumentation Is Nothing Then
                If Not m_objInstrumentation.ItemAlreadyPersisted Then
                    m_objInstrumentation.Save()
                End If
            End If

            MyGlobals.AddCachedObject(Me, Me.WorkID.ToString)

            If m_blnObjectAlreadyPersisted Then
                Dim ldbcmdCommand As New SqlClient.SqlCommand
                ldbcmdCommand.CommandText = "upd_work"

                ldbcmdCommand.CommandType = CommandType.StoredProcedure
                ' Set up parameter for stored procedure 
                Dim prmWorkID As New SqlClient.SqlParameter
                prmWorkID.ParameterName = "@work_id"
                prmWorkID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmWorkID.Size = 5
                prmWorkID.Value = m_guidWorkID
                ldbcmdCommand.Parameters.Add(prmWorkID)
                ' Set up parameter for stored procedure 
                Dim prmComposerID As New SqlClient.SqlParameter
                prmComposerID.ParameterName = "@composer_id"
                prmComposerID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmComposerID.Size = 5
                If m_objComposer Is Nothing Then
                    prmComposerID.Value = DBNull.Value
                Else
                    prmComposerID.Value = m_objComposer.ComposerID
                End If
                ldbcmdCommand.Parameters.Add(prmComposerID)
                ' Set up parameter for stored procedure 
                Dim prmBookID As New SqlClient.SqlParameter
                prmBookID.ParameterName = "@book_id"
                prmBookID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmBookID.Size = 5
                If m_objBook Is Nothing Then
                    prmBookID.Value = DBNull.Value
                Else
                    prmBookID.Value = m_objBook.BookID
                End If
                ldbcmdCommand.Parameters.Add(prmBookID)
                ' Set up parameter for stored procedure 
                Dim prmPageNumber As New SqlClient.SqlParameter
                prmPageNumber.ParameterName = "@page_number"
                prmPageNumber.SqlDbType = SqlDbType.Int
                'prmPageNumber.Size = 5
                prmPageNumber.Value = m_intPageNumber
                ldbcmdCommand.Parameters.Add(prmPageNumber)
                ' Set up parameter for stored procedure 
                Dim prmWorkName As New SqlClient.SqlParameter
                prmWorkName.ParameterName = "@work_name"
                prmWorkName.SqlDbType = SqlDbType.VarChar
                prmWorkName.Size = MyGlobals.WORK_NAME_SIZE
                prmWorkName.Value = m_strWorkName
                ldbcmdCommand.Parameters.Add(prmWorkName)
                ' Set up parameter for stored procedure 
                Dim prmOpusInfo As New SqlClient.SqlParameter
                prmOpusInfo.ParameterName = "@opus_info"
                prmOpusInfo.SqlDbType = SqlDbType.VarChar
                prmOpusInfo.Size = MyGlobals.OPUS_INFO_SIZE
                prmOpusInfo.Value = m_strOpusInfo
                ldbcmdCommand.Parameters.Add(prmOpusInfo)
                ' Set up parameter for stored procedure 
                Dim prmInstrumentationID As New SqlClient.SqlParameter
                prmInstrumentationID.ParameterName = "@instrumentation_id"
                prmInstrumentationID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmInstrumentationID.Size = MyGlobals.INSTRUMENTATION_SIZE
                If m_objInstrumentation Is Nothing Then
                    prmInstrumentationID.Value = DBNull.Value
                Else
                    prmInstrumentationID.Value = m_objInstrumentation.InstrumentationID
                End If
                ldbcmdCommand.Parameters.Add(prmInstrumentationID)
                ' Set up parameter for stored procedure 
                Dim prmHymnTuneNameID As New SqlClient.SqlParameter
                prmHymnTuneNameID.ParameterName = "@hymn_tune_name_id"
                prmHymnTuneNameID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmHymnTuneNameID.Size = 5
                If m_objHymnTuneName Is Nothing Then
                    prmHymnTuneNameID.Value = DBNull.Value
                Else
                    prmHymnTuneNameID.Value = m_objHymnTuneName.HymnTuneNameID
                End If
                ldbcmdCommand.Parameters.Add(prmHymnTuneNameID)

                Dim lintRowsAffected As Integer = MyGlobals.g_objDatabaseLayer.ExecuteNonQuery(ldbcmdCommand)
                If lintRowsAffected > 0 Then
                    Return True
                Else
                    Return False
                End If
            Else
                Dim ldbcmdCommand As New SqlClient.SqlCommand
                ldbcmdCommand.CommandText = "ins_work"

                ldbcmdCommand.CommandType = CommandType.StoredProcedure
                ' Set up parameter for stored procedure 
                Dim prmWorkID As New SqlClient.SqlParameter
                prmWorkID.ParameterName = "@work_id"
                prmWorkID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmWorkID.Size = 5
                prmWorkID.Value = m_guidWorkID
                ldbcmdCommand.Parameters.Add(prmWorkID)
                ' Set up parameter for stored procedure 
                Dim prmComposerID As New SqlClient.SqlParameter
                prmComposerID.ParameterName = "@composer_id"
                prmComposerID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmComposerID.Size = 5
                If m_objComposer Is Nothing Then
                    prmComposerID.Value = DBNull.Value
                Else
                    prmComposerID.Value = m_objComposer.ComposerID
                End If
                ldbcmdCommand.Parameters.Add(prmComposerID)
                ' Set up parameter for stored procedure 
                Dim prmBookID As New SqlClient.SqlParameter
                prmBookID.ParameterName = "@book_id"
                prmBookID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmBookID.Size = 5
                If m_objBook Is Nothing Then
                    prmBookID.Value = DBNull.Value
                Else
                    prmBookID.Value = m_objBook.BookID
                End If
                ldbcmdCommand.Parameters.Add(prmBookID)
                ' Set up parameter for stored procedure 
                Dim prmPageNumber As New SqlClient.SqlParameter
                prmPageNumber.ParameterName = "@page_number"
                prmPageNumber.SqlDbType = SqlDbType.Int
                'prmPageNumber.Size = 5
                prmPageNumber.Value = m_intPageNumber
                ldbcmdCommand.Parameters.Add(prmPageNumber)
                ' Set up parameter for stored procedure 
                Dim prmWorkName As New SqlClient.SqlParameter
                prmWorkName.ParameterName = "@work_name"
                prmWorkName.SqlDbType = SqlDbType.VarChar
                prmWorkName.Size = MyGlobals.WORK_NAME_SIZE
                prmWorkName.Value = m_strWorkName
                ldbcmdCommand.Parameters.Add(prmWorkName)
                ' Set up parameter for stored procedure 
                Dim prmOpusInfo As New SqlClient.SqlParameter
                prmOpusInfo.ParameterName = "@opus_info"
                prmOpusInfo.SqlDbType = SqlDbType.VarChar
                prmOpusInfo.Size = MyGlobals.OPUS_INFO_SIZE
                prmOpusInfo.Value = m_strOpusInfo
                ldbcmdCommand.Parameters.Add(prmOpusInfo)
                ' Set up parameter for stored procedure 
                Dim prmInstrumentationID As New SqlClient.SqlParameter
                prmInstrumentationID.ParameterName = "@instrumentation_id"
                prmInstrumentationID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmInstrumentationID.Size = MyGlobals.INSTRUMENTATION_SIZE
                If m_objInstrumentation Is Nothing Then
                    prmInstrumentationID.Value = DBNull.Value
                Else
                    prmInstrumentationID.Value = m_objInstrumentation.InstrumentationID
                End If
                ldbcmdCommand.Parameters.Add(prmInstrumentationID)
                ' Set up parameter for stored procedure 
                Dim prmHymnTuneNameID As New SqlClient.SqlParameter
                prmHymnTuneNameID.ParameterName = "@hymn_tune_name_id"
                prmHymnTuneNameID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmHymnTuneNameID.Size = 5
                If m_objHymnTuneName Is Nothing Then
                    prmHymnTuneNameID.Value = DBNull.Value
                Else
                    prmHymnTuneNameID.Value = m_objHymnTuneName.HymnTuneNameID
                End If
                ldbcmdCommand.Parameters.Add(prmHymnTuneNameID)

                Dim lintRowsAffected As Integer = MyGlobals.g_objDatabaseLayer.ExecuteNonQuery(ldbcmdCommand)
                If lintRowsAffected > 0 Then
                    m_blnObjectAlreadyPersisted = True
                    Return True
                Else
                    Return False
                End If
            End If
        Catch e As SqlClient.SqlException
            MessageBox.Show(e.Message)
            Return False
        Catch
            Return False
        End Try

    End Function

    Public Property WorkID() As Guid
        Get
            Return m_guidWorkID
        End Get
        Set(ByVal Value As Guid)
            m_guidWorkID = Value
        End Set
    End Property

    Public Property Composer() As clsComposer
        Get
            Return m_objComposer
        End Get
        Set(ByVal Value As clsComposer)
            m_objComposer = Value
        End Set
    End Property

    Public Property Book() As clsBook
        Get
            Return m_objBook
        End Get
        Set(ByVal Value As clsBook)
            m_objBook = Value
        End Set
    End Property

    Public Property PageNumber() As Integer
        Get
            Return m_intPageNumber
        End Get
        Set(ByVal Value As Integer)
            m_intPageNumber = Value
        End Set
    End Property

    Public Property PageNumberAsString() As String
        Get
            Return m_intPageNumber.ToString
        End Get
        Set(ByVal Value As String)
            If Val(Value) > 0 Then
                m_intPageNumber = CType(Value, Integer)
            Else
                m_intPageNumber = 0
            End If
        End Set
    End Property

    Public Property WorkName() As String
        Get
            Return m_strWorkName
        End Get
        Set(ByVal Value As String)
            m_strWorkName = Value.Trim
        End Set
    End Property

    Public Property OpusInfo() As String
        Get
            Return m_strOpusInfo
        End Get
        Set(ByVal Value As String)
            m_strOpusInfo = Value.Trim
        End Set
    End Property

    Public Property Instrumentation() As clsInstrumentation
        Get
            Return m_objInstrumentation
        End Get
        Set(ByVal Value As clsInstrumentation)
            m_objInstrumentation = Value
        End Set
    End Property

    Public Property HymnTuneName() As clsHymnTuneName
        Get
            Return m_objHymnTuneName
        End Get
        Set(ByVal Value As clsHymnTuneName)
            m_objHymnTuneName = Value
        End Set
    End Property

    Public Property ItemAlreadyPersisted() As Boolean
        Get
            Return m_blnObjectAlreadyPersisted
        End Get
        Set(ByVal Value As Boolean)
            m_blnObjectAlreadyPersisted = Value
        End Set
    End Property

    Public Function Clone() As Object Implements ICloneable.Clone
        Dim lobjReturnValue As New clsWork
        lobjReturnValue.WorkID = Me.WorkID
        lobjReturnValue.Composer = Me.Composer
        lobjReturnValue.Book = Me.Book
        lobjReturnValue.PageNumber = Me.PageNumber
        lobjReturnValue.WorkName = Me.WorkName
        lobjReturnValue.OpusInfo = Me.OpusInfo
        lobjReturnValue.Instrumentation = Me.Instrumentation
        lobjReturnValue.HymnTuneName = Me.HymnTuneName
        lobjReturnValue.ItemAlreadyPersisted = Me.ItemAlreadyPersisted

        Return lobjReturnValue
    End Function

    Public Function StateIsIdentical(ByVal lobjOtherWork As clsWork) As Boolean
        Dim blnResult As Boolean

        If Me.WorkID.Equals(lobjOtherWork.WorkID) _
        AndAlso Me.PageNumber = lobjOtherWork.PageNumber _
        AndAlso Me.WorkName = lobjOtherWork.WorkName _
        AndAlso Me.OpusInfo = lobjOtherWork.OpusInfo Then
            blnResult = True

            If Me.Composer Is Nothing Then
                If lobjOtherWork.Composer Is Nothing Then
                    'OK
                Else
                    Return False
                End If
            Else
                If lobjOtherWork.Composer Is Nothing Then
                    Return False
                Else
                    blnResult = Me.Composer.ComposerID.Equals(lobjOtherWork.Composer.ComposerID)
                End If
            End If

            If Not blnResult Then
                Return False
            End If

            If Me.Book Is Nothing Then
                If lobjOtherWork.Book Is Nothing Then
                    'OK
                Else
                    Return False
                End If
            Else
                If lobjOtherWork.Book Is Nothing Then
                    Return False
                Else
                    blnResult = Me.Book.BookID.Equals(lobjOtherWork.Book.BookID)
                End If
            End If

            If Not blnResult Then
                Return False
            End If

            If Me.Instrumentation Is Nothing Then
                If lobjOtherWork.Instrumentation Is Nothing Then
                    'OK
                Else
                    Return False
                End If
            Else
                If lobjOtherWork.Instrumentation Is Nothing Then
                    Return False
                Else
                    blnResult = Me.Instrumentation.InstrumentationID.Equals(lobjOtherWork.Instrumentation.InstrumentationID)
                End If
            End If

            If Not blnResult Then
                Return False
            End If

            If Me.HymnTuneName Is Nothing Then
                If lobjOtherWork.HymnTuneName Is Nothing Then
                    'OK
                Else
                    Return False
                End If
            Else
                If lobjOtherWork.HymnTuneName Is Nothing Then
                    Return False
                Else
                    blnResult = Me.HymnTuneName.HymnTuneNameID.Equals(lobjOtherWork.HymnTuneName.HymnTuneNameID)
                End If
            End If
        Else
            Return False
        End If

        Return blnResult

    End Function

    Public Function Validate(ByRef lstrErrorString As String) As Boolean
        lstrErrorString = ""

        If Me.Composer Is Nothing Then
            lstrErrorString = "Composer is required"
            Return False
        End If

        If Me.Book Is Nothing Then
            lstrErrorString = "Book is required"
            Return False
        End If

        If Me.WorkName.Length = 0 Then
            lstrErrorString = "Work Name is required"
            Return False
        End If

        If Me.Instrumentation Is Nothing Then
            lstrErrorString = "Instrumentation is required"
            Return False
        End If

        Return True
    End Function

    Public Function OKToDelete(ByRef lstrReferencedByString As String) As Boolean
        lstrReferencedByString = ""
        Return True
    End Function

    Public Overrides Function ToString() As String
        Dim strReturnString As New StringBuilder

        If m_objBook Is Nothing Then
            'strReturnString.Append(MyGlobals.COLLECTION_STRING)
        Else
            strReturnString.Append(m_objBook.BookTitle)
        End If

        strReturnString.Append(m_intPageNumber)
        strReturnString.Append(" - ")
        strReturnString.Append(m_strWorkName)

        If m_objComposer Is Nothing Then
            'strReturnString.Append(MyGlobals.COLLECTION_STRING)
        Else
            strReturnString.Append(m_objComposer.ComposerName)
        End If

        Return strReturnString.ToString
    End Function

    Public Sub WriteXML(ByRef objXMLWriter As XmlTextWriter)
        objXMLWriter.WriteStartElement("Work")

        objXMLWriter.WriteElementString("WorkID", Me.WorkID.ToString)
        objXMLWriter.WriteElementString("WorkName", Me.WorkName)
        objXMLWriter.WriteElementString("ComposerID", Me.Composer.ComposerID.ToString)
        Me.Composer.WriteXML(objXMLWriter)
        objXMLWriter.WriteElementString("BookID", Me.Book.BookID.ToString)
        Me.Book.WriteXML(objXMLWriter)
        If Me.HymnTuneName Is Nothing Then
            objXMLWriter.WriteElementString("HymnTuneNameID", "")
        Else
            objXMLWriter.WriteElementString("HymnTuneNameID", Me.HymnTuneName.HymnTuneNameID.ToString)
            Me.HymnTuneName.WriteXML(objXMLWriter)
        End If
        objXMLWriter.WriteElementString("OpusInfo", Me.OpusInfo)
        objXMLWriter.WriteElementString("InstrumentationID", Me.Instrumentation.InstrumentationID.ToString)
        Me.Instrumentation.WriteXML(objXMLWriter)
        objXMLWriter.WriteElementString("PageNumber", Me.PageNumberAsString)

        objXMLWriter.WriteEndElement()
    End Sub

    Public Shared Function GetAllWorks() As System.Collections.Generic.List(Of clsWork)
        'Preload to cache
        clsHymnTuneName.AddAllHymnTuneNamesToCache()
        clsComposer.AddAllComposersToCache()
        clsInstrumentation.AddAllInstrumentationsToCache()
        clsPublisher.AddAllPublishersToCache()
        clsBook.AddAllBooksToCache()

        Dim lguidWorkID As Guid
        Dim ldbcmdCommand As New SqlClient.SqlCommand
        Dim lobjWork As clsWork
        Dim lcolWorks As New System.Collections.Generic.List(Of clsWork)

        ldbcmdCommand.CommandText = "sel_work_all"

        ldbcmdCommand.CommandType = CommandType.StoredProcedure

        Dim ldbdsWorks As DataSet = MyGlobals.g_objDatabaseLayer.ExecuteDataSet(ldbcmdCommand)

        If ldbdsWorks.Tables(0).Rows.Count > 0 Then
            Dim ldbrwRow As DataRow
            For Each ldbrwRow In ldbdsWorks.Tables(0).Rows
                lguidWorkID = CType(ldbrwRow.Item("work_id"), Guid)

                If MyGlobals.CachedObjectExists(lguidWorkID.ToString) Then
                    lobjWork = CType(MyGlobals.CachedObjectRetrieve(lguidWorkID.ToString), clsWork)
                Else
                    'lobjWork = New clsWork(lguidWorkID)
                    lobjWork = New clsWork(ldbrwRow)
                End If
                lcolWorks.Add(lobjWork)
            Next
        End If
        ldbdsWorks.Clear()

        Return lcolWorks
    End Function

End Class