wdatimesheets.vb

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

Public Class WDATimeSheets
    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 Timesheet Functions "
    Public Function AddTimeSheet(ByVal TimeSheet As DataSet) As Boolean
        Try
            'Clear previous Command just in case it has been set
            MyBase.Command = Nothing
            MyBase.SQL = "usp_InsertTimeSheet"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@TimeSheetID", _
                SqlDbType.UniqueIdentifier, 16, _
                TimeSheet.Tables("TimeSheet").Rows(0).Item("TimeSheetID"))
            MyBase.AddParameter("@UserID", _
                SqlDbType.UniqueIdentifier, 16, _
                TimeSheet.Tables("TimeSheet").Rows(0).Item("UserID"))
            MyBase.AddParameter("@WeekEndingDate", _
                SqlDbType.DateTime, 8, _
                TimeSheet.Tables("TimeSheet").Rows(0).Item("WeekEndingDate"))
            'Execute the stored procedure
            AddTimeSheet = ExecuteStoredProcedure()
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function GetTimeSheet(ByVal UserID As Guid, _
        ByVal WeekEndingDate As Date) As DataSet
        Try
            GetTimeSheet = New DataSet
            MyBase.SQL = "usp_SelectTimeSheet"
            'Clear previous Command just in case it has been set
            MyBase.Command = Nothing
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@UserID", _
                SqlDbType.UniqueIdentifier, 16, UserID)
            MyBase.AddParameter("@WeekEndingDate", _
                SqlDbType.DateTime, 8, WeekEndingDate)
            'Fill the DataSet
            MyBase.FillDataSet(GetTimeSheet, "TimeSheet")
            'Check the DataSet for 0 rows and create a new timesheet
            'if necessary
            If GetTimeSheet.Tables("TimeSheet").Rows.Count = 0 Then
                'Create a new TimeSheet DataSet
                Dim objDataSet As New DataSet
                'Create a DataTable
                Dim objDataTable As DataTable = objDataSet.Tables.Add("TimeSheet")
                'Add the DataColumns to the table
                objDataTable.Columns.Add( _
                    "TimeSheetID", Type.GetType("System.Guid"))
                objDataTable.Columns.Add( _
                    "UserID", Type.GetType("System.Guid"))
                objDataTable.Columns.Add( _
                    "WeekEndingDate", Type.GetType("System.DateTime"))
                'Initialize a datarow object from the TimeSheet DataSet
                Dim objDataRow As DataRow = objDataSet.Tables("TimeSheet").NewRow
                'Set the values in the DataRow
                objDataRow.Item("TimeSheetID") = Guid.NewGuid
                objDataRow.Item("UserID") = UserID
                objDataRow.Item("WeekEndingDate") = WeekEndingDate
                'Add the DataRow to the DataSet
                objDataSet.Tables("TimeSheet").Rows.Add(objDataRow)
                'Add the TimeSheet to the database
                If Not AddTimeSheet(objDataSet) Then
                    Throw New Exception("Insert TimeSheet Failed")
                End If
                'Now perform a recursive call to this procedure
                Return GetTimeSheet(UserID, WeekEndingDate)
            End If
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function SaveTimeSheet(ByVal TimeSheet As DataSet) As Boolean
        Try
            MyBase.SQL = "usp_UpdateTimeSheetItem"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@TimeSheetItemID", _
             SqlDbType.UniqueIdentifier, 16, Nothing)
            MyBase.AddParameter("@Hours", _
             SqlDbType.TinyInt, 1, Nothing)
            'Process all rows in the table
            For intIndex As Integer = 0 To _
                TimeSheet.Tables("TimeSheet").Rows.Count - 2
                'Update Monday's Hours
                MyBase.Command.Parameters.Item("@TimeSheetItemID").Value = _
                    TimeSheet.Tables("TimeSheet").Rows(intIndex).Item( _
                    "MondayTimeSheetItemID")
                MyBase.Command.Parameters.Item("@Hours").Value = _
                    TimeSheet.Tables("TimeSheet").Rows(intIndex).Item( _
                    "MondayHours")
                SaveTimeSheet = ExecuteStoredProcedure()
                'Update Tuesday's Hours
                MyBase.Command.Parameters.Item("@TimeSheetItemID").Value = _
                    TimeSheet.Tables("TimeSheet").Rows(intIndex).Item( _
                    "TuesdayTimeSheetItemID")
                MyBase.Command.Parameters.Item("@Hours").Value = _
                    TimeSheet.Tables("TimeSheet").Rows(intIndex).Item( _
                    "TuesdayHours")
                SaveTimeSheet = ExecuteStoredProcedure()
                'Update Wednesday's Hours
                MyBase.Command.Parameters.Item("@TimeSheetItemID").Value = _
                    TimeSheet.Tables("TimeSheet").Rows(intIndex).Item( _
                    "WednesdayTimeSheetItemID")
                MyBase.Command.Parameters.Item("@Hours").Value = _
                    TimeSheet.Tables("TimeSheet").Rows(intIndex).Item( _
                    "WednesdayHours")
                SaveTimeSheet = ExecuteStoredProcedure()
                'Update Thursday's Hours
                MyBase.Command.Parameters.Item("@TimeSheetItemID").Value = _
                    TimeSheet.Tables("TimeSheet").Rows(intIndex).Item( _
                    "ThursdayTimeSheetItemID")
                MyBase.Command.Parameters.Item("@Hours").Value = _
                    TimeSheet.Tables("TimeSheet").Rows(intIndex).Item( _
                    "ThursdayHours")
                SaveTimeSheet = ExecuteStoredProcedure()
                'Update Friday's Hours
                MyBase.Command.Parameters.Item("@TimeSheetItemID").Value = _
                    TimeSheet.Tables("TimeSheet").Rows(intIndex).Item( _
                    "FridayTimeSheetItemID")
                MyBase.Command.Parameters.Item("@Hours").Value = _
                    TimeSheet.Tables("TimeSheet").Rows(intIndex).Item( _
                    "FridayHours")
                SaveTimeSheet = ExecuteStoredProcedure()
            Next
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function SubmitTimeSheet(ByVal TimeSheetID As Guid) As Boolean
        Try
            MyBase.SQL = "usp_SubmitTimeSheet"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@TimeSheetID", _
                SqlDbType.UniqueIdentifier, 16, TimeSheetID)
            'Execute the stored procedure
            SubmitTimeSheet = ExecuteStoredProcedure()
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function ApproveTimeSheet(ByVal TimeSheetID As Guid, _
        ByVal ManagerID As Guid) As Boolean
        Try
            MyBase.SQL = "usp_ApproveTimeSheet"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@TimeSheetID", _
                SqlDbType.UniqueIdentifier, 16, TimeSheetID)
            MyBase.AddParameter("@ManagerID", _
                SqlDbType.UniqueIdentifier, 16, ManagerID)
            'Execute the stored procedure
            ApproveTimeSheet = ExecuteStoredProcedure()
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function GetManagerName(ByVal ManagerID As Guid) As String
        Try
            MyBase.SQL = "usp_SelectManagerName"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@ManagerID", _
             SqlDbType.UniqueIdentifier, 16, ManagerID)
            MyBase.AddParameter("@ManagerName", _
             SqlDbType.VarChar, 61, Nothing)
            'Change the default direction for the @ManagerName parameter
            MyBase.Command.Parameters("@ManagerName").Direction = _
                ParameterDirection.Output
            'Execute the stored procedure
            MyBase.ExecuteStoredProcedure()
            'Retrieve the output
            GetManagerName = MyBase.Command.Parameters("@ManagerName").Value
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function TimeSheetsDue(ByVal ManagerID As Guid, _
        ByVal WeekEndingDate As Date) As DataSet
        Try
            TimeSheetsDue = New DataSet
            MyBase.SQL = "usp_SelectTimeSheetsDue"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@ManagerID", _
             SqlDbType.UniqueIdentifier, 16, ManagerID)
            MyBase.AddParameter("@WeekEndingDate", _
             SqlDbType.DateTime, 8, WeekEndingDate)
            'Fill the DataSet
            MyBase.FillDataSet(TimeSheetsDue, "TimeSheets")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function TimeSheetsSubmitted(ByVal ManagerID As Guid, _
        ByVal WeekEndingDate As Date) As DataSet
        Try
            TimeSheetsSubmitted = New DataSet
            MyBase.SQL = "usp_SelectTimeSheetsSubmitted"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@ManagerID", _
             SqlDbType.UniqueIdentifier, 16, ManagerID)
            MyBase.AddParameter("@WeekEndingDate", _
             SqlDbType.DateTime, 8, WeekEndingDate)
            'Fill the DataSet
            MyBase.FillDataSet(TimeSheetsSubmitted, "TimeSheets")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function TimeSheetsMTD(ByVal ManagerID As Guid) As DataSet
        Try
            TimeSheetsMTD = New DataSet
            MyBase.SQL = "usp_SelectTimeSheetsMTD"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@ManagerID", _
             SqlDbType.UniqueIdentifier, 16, ManagerID)
            'Fill the DataSet
            MyBase.FillDataSet(TimeSheetsMTD, "TimeSheets")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function TimeSheetsQTD(ByVal ManagerID As Guid) As DataSet
        Try
            TimeSheetsQTD = New DataSet
            MyBase.SQL = "usp_SelectTimeSheetsQTD"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@ManagerID", _
             SqlDbType.UniqueIdentifier, 16, ManagerID)
            'Fill the DataSet
            MyBase.FillDataSet(TimeSheetsQTD, "TimeSheets")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function

    Public Function TimeSheetsYTD(ByVal ManagerID As Guid) As DataSet
        Try
            TimeSheetsYTD = New DataSet
            MyBase.SQL = "usp_SelectTimeSheetsYTD"
            'Initialize the Command object
            MyBase.InitializeCommand()
            'Add the Parameters to the Parameters collection
            MyBase.AddParameter("@ManagerID", _
             SqlDbType.UniqueIdentifier, 16, ManagerID)
            'Fill the DataSet
            MyBase.FillDataSet(TimeSheetsYTD, "TimeSheets")
        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, _
            ExceptionErr.InnerException)
        End Try
    End Function
#End Region
End Class