Building a Shared Code Library as a VS.NET Plug-in

Imports System.Windows.Forms
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Diagnostics


Public Class GetOptions

    Private Shared pConnectionString As String

    Shared Property ConnectionString() As String
        Get
            'get this from the registry
            'Return "data source=(local);initial catalog=SupportMaster;persist security info=False;user id=support;password=support;packet size=4096"
            ' Return "workstation id=PAUL;packet size=4096;user id=sa;password=sa;data source=PAUL;persist security info=False;initial catalog=RADDev"

            Dim DataBaseName As String
            Dim ServerName As String

            Try
                ServerName = GetSetting("RadDev", "General", "DBServer")
            Catch
                ServerName = ""
            End Try

            Try
                DataBaseName = GetSetting("RadDev", "General", "Database")
            Catch
                DataBaseName = ""
            End Try

            pConnectionString = "user id=raddev;password=raddev;data source=" & ServerName & ";persist security info=False;initial catalog=" & DataBaseName
            Return pConnectionString
        End Get
        Set(ByVal Value As String)
            pConnectionString = Value
        End Set
    End Property
End Class

Public Class clsCodeDBFunctions
    Private aClsDB As New RadDev.Data.StoredProcedure.clsDBTierStoredProc
    Dim aClsDBSQL As New Data.SQLBased.clsDbTier
    Dim aMaintainDatabaseVersioning As New MaintainDatabaseVersioning
    Public Function ConnectionWorks() As Boolean
        Try
            Dim asqlconnection As New SqlConnection(GetOptions.ConnectionString)

            asqlconnection.Open()
            If asqlconnection.State <> ConnectionState.Open Then
                aMaintainDatabaseVersioning.CreateDatabaseSettings()
                Return False
            Else
                aMaintainDatabaseVersioning.UpdateDatabaseSettings()
                Return True
            End If
        Catch ex As Exception
            aMaintainDatabaseVersioning.CreateDatabaseSettings()
            Return False
        End Try
    End Function

    Public Sub LoadTree(ByVal tvMain As TreeView)
        System.Diagnostics.Debug.WriteLine("Refreshing Code Library")
        aClsDB.ConnectionString = GetOptions.ConnectionString
        ' Dim SQL As String
        Dim dr As SqlDataReader
        Dim ParentFound As Boolean
        Try
            'SQL = "Select * from tblCodeCategory order by ParentNodeID asc"
            aClsDB.HandleExceptions = False
            aClsDB.ClearParameters()
            dr = aClsDB.GetDataReader("proc_SelectCodeCategories")
            tvMain.Nodes.Clear()

            While dr.Read
                Dim addNode As New TreeNode
                Dim ParentNode As TreeNode
                Dim ParentNodeId As Integer = dr("ParentNodeID")
                Dim addNodeTag As New clsNodeTag

                ' set up the node
                addNode.Text = dr("DisplayCaption")

                If Not IsDBNull(dr("CodeEntryID")) Then
                    addNodeTag.CodeEntryID = dr("CodeEntryID")
                Else
                    addNodeTag.CodeEntryID = -1
                End If

                addNodeTag.CodeText = ""
                addNodeTag.EntryID = dr("CodeCategoryID")
                addNodeTag.IsCode = Not IsDBNull(dr("CodeEntryID"))


                addNode.Tag = addNodeTag 'dr("CodeCategoryID")  'store the ID here of the node


               
                If IsDBNull(dr("IconID")) Or (dr("IconID") = 0) Then
                    addNode.SelectedImageIndex = 1
                    addNode.ImageIndex = 0
                Else
                    addNode.SelectedImageIndex = dr("IconID")
                    addNode.ImageIndex = dr("IconID")
                End If

                ' look for the parent to attach it to
                ParentFound = False

                If tvMain.GetNodeCount(True) > 0 Then

                    For Each ParentNode In tvMain.Nodes ' this only gets the first node
                        ParentFound = CheckRecursive(ParentNode, ParentNodeId, addNode)
                        If ParentFound Then Exit For
                    Next
                End If

                If Not ParentFound Then
                    tvMain.Nodes.Add(addNode)
                End If

            End While
        Finally
            If Not dr Is Nothing Then
                dr.Close()
            End If
        End Try
        System.Diagnostics.Debug.WriteLine("Refresh Completed")
    End Sub


    Private Function CheckRecursive(ByVal n As TreeNode, ByVal ParentnodeID As Integer, ByRef addnode As TreeNode) As Boolean

        CheckRecursive = False

        Dim addNodeTag As clsNodeTag

        addNodeTag = n.Tag

        If addNodeTag.EntryID = ParentnodeID Then
            n.Nodes.Add(addnode)
            CheckRecursive = True
            Exit Function
        End If

        Dim aNode As TreeNode
        Dim RetValue As Boolean
        For Each aNode In n.Nodes
            CheckRecursive = CheckRecursive(aNode, ParentnodeID, addnode)
            If CheckRecursive = True Then
                Exit Function
            End If
        Next
    End Function

    Public Function InsertToDBCodeCategory(ByRef CodeCategoryID As Integer, ByVal DisplayCaption As String, ByVal IconID As Integer, ByVal ParentNodeID As Integer, ByVal CodeEntryID As Object) As Boolean
        Try
            With aClsDB
                .HandleExceptions = True
                .ClearParameters()
                .AddParameter("@CodeCategoryID", CodeCategoryID, ParameterDirection.Output)
                .AddParameter("@DisplayCaption", DisplayCaption, ParameterDirection.Input)
                .AddParameter("@IconID", IconID, ParameterDirection.Input)
                .AddParameter("@ParentNodeID", ParentNodeID, ParameterDirection.Input)
                If Not CodeEntryID Is Nothing Then
                    .AddParameter("@CodeEntryID", CodeEntryID, ParameterDirection.Input)
                    .ExecuteNonQuery("proc_AddCodeCategoryEntry")
                Else
                    .ExecuteNonQuery("proc_AddCategoryEntry")
                End If

                CodeCategoryID = .GetOutputParameterValue("@CodeCategoryID")

                If .HasError Then
                    InsertToDBCodeCategory = False
                    Trace.Write("Error saving code category: " & .ErrorMessage)
                Else
                    InsertToDBCodeCategory = True
                End If

            End With
        Catch
            InsertToDBCodeCategory = False
        End Try

    End Function

    Public Function GetCodeText(ByVal EntryID As Integer) As String
        Dim SQL As String
        Try
            SQL = "Select Codetext from TblCodeEntry where EntryID = " & EntryID.ToString
            GetCodeText = aClsDBSQL.GetDataScalar(SQL)
        Catch ex As Exception
            Trace.Write("Unable to retrieve text for Code : " & ex.Message)
            GetCodeText = ""
            Throw
        End Try
    End Function

    Public Function GetCodeEntryTypeID(ByVal EntryID As Integer) As Integer
        Dim SQL As String
        Try
            SQL = "Select EntryTypeID from TblCodeEntry where EntryID = " & EntryID.ToString
            GetCodeEntryTypeID = CInt(aClsDBSQL.GetDataScalar(SQL))
        Catch ex As Exception
            Trace.Write("Unable to retrieve text for Code : " & ex.Message)
            'GetCodeEntryTypeID = ""
            Throw
        End Try
    End Function

    Public Function GetCodeDescription(ByVal EntryID As Integer) As String
        Dim SQL As String
        Try
            SQL = "Select LongDesc from TblCodeEntry where EntryID = " & EntryID.ToString
            GetCodeDescription = aClsDBSQL.GetDataScalar(SQL)
        Catch ex As Exception
            Trace.Write("Unable to retrieve text for Code : " & ex.Message)
            GetCodeDescription = ""
            Throw
        End Try
    End Function

    ' run this off a trigger instead!
    Public Sub LogCodeUsage(ByVal CodeEntryID As Integer, ByVal UserID As Integer)
        With aClsDB
            .HandleExceptions = True
            .ClearParameters()
            .AddParameter("@UserID", UserID, ParameterDirection.Input)
            .AddParameter("@CodeEntryID", CodeEntryID, ParameterDirection.Input)
            .ExecuteNonQuery("proc_LogCodeUsage")

            If .HasError Then
                Trace.Write("Error logging code usage: " & .ErrorMessage)
            End If

        End With

    End Sub
    '  Dim insertCmd As String
    '  Dim mycommand As SqlCommand
    '  Try
    '      insertCmd = "Insert into tblCodeentryLogging (UserID, CodeEntryID, DateUsed) values (@UserID, @CodeEntryID, @DateUsed);"

    '      ' Initialize the SqlCommand with the new SQL string.
    '      Dim myConnection As SqlConnection
    '      myConnection = New SqlConnection(GetOptions.ConnectionString)

    '      mycommand = New SqlCommand(insertCmd, myConnection)
    '      ' Create new parameters for the SqlCommand object and
    '      ' initialize them to the input-form field values.

    '      mycommand.Parameters.Add(New SqlParameter("@UserID", _
    ' UserID))
    '      mycommand.Parameters.Add(New SqlParameter("@CodeEntryID", _
    ' CodeEntryID))

    '      mycommand.Parameters.Add(New SqlParameter("@DateUsed", _
    'Now))

    '      Try
    '          mycommand.Connection.Open()
    '          Try
    '              mycommand.ExecuteNonQuery()
    '              mycommand.Connection.Close()

    '          Catch ex As SqlException
    '              Trace.Write("Error in LogCodeInsert : " & ex.Message)
    '          End Try
    '      Finally
    '          If Not mycommand.Connection Is Nothing Then
    '              mycommand.Connection.Close()
    '          End If
    '      End Try
    '  Catch ex As Exception
    '      Trace.Write("Error in LogCodeInsert : " & ex.Message)
    '      Throw (ex)
    '  End Try
    'End Sub

    Public Function GetCodeEntryUserID(ByVal CodeEntryID As Integer) As Integer
        Dim SQL As String
        Dim Resultval As String
        Try

            SQL = "Select UserID from tblCodeEntry where EntryID = " & CodeEntryID.ToString
            Resultval = aClsDBSQL.GetDataScalar(SQL)

            GetCodeEntryUserID = CInt(Resultval)
        Catch ex As Exception
            Trace.Write("Unable to retrieve text for Code : " & ex.Message)
            Throw (ex)
        End Try
    End Function

    Public Sub DeleteCodeEntry(ByVal CodeEntryID As Integer)
        With aClsDB
            .ClearParameters()
            .AddParameter("@CodeEntryID", CodeEntryID, ParameterDirection.Input)
            .ExecuteNonQuery("proc_DeleteCodeEntry")

            If .HasError Then
                ' InsertToDBCodeCategory = False
                Trace.Write("Unable to delete code entry : " & .ErrorMessage)
            Else
                'InsertToDBCodeCategory = True
            End If

        End With
    End Sub

    Public Function CountCodeEntriesForCategory(ByVal CodeCategoryID As Integer) As Integer
        Dim SQL As String
        Try
            SQL = "Select count(*) as total from tblCodeCategory where ParentNodeID = " & CodeCategoryID.ToString
            CountCodeEntriesForCategory = aClsDBSQL.GetDataScalar(SQL)
        Catch ex As Exception
            Trace.Write("Unable to run sql : " & SQL & ex.Message)
            Throw (ex)
        End Try
    End Function

    Public Sub DeleteCodeCategoryEntry(ByVal CodeCategoryID As Integer)
        Dim SQL As String
        SQL = "Delete from tblCodeCategory where CodeCategoryID = " & CodeCategoryID.ToString
        aClsDBSQL.ExecuteNonQuery(SQL)
    End Sub

    Public Sub UpdateCodeCategoryName(ByVal CatName As String, ByVal EntryID As Integer)
        Dim SQL As String
        Try
            SQL = "Update tblCodeCategory set DisplayCaption = '" & CatName & "' where codecategoryid = " & EntryID.ToString
            aClsDBSQL.ExecuteNonQuery(SQL)
        Catch ex As Exception
            Trace.Write("Unable to UpdateCodeCategoryName : " & SQL & ex.Message)
            Throw (ex)
        End Try
    End Sub

    Public Sub RunSearch(ByVal SearchText As String, ByRef lvResults As ListView)

        Dim aDr As SqlDataReader
        Try
            With aClsDB
                .HandleExceptions = True
                .ClearParameters()
                .AddParameter("@SearchText", SearchText, ParameterDirection.Input)
                aDr = .GetDataReader("proc_SearchCode")
                lvResults.Items.Clear()
                If .HasError Then

                    'MessageBox.Show("Error saving code category: " & .ErrorMessage)
                Else
                    While aDr.Read
                        Dim alistviewitem As ListViewItem
                        alistviewitem = lvResults.Items.Add(aDr("DisplayCaption"))
                        alistviewitem.ImageIndex = aDr("IconID")
                        alistviewitem.Tag = aDr("EntryID")
                        Dim DateVal As DateTime = aDr("DateAdded")
                        alistviewitem.SubItems.Add(DateVal.ToShortDateString)
                        alistviewitem.SubItems.Add(aDr("FirstName") & " " & aDr("LastName"))
                    End While
                End If

            End With
        Catch
        Finally
            If Not aDr Is Nothing Then
                aDr.Close()
            End If
        End Try
    End Sub



End Class