admin.vb

数据库学习的绝好例子简单的数据库经典入门

        'Declare variables
        Dim objListViewItem As ListViewItem

        'Initialize a new instance of the data access base class
        Using objData As New WDABase
            Try
                'Clear previous bindings
                lstProjects.DataSource = Nothing
                lstProjects.DisplayMember = String.Empty
                lstProjects.ValueMember = String.Empty

                'Get all Projects in a DataSet
                objData.SQL = "ProjectsPackage.usp_SelectProjects"
                objData.InitializeCommand()
                objData.AddParameter("results_cursor", _
                    Data.OracleClient.OracleType.Cursor, _
                    Data.ParameterDirection.Output)
                objProjectsDS = New Data.DataSet
                Call objData.FillDataSet(objProjectsDS, "Projects")

                'Clear previous list
                lvwProjects.Items.Clear()

                'Process all rows
                For intIndex = 0 To objProjectsDS.Tables("Projects").Rows.Count - 1
                    'Create a new listview item
                    objListViewItem = New ListViewItem
                    'Add the data to the listview item
                    objListViewItem.Text = objProjectsDS.Tables( _
                        "Projects").Rows(intIndex).Item("ProjectName")
                    objListViewItem.Tag = objProjectsDS.Tables( _
                        "Projects").Rows(intIndex).Item("ProjectID")
                    'Add the sub items to the listview item
                    objListViewItem.SubItems.Add(objProjectsDS.Tables( _
                        "Projects").Rows(intIndex).Item("ProjectDescription"))
                    objListViewItem.SubItems.Add(objProjectsDS.Tables( _
                        "Projects").Rows(intIndex).Item("SequenceNumber"))
                    objListViewItem.SubItems.Add(Format(objProjectsDS.Tables( _
                        "Projects").Rows(intIndex).Item("LastUpdateDate"), "g"))
                    'Add the listview item to the listview control
                    lvwProjects.Items.Add(objListViewItem)
                Next

                'Rebind ListBox control
                lstProjects.DataSource = objProjectsDS.Tables("Projects")
                lstProjects.DisplayMember = "ProjectName"
                lstProjects.ValueMember = "ProjectID"
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)
            End Try
        End Using

        'Cleanup
        objListViewItem = Nothing

    End Sub

    Private Sub lvwProjects_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles lvwProjects.Click

        'Initialize a new instance of the data access base class
        Using objData As New WDABase
            Try
                'Get the specific project selected in the ListView control
                objData.SQL = "ProjectPackage.usp_SelectProject"
                objData.InitializeCommand()
                objData.AddParameter("inProjectID", _
                    Data.OracleClient.OracleType.Char, _
                    36, lvwProjects.SelectedItems.Item(0).Tag)
                objData.AddParameter("results_cursor", _
                    Data.OracleClient.OracleType.Cursor, _
                    Data.ParameterDirection.Output)
                objData.OpenConnection()
                objData.DataReader = objData.Command.ExecuteReader

                'See if any data exists before continuing
                If objData.DataReader.HasRows Then

                    'Read the first and only row of data
                    objData.DataReader.Read()

                    'Populate the Project Details section
                    txtProjectID.Text = _
                        objData.DataReader.Item("ProjectID").ToString.ToUpper
                    txtProjectName.Text = _
                        objData.DataReader.Item("ProjectName")
                    txtProjectDescription.Text = _
                        objData.DataReader.Item("ProjectDescription")
                    txtSequenceNumber.Text = _
                        objData.DataReader.Item("SequenceNumber")
                    txtProjectUpdateDate.Text = _
                        Format(objData.DataReader.Item("LastUpdateDate"), "g")

                End If

                objData.DataReader.Close()
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)
            End Try
        End Using
    End Sub

    Private Sub lstProjects_MouseDown(ByVal sender As Object, _
        ByVal e As System.Windows.Forms.MouseEventArgs) _
        Handles lstProjects.MouseDown

        If e.Button = MouseButtons.Left Then
            lstProjects.DoDragDrop(objProjectsDS.Tables("Projects").Rows( _
                lstProjects.SelectedIndex)("ProjectID").ToString, _
                DragDropEffects.Copy)
        End If
    End Sub

    Private Sub lstGroupProjects_DragEnter(ByVal sender As Object, _
        ByVal e As System.Windows.Forms.DragEventArgs) _
        Handles lstGroupProjects.DragEnter

        'Exit if nothing has been selected in the cboGroups ComboBox
        If cboGroups.SelectedIndex = -1 Then
            Exit Sub
        End If

        'Now ensure that the drag content is the correct type for this control
        If (e.Data.GetDataPresent(DataFormats.Text)) Then
            'If the item does not already exist then allow the copy
            Dim objDataView As Data.DataView = New _
                Data.DataView(objGroupProjectsDS.Tables("GroupProjects"))
            objDataView.Sort = "ProjectID"
            intIndex = objDataView.Find(e.Data.GetData(DataFormats.Text))
            If intIndex = -1 Then
                e.Effect = DragDropEffects.Copy
            Else
                e.Effect = DragDropEffects.None
            End If
        Else
            e.Effect = DragDropEffects.None
        End If
    End Sub

    Private Sub lstGroupProjects_DragDrop(ByVal sender As Object, _
        ByVal e As System.Windows.Forms.DragEventArgs) _
        Handles lstGroupProjects.DragDrop

        Dim objDataRow As Data.DataRow
        Dim objDataView As Data.DataView = New _
            Data.DataView(objProjectsDS.Tables("Projects"))
        objDataView.Sort = "ProjectID"
        intIndex = objDataView.Find(e.Data.GetData(DataFormats.Text))
        objDataRow = objGroupProjectsDS.Tables("GroupProjects").NewRow
        objDataRow.Item("ProjectID") = New _
            Guid(e.Data.GetData(DataFormats.Text).ToString)
        objDataRow.Item("ProjectName") = _
            objDataView.Item(intIndex).Item("ProjectName")
        objGroupProjectsDS.Tables("GroupProjects").Rows.Add(objDataRow)
        objGroupProjectsDS.AcceptChanges()
    End Sub

    Private Sub lstGroupProjects_KeyUp(ByVal sender As Object, _
        ByVal e As System.Windows.Forms.KeyEventArgs) _
        Handles lstGroupProjects.KeyUp

        If e.KeyCode = Keys.Delete Then
            objGroupProjectsDS.Tables("GroupProjects").Rows.RemoveAt( _
                lstGroupProjects.SelectedIndex)
            objGroupProjectsDS.AcceptChanges()
        End If
    End Sub
#End Region

#Region " Groups Procedures "
    Private Sub LoadGroups()
        'Declare variables
        Dim objListViewItem As ListViewItem

        'Turn the loading flag on so no items are processed
        blnLoading = True

        'Initialize a new instance of the data access base class
        Using objData As New WDABase
            Try
                'Clear previous data bindings
                cboGroups.DataSource = Nothing
                cboGroups.DisplayMember = String.Empty
                cboGroups.ValueMember = String.Empty

                'Get all Groups in a DataSet object
                objData.SQL = "GroupsPackage.usp_SelectGroups"
                objData.InitializeCommand()
                objData.AddParameter("results_cursor", _
                    Data.OracleClient.OracleType.Cursor, _
                    Data.ParameterDirection.Output)
                objGroupsDS = New DataSet
                objData.FillDataSet(objGroupsDS, "Groups")

                'Clear previous list
                lvwGroups.Items.Clear()

                'Process all rows
                For intIndex = 0 To objGroupsDS.Tables("Groups").Rows.Count - 1

                    'Create a new listview item
                    objListViewItem = New ListViewItem

                    'Add the data to the listview item
                    objListViewItem.Text = _
                        objGroupsDS.Tables("Groups").Rows(intIndex).Item( _
                        "GroupName")
                    objListViewItem.Tag = _
                        objGroupsDS.Tables("Groups").Rows(intIndex).Item( _
                        "GroupID")

                    'Add the sub items to the listview item
                    objListViewItem.SubItems.Add( _
                        objGroupsDS.Tables("Groups").Rows(intIndex).Item( _
                        "GroupDescription"))
                    objListViewItem.SubItems.Add( _
                        Format(objGroupsDS.Tables("Groups").Rows(intIndex).Item( _
                        "LastUpdateDate"), "g"))

                    'Add the listview item to the listview control
                    lvwGroups.Items.Add(objListViewItem)

                Next

                'Rebind ComboBox control
                cboGroups.DataSource = objGroupsDS.Tables("Groups")
                cboGroups.DisplayMember = "GroupName"
                cboGroups.ValueMember = "GroupID"

                'Reset the selected index
                cboGroups.SelectedIndex = -1
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)
            End Try
        End Using

        'Cleanup
        objListViewItem = Nothing

        'Turn off loading switch
        blnLoading = False
    End Sub

    Private Sub lvwGroups_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles lvwGroups.Click

        'Initialize a new instance of the data access base class
        Using objData As New WDABase
            Try
                'Get the specific Group selected in the ListView control
                objData.SQL = "GroupPackage.usp_SelectGroup"
                objData.InitializeCommand()
                objData.AddParameter("inGroupID", _
                    Data.OracleClient.OracleType.Char, _
                    36, lvwGroups.SelectedItems.Item(0).Tag)
                objData.AddParameter("results_cursor", _
                    Data.OracleClient.OracleType.Cursor, _
                    Data.ParameterDirection.Output)
                objData.OpenConnection()
                objData.DataReader = objData.Command.ExecuteReader

                'See if any data exists before continuing
                If objData.DataReader.HasRows Then

                    'Read the first and only row of data
                    objData.DataReader.Read()

                    'Populate the Group Details section
                    txtGroupID.Text = _
                        objData.DataReader.Item("GroupID").ToString.ToUpper
                    txtGroupName.Text = _
                        objData.DataReader.Item("GroupName")
                    txtGroupDescription.Text = _
                        objData.DataReader.Item("GroupDescription")
                    txtGroupUpdateDate.Text = _
                        Format(objData.DataReader.Item("LastUpdateDate"), "g")

                End If

                'Close the DataReader
                objData.DataReader.Close()

                'Close the database connection
                objData.CloseConnection()
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)
            End Try
        End Using
    End Sub

    Private Sub cboGroups_SelectedIndexChanged(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles cboGroups.SelectedIndexChanged

        'Don't process if the ComboBox is being loaded
        If blnLoading Then
            Exit Sub
        End If

        Using objData As New WDABase
            Try
                'Clear previous bindings
                lstGroupProjects.DataSource = Nothing
                lstGroupProjects.DisplayMember = String.Empty
                lstGroupProjects.ValueMember = String.Empty

                'Get the specific Group Projects selected in the ComboBox control
                objData.SQL = "SELECT ProjectID, ProjectName " & _
                    "FROM vw_SelectGroupProjects " & _
                    "WHERE GroupID = :inGroupID " & _
                    "ORDER BY SequenceNumber"
                objData.InitializeCommand()
                objData.AddParameter("inGroupID", _
                    Data.OracleClient.OracleType.Char, _
                    36, cboGroups.SelectedItem.Item("GroupID"))

                objGroupProjectsDS = New Data.DataSet
                Call objData.FillDataSet(objGroupProjectsDS, "GroupProjects")

                'Rebind ListBox control
                lstGroupProjects.DataSource = _
                    objGroupProjectsDS.Tables("GroupProjects")
                lstGroupProjects.DisplayMember = "ProjectName"
                lstGroupProjects.ValueMember = "ProjectID"
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)
            End Try
        End Using
    End Sub
#End Region
End Class