wdagroups.vb

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

Public Class WDAGroups
    Inherits WDABase

#Region " Constructor and Destructor "
    Public Sub New(ByVal Company As String, ByVal Application As String)
        MyBase.New(Company, Application)
    End Sub

    Public Shadows Sub Dispose()
        MyBase.Dispose()
    End Sub
#End Region

#Region " Public Group Functions "
    Public Function GetGroups() As DataSet
        Try
            GetGroups = New DataSet
            MyBase.SQL = "GroupsPackage.usp_SelectGroups"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add a Parameter to the Parameters collection
            MyBase.AddParameter("results_cursor", _
                OracleClient.OracleType.Cursor, ParameterDirection.Output)
            'Fill the DataSet
            MyBase.FillDataSet(GetGroups, "Groups")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function GetGroup(ByVal GroupID As Guid) As DataSet
        Try
            GetGroup = New DataSet
            MyBase.SQL = "GroupPackage.usp_SelectGroup"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add a Parameter to the Parameters collection
            MyBase.AddParameter("inGroupID", OracleClient.OracleType.Char, _
                36, GroupID.ToString)
            MyBase.AddParameter("results_cursor", _
                OracleClient.OracleType.Cursor, ParameterDirection.Output)
            'Fill the DataSet
            MyBase.FillDataSet(GetGroup, "Group")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function AddGroup(ByVal Group As DataSet) As Boolean
        Try
            MyBase.SQL = "usp_InsertGroup"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("inGroupID", _
                OracleClient.OracleType.Char, 36, _
                Group.Tables("Group").Rows(0).Item("GroupID").ToString)
            MyBase.AddParameter("inGroupName", _
                OracleClient.OracleType.VarChar, 50, _
                Group.Tables("Group").Rows(0).Item("GroupName"))
            MyBase.AddParameter("inGroupDescription", _
                OracleClient.OracleType.Clob, _
                Group.Tables("Group").Rows(0).Item( _
                "GroupDescription").ToString.Length, _
                Group.Tables("Group").Rows(0).Item("GroupDescription"))
            'Execute the stored procedure
            AddGroup = ExecuteStoredProcedure()
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
                ExceptionErr.InnerException)
        End Try
    End Function

    Public Function UpdateGroup(ByVal Group As DataSet) As Boolean
        Try
            MyBase.SQL = "usp_UpdateGroup"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("inGroupName", _
                OracleClient.OracleType.VarChar, 50, _
                Group.Tables("Group").Rows(0).Item("GroupName"))
            MyBase.AddParameter("inGroupDescription", _
                OracleClient.OracleType.Clob, _
                Group.Tables("Group").Rows(0).Item( _
                "GroupDescription").ToString.Length, _
                Group.Tables("Group").Rows(0).Item("GroupDescription"))
            MyBase.AddParameter("inGroupID", _
                OracleClient.OracleType.Char, 36, _
                Group.Tables("Group").Rows(0).Item("GroupID").ToString)
            'Execute the stored procedure
            UpdateGroup = ExecuteStoredProcedure()
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
                ExceptionErr.InnerException)
        End Try
    End Function

    Public Function DeleteGroup(ByVal GroupID As Guid) As Boolean
        Try
            MyBase.SQL = "usp_DeleteGroup"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add a Parameter to the Parameters collection
            MyBase.AddParameter("inGroupID", _
               OracleClient.OracleType.Char, 36, GroupID.ToString)
            'Execute the stored procedure
            DeleteGroup = ExecuteStoredProcedure()
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
                ExceptionErr.InnerException)
        End Try
    End Function
#End Region

#Region " Public Group Projects Functions "
    Public Function GetGroupProjects(ByVal GroupID As Guid) As DataSet
        Try
            GetGroupProjects = New DataSet
            MyBase.SQL = "SELECT ProjectID, ProjectName " & _
                "FROM vw_SelectGroupProjects " & _
                "WHERE GroupID = :inGroupID " & _
                "ORDER BY SequenceNumber"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add a Parameter to the Parameters collection
            MyBase.AddParameter("inGroupID", _
                OracleClient.OracleType.Char, 36, GroupID.ToString)
            'Fill the DataSet
            MyBase.FillDataSet(GetGroupProjects, "GroupProjects")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
                ExceptionErr.InnerException)
        End Try
    End Function

    Public Function AddGroupProjects(ByVal GroupProjects As DataSet) As Boolean
        Try
            MyBase.SQL = "usp_InsertGroupProject"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("inGroupProjectID", _
                OracleClient.OracleType.Char, 36, Nothing)
            MyBase.AddParameter("inGroupID", _
                OracleClient.OracleType.Char, 36, Nothing)
            MyBase.AddParameter("inProjectID", _
                OracleClient.OracleType.Char, 36, Nothing)
            'Process all rows in the table
            For intIndex As Integer = 0 To _
                GroupProjects.Tables("GroupProjects").Rows.Count - 1
                MyBase.Command.Parameters.Item("inGroupProjectID").Value = _
                    GroupProjects.Tables("GroupProjects").Rows(intIndex).Item( _
                    "GroupProjectID").ToString
                MyBase.Command.Parameters.Item("inGroupID").Value = _
                    GroupProjects.Tables("GroupProjects").Rows(intIndex).Item( _
                    "GroupID").ToString
                MyBase.Command.Parameters.Item("inProjectID").Value = _
                    GroupProjects.Tables("GroupProjects").Rows(intIndex).Item( _
                    "ProjectID").ToString
                'Execute the stored procedure
                AddGroupProjects = ExecuteStoredProcedure()
            Next
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
                ExceptionErr.InnerException)
        End Try
    End Function

    Public Function DeleteGroupProjects(ByVal GroupID As Guid) As Boolean
        Try
            MyBase.SQL = "usp_DeleteGroupProjects"
            'Initialize the Command object
            MyBase.InitializeCommand()
            MyBase.Command.CommandType = CommandType.StoredProcedure
            'Add a Parameter to the Parameters collection
            MyBase.AddParameter("inGroupID", _
                OracleClient.OracleType.Char, 36, GroupID.ToString)
            'Execute the stored procedure
            DeleteGroupProjects = ExecuteStoredProcedure()
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
                ExceptionErr.InnerException)
        End Try
    End Function
#End Region
End Class