admin.vb

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

        ActionUpdate()
    End Sub

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

        ActionDelete()
    End Sub
#End Region

#Region " Add, Update and Delete Procedures "
    Private Sub ActionAdd()
        'Initialize a new instance of the data access base class
        Using objData As New WDABase
            Try
                'Add database row based on the active screen
                Select Case strActiveScreen
                    Case "Projects"
                        'Set the SQL string
                        objData.SQL = "usp_InsertProject"
                        'Initialize the Command object
                        objData.InitializeCommand()
                        'Add the Parameters to the Parameters collection
                        objData.AddParameter("inProjectID", _
                            Data.OracleClient.OracleType.Char, 36, _
                            Guid.NewGuid.ToString.ToUpper)
                        objData.AddParameter("inProjectName", _
                            Data.OracleClient.OracleType.VarChar, 50, _
                            txtProjectName.Text)
                        objData.AddParameter("inProjectDescription", _
                            Data.OracleClient.OracleType.Clob, _
                            txtProjectDescription.Text.Length, _
                            txtProjectDescription.Text)
                        objData.AddParameter("inSequenceNumber", _
                            Data.OracleClient.OracleType.Number, 1, _
                            CType(txtSequenceNumber.Text, Byte))
                        'Open the database connection
                        objData.OpenConnection()
                        'Execute the query
                        intRowsAffected = objData.Command.ExecuteNonQuery()
                        'Close the database connection
                        objData.CloseConnection()
                        If intRowsAffected = 0 Then
                            Throw New Exception("Insert Project Failed")
                        End If
                        'Clear the input fields
                        txtProjectName.Text = String.Empty
                        txtProjectDescription.Text = String.Empty
                        txtSequenceNumber.Text = String.Empty
                        'Reload the Projects list
                        LoadProjects()

                    Case "Groups"
                        'Set the SQL string
                        objData.SQL = "usp_InsertGroup"
                        'Initialize the Command object
                        objData.InitializeCommand()
                        'Add the Parameters to the Parameters collection
                        objData.AddParameter("inGroupID", _
                            Data.OracleClient.OracleType.Char, 36, _
                            Guid.NewGuid.ToString.ToUpper)
                        objData.AddParameter("inGroupName", _
                            Data.OracleClient.OracleType.VarChar, 50, _
                            txtGroupName.Text)
                        objData.AddParameter("inGroupDescription", _
                            Data.OracleClient.OracleType.Clob, _
                            txtGroupDescription.Text.Length, _
                            txtGroupDescription.Text)
                        'Open the database connection
                        objData.OpenConnection()
                        'Execute the query
                        intRowsAffected = objData.Command.ExecuteNonQuery()
                        'Close the database connection
                        objData.CloseConnection()
                        If intRowsAffected = 0 Then
                            Throw New Exception("Insert Group Failed")
                        End If
                        'Clear the input fields
                        txtGroupName.Text = String.Empty
                        txtGroupDescription.Text = String.Empty
                        'Reload the Groups list
                        LoadGroups()

                    Case "Group Projects"
                        'Turn the loading flag on so no items are processed
                        blnLoading = True
                        'Delete any previous projects
                        Call ActionDelete()
                        'Turn the loading flag off
                        blnLoading = False
                        'Set the SQL string
                        objData.SQL = "usp_InsertGroupProject"
                        'Open the database connection
                        objData.OpenConnection()
                        'Initialize the Command object
                        objData.InitializeCommand()
                        For intIndex = 0 To _
                            objGroupProjectsDS.Tables( _
                            "GroupProjects").Rows.Count - 1
                            'Add the Parameters to the Parameters collection
                            objData.AddParameter("inGroupProjectID", _
                                Data.OracleClient.OracleType.Char, 36, _
                                Guid.NewGuid.ToString.ToUpper)
                            objData.AddParameter("inGroupID", _
                                Data.OracleClient.OracleType.Char, 36, _
                                cboGroups.SelectedItem.Item("GroupID"))
                            objData.AddParameter("inProjectID", _
                                Data.OracleClient.OracleType.Char, 36, _
                                objGroupProjectsDS.Tables( _
                                "GroupProjects").Rows(intIndex).Item("ProjectID"))
                            'Execute the stored procedure
                            objData.Command.ExecuteNonQuery()
                            'Clear the Parameters collection for the next insert
                            objData.Command.Parameters.Clear()
                        Next
                        'Close the database connection
                        objData.CloseConnection()
                        'Clear previous bindings
                        lstGroupProjects.DataSource = Nothing
                        lstGroupProjects.DisplayMember = String.Empty
                        lstGroupProjects.ValueMember = String.Empty
                        lstGroupProjects.Items.Clear()
                        'Turn the loading flag on so no items are processed
                        blnLoading = True
                        cboGroups.SelectedIndex = -1
                        'Turn the loading flag off
                        blnLoading = False

                    Case "Roles"
                    Case "Users"
                End Select

                'Display a message that the record was added
                ToolStripStatus.Text = "Record Added"
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)
            End Try
        End Using
    End Sub

    Private Sub ActionUpdate()
        'Initialize a new instance of the data access base class
        Using objData As New WDABase
            Try
                'Update database row based on the active screen
                Select Case strActiveScreen
                    Case "Projects"
                        'Set the SQL string
                        objData.SQL = "usp_UpdateProject"
                        'Initialize the Command object
                        objData.InitializeCommand()
                        'Add the Parameters to the Parameters collection
                        objData.AddParameter("inProjectName", _
                            Data.OracleClient.OracleType.VarChar, 50, _
                            txtProjectName.Text)
                        objData.AddParameter("inProjectDescription", _
                            Data.OracleClient.OracleType.Clob, _
                            txtProjectDescription.Text.Length, _
                            txtProjectDescription.Text)
                        objData.AddParameter("inSequenceNumber", _
                            Data.OracleClient.OracleType.Number, 1, _
                            CType(txtSequenceNumber.Text, Byte))
                        objData.AddParameter("inProjectID", _
                            Data.OracleClient.OracleType.Char, 36, _
                            txtProjectID.Text)
                        'Open the database connection
                        objData.OpenConnection()
                        'Execute the query
                        intRowsAffected = objData.Command.ExecuteNonQuery()
                        'Close the database connection
                        objData.CloseConnection()
                        If intRowsAffected = 0 Then
                            Throw New Exception("Update Project Failed")
                        End If
                        'Clear the input fields
                        txtProjectID.Text = String.Empty
                        txtProjectName.Text = String.Empty
                        txtProjectDescription.Text = String.Empty
                        txtSequenceNumber.Text = String.Empty
                        txtProjectUpdateDate.Text = String.Empty
                        'Reload the Projects list
                        LoadProjects()

                    Case "Groups"
                        'Set the SQL string
                        objData.SQL = "usp_UpdateGroup"
                        'Initialize the Command object
                        objData.InitializeCommand()
                        'Add the Parameters to the Parameters collection
                        objData.AddParameter("inGroupName", _
                            Data.OracleClient.OracleType.VarChar, 50, _
                            txtGroupName.Text)
                        objData.AddParameter("inGroupDescription", _
                            Data.OracleClient.OracleType.Clob, _
                            txtGroupDescription.Text.Length, _
                            txtGroupDescription.Text)
                        objData.AddParameter("inGroupID", _
                            Data.OracleClient.OracleType.Char, 36, _
                            txtGroupID.Text)
                        'Open the database connection
                        objData.OpenConnection()
                        'Execute the query
                        intRowsAffected = objData.Command.ExecuteNonQuery()
                        'Close the database connection
                        objData.CloseConnection()
                        If intRowsAffected = 0 Then
                            Throw New Exception("Update Group Failed")
                        End If
                        'Clear the input fields
                        txtGroupID.Text = String.Empty
                        txtGroupName.Text = String.Empty
                        txtGroupDescription.Text = String.Empty
                        txtGroupUpdateDate.Text = String.Empty
                        'Reload the Groups list
                        LoadGroups()

                    Case "Group Projects"
                        'Turn the loading flag on so no items are processed
                        blnLoading = True
                        Call ActionAdd()

                    Case "Roles"
                    Case "Users"
                End Select

                'Display a message that the record was update
                ToolStripStatus.Text = "Record Updated"
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)
            End Try
        End Using
    End Sub

    Private Sub ActionDelete()
        'Initialize a new instance of the data access base class
        Using objData As New WDABase
            Try
                'Delete database row based on the active screen
                Select Case strActiveScreen
                    Case "Projects"
                        'Set the SQL string
                        objData.SQL = "usp_DeleteProject"
                        'Initialize the Command object
                        objData.InitializeCommand()
                        'Add the Parameters to the Parameters collection
                        objData.AddParameter("inProjectID", _
                            Data.OracleClient.OracleType.Char, 36, _
                            txtProjectID.Text)
                        'Open the database connection
                        objData.OpenConnection()
                        'Execute the query
                        intRowsAffected = objData.Command.ExecuteNonQuery()
                        'Close the database connection
                        objData.CloseConnection()
                        If intRowsAffected = 0 Then
                            Throw New Exception("Delete Project Failed")
                        End If
                        'Clear the input fields
                        txtProjectID.Text = String.Empty
                        txtProjectName.Text = String.Empty
                        txtProjectDescription.Text = String.Empty
                        txtSequenceNumber.Text = String.Empty
                        txtProjectUpdateDate.Text = String.Empty
                        'Reload the Projects list
                        LoadProjects()

                    Case "Groups"
                        'Set the SQL string
                        objData.SQL = "usp_DeleteGroup"
                        'Initialize the Command object
                        objData.InitializeCommand()
                        'Add the Parameters to the Parameters collection
                        objData.AddParameter("inGroupID", _
                            Data.OracleClient.OracleType.Char, 36, _
                            txtGroupID.Text)
                        'Open the database connection
                        objData.OpenConnection()
                        'Execute the query
                        intRowsAffected = objData.Command.ExecuteNonQuery()
                        'Close the database connection
                        objData.CloseConnection()
                        If intRowsAffected = 0 Then
                            Throw New Exception("Delete Group Failed")
                        End If
                        'Clear the input fields
                        txtGroupID.Text = String.Empty
                        txtGroupName.Text = String.Empty
                        txtGroupDescription.Text = String.Empty
                        txtGroupUpdateDate.Text = String.Empty
                        'Reload the Groups list
                        LoadGroups()

                    Case "Group Projects"
                        'Set the SQL string
                        objData.SQL = "usp_DeleteGroupProjects"
                        'Initialize the Command object
                        objData.InitializeCommand()
                        'Add the Parameters to the Parameters collection
                        objData.AddParameter("inGroupID", _
                            Data.OracleClient.OracleType.Char, _
                            36, cboGroups.SelectedItem.Item("GroupID"))
                        'Open the database connection
                        objData.OpenConnection()
                        'Execute the stored procedure
                        objData.Command.ExecuteNonQuery()
                        'Close the database connection
                        objData.CloseConnection()
                        'Clear previous bindings
                        If Not blnLoading Then
                            lstGroupProjects.DataSource = Nothing
                            lstGroupProjects.DisplayMember = String.Empty
                            lstGroupProjects.ValueMember = String.Empty
                            lstGroupProjects.Items.Clear()
                            blnLoading = True
                            cboGroups.SelectedIndex = -1
                            blnLoading = False
                        End If

                    Case "Roles"
                    Case "Users"
                End Select

                'Display a message that the record was deleted
                ToolStripStatus.Text = "Record Deleted"
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message, strAppTitle)
            End Try
        End Using
    End Sub
#End Region

#Region " Projects Procedures "
    Private Sub LoadProjects()