wdausers.vb

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

Public Class WDAUsers
    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 User Functions "
    Public Function AddUser(ByVal User As DataSet) As Boolean
        Try
            MyBase.SQL = "usp_InsertUser"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("inUserID", _
                OracleClient.OracleType.Char, 36, _
                User.Tables("User").Rows(0).Item("UserID").ToString)
            MyBase.AddParameter("inLoginName", _
                OracleClient.OracleType.VarChar, 15, _
                User.Tables("User").Rows(0).Item("LoginName"))
            MyBase.AddParameter("inPassword", _
                OracleClient.OracleType.VarChar, 30, _
                User.Tables("User").Rows(0).Item("Password"))
            MyBase.AddParameter("inFirstName", _
                OracleClient.OracleType.VarChar, 30, _
                User.Tables("User").Rows(0).Item("FirstName"))
            MyBase.AddParameter("inLastName", _
                OracleClient.OracleType.VarChar, 30, _
                User.Tables("User").Rows(0).Item("LastName"))
            MyBase.AddParameter("inEmail", _
                OracleClient.OracleType.VarChar, 50, _
                User.Tables("User").Rows(0).Item("Email"))
            MyBase.AddParameter("inPhone", _
                OracleClient.OracleType.VarChar, 20, _
                User.Tables("User").Rows(0).Item("Phone"))
            MyBase.AddParameter("inStatus", _
                OracleClient.OracleType.Number, 1, _
                User.Tables("User").Rows(0).Item("Status"))
            MyBase.AddParameter("inGroupID", _
                OracleClient.OracleType.Char, 36, _
                User.Tables("User").Rows(0).Item("GroupID").ToString)
            MyBase.AddParameter("inRoleID", _
                OracleClient.OracleType.Char, 36, _
                User.Tables("User").Rows(0).Item("RoleID").ToString)
            MyBase.AddParameter("inManagerID", _
                OracleClient.OracleType.Char, 36, _
                User.Tables("User").Rows(0).Item("ManagerID").ToString)
            'Execute the stored procedure
            AddUser = ExecuteStoredProcedure()
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function GetUsers() As DataSet
        Try
            GetUsers = New DataSet
            MyBase.SQL = "UsersPackage.usp_SelectUsers"
            '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(GetUsers, "Users")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

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

    Public Function GetManagers() As DataSet
        Try
            GetManagers = New DataSet
            MyBase.SQL = "ManagersPackage.usp_SelectManagers"
            '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(GetManagers, "Managers")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

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

    Public Function ValidateLogin(ByVal LoginName As String, _
        ByVal Password As String) As DataSet

        Try
            'Initialize a new instance of the Crypto class
            ValidateLogin = New DataSet
            MyBase.SQL = "ValidateLoginPackage.usp_ValidateLogin"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("inLoginName", OracleClient.OracleType.VarChar, _
                15, LoginName)
            MyBase.AddParameter("inPassword", OracleClient.OracleType.VarChar, _
                30, Password)
            MyBase.AddParameter("results_cursor", _
                OracleClient.OracleType.Cursor, ParameterDirection.Output)
            'Fill the DataSet
            MyBase.FillDataSet(ValidateLogin, "User")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function UpdateUser(ByVal User As DataSet) As Boolean
        Try
            MyBase.SQL = "usp_UpdateUser"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("inUserID", _
                OracleClient.OracleType.Char, 36, _
                User.Tables("User").Rows(0).Item("UserID").ToString)
            MyBase.AddParameter("inLoginName", _
                OracleClient.OracleType.VarChar, 15, _
                User.Tables("User").Rows(0).Item("LoginName"))
            MyBase.AddParameter("inPassword", _
                OracleClient.OracleType.VarChar, 30, _
                User.Tables("User").Rows(0).Item("Password"))
            MyBase.AddParameter("inFirstName", _
                OracleClient.OracleType.VarChar, 30, _
                User.Tables("User").Rows(0).Item("FirstName"))
            MyBase.AddParameter("inLastName", _
                OracleClient.OracleType.VarChar, 30, _
                User.Tables("User").Rows(0).Item("LastName"))
            MyBase.AddParameter("inEmail", _
                OracleClient.OracleType.VarChar, 50, _
                User.Tables("User").Rows(0).Item("Email"))
            MyBase.AddParameter("inPhone", _
                OracleClient.OracleType.VarChar, 20, _
                User.Tables("User").Rows(0).Item("Phone"))
            MyBase.AddParameter("inStatus", _
                OracleClient.OracleType.Number, 1, _
                User.Tables("User").Rows(0).Item("Status"))
            MyBase.AddParameter("inGroupID", _
                OracleClient.OracleType.Char, 36, _
                User.Tables("User").Rows(0).Item("GroupID").ToString)
            MyBase.AddParameter("inRoleID", _
                OracleClient.OracleType.Char, 36, _
                User.Tables("User").Rows(0).Item("RoleID").ToString)
            MyBase.AddParameter("inManagerID", _
                OracleClient.OracleType.Char, 36, _
                User.Tables("User").Rows(0).Item("ManagerID").ToString)
            'Execute the stored procedure
            UpdateUser = ExecuteStoredProcedure()
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

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