Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports ASPNetPortal


Namespace ASPNetPortal

    '*********************************************************************
    '
    ' EventDB Class
    '
    ' Class that encapsulates all data logic necessary to add/query/delete
    ' events within the Portal database.
    '
    '*********************************************************************

    Public Class EventsDB

        '*********************************************************************
        '
        ' GetEvents Method
        '
        ' The GetEvents method returns a DataSet containing all of the
        ' events for a specific portal module from the events
        ' database.
        '
        ' NOTE: A DataSet is returned from this method to allow this method to support
        ' both desktop and mobile Web UI.
        '
        ' Other relevant sources:
        '     + GetEvents 存储过程
        '
        '*********************************************************************

        Public Function GetEvents(ByVal moduleId As Integer) As DataSet

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
            Dim myCommand As New SqlDataAdapter("GetEvents", myConnection)

            ' Mark the Command as a SPROC
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterModuleId As New SqlParameter("@ModuleId", SqlDbType.Int, 4)
            parameterModuleId.Value = moduleId
            myCommand.SelectCommand.Parameters.Add(parameterModuleId)

            ' Create and Fill the DataSet
            Dim myDataSet As New DataSet()
            myCommand.Fill(myDataSet)

            ' Return the DataSet
            Return myDataSet

        End Function


        '*********************************************************************
        '
        ' GetSingleEvent Method
        '
        ' The GetSingleEvent method returns a SqlDataReader containing details
        ' about a specific event from the events database.
        '
        ' Other relevant sources:
        '     + GetSingleEvent 存储过程
        '
        '*********************************************************************

        Public Function GetSingleEvent(ByVal itemId As Integer) As SqlDataReader

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
            Dim myCommand As New SqlCommand("GetSingleEvent", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterItemId As New SqlParameter("@ItemId", SqlDbType.Int, 4)
            parameterItemId.Value = itemId
            myCommand.Parameters.Add(parameterItemId)

            ' Execute the command
            myConnection.Open()
            Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

            ' Return the datareader 
            Return result

        End Function


        '*********************************************************************
        '
        ' DeleteEvent Method
        '
        ' The DeleteEvent method deletes a specified event from
        ' the events database.
        '
        ' Other relevant sources:
        '     + DeleteEvent 存储过程
        '
        '*********************************************************************

        Public Sub DeleteEvent(ByVal itemID As Integer)

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
            Dim myCommand As New SqlCommand("DeleteEvent", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterItemID As New SqlParameter("@ItemID", SqlDbType.Int, 4)
            parameterItemID.Value = itemID
            myCommand.Parameters.Add(parameterItemID)

            ' Open the database connection and execute SQL Command
            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

        End Sub


        '*********************************************************************
        '
        ' AddEvent Method
        '
        ' The AddEvent method adds a new event within the Events database table, 
        ' and returns the ItemID value as a result.
        '
        ' Other relevant sources:
        '     + AddEvent 存储过程
        '
        '*********************************************************************

        Public Function AddEvent(ByVal moduleId As Integer, ByVal itemId As Integer, ByVal userName As String, ByVal title As String, ByVal expireDate As DateTime, ByVal description As String, ByVal wherewhen As String) As Integer

            If userName.Length < 1 Then
                userName = "unknown"
            End If

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
            Dim myCommand As New SqlCommand("AddEvent", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterItemID As New SqlParameter("@ItemID", SqlDbType.Int, 4)
            parameterItemID.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterItemID)

            Dim parameterModuleID As New SqlParameter("@ModuleID", SqlDbType.Int, 4)
            parameterModuleID.Value = moduleId
            myCommand.Parameters.Add(parameterModuleID)

            Dim parameterUserName As New SqlParameter("@UserName", SqlDbType.NVarChar, 100)
            parameterUserName.Value = userName
            myCommand.Parameters.Add(parameterUserName)

            Dim parameterTitle As New SqlParameter("@Title", SqlDbType.NVarChar, 100)
            parameterTitle.Value = title
            myCommand.Parameters.Add(parameterTitle)

            Dim parameterWhereWhen As New SqlParameter("@WhereWhen", SqlDbType.NVarChar, 100)
            parameterWhereWhen.Value = wherewhen
            myCommand.Parameters.Add(parameterWhereWhen)

            Dim parameterExpireDate As New SqlParameter("@ExpireDate", SqlDbType.DateTime, 8)
            parameterExpireDate.Value = expireDate
            myCommand.Parameters.Add(parameterExpireDate)

            Dim parameterDescription As New SqlParameter("@Description", SqlDbType.NVarChar, 2000)
            parameterDescription.Value = description
            myCommand.Parameters.Add(parameterDescription)

            ' Open the database connection and execute SQL Command
            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            ' Return the new Event ItemID
            Return CInt(parameterItemID.Value)

        End Function


        '*********************************************************************
        '
        ' UpdateEvent Method
        '
        ' The UpdateEvent method updates the specified event within
        ' the Events database table.
        '
        ' Other relevant sources:
        '     + UpdateEvent 存储过程
        '
        '*********************************************************************

        Public Sub UpdateEvent(ByVal moduleId As Integer, ByVal itemId As Integer, ByVal userName As String, ByVal title As String, ByVal expireDate As DateTime, ByVal description As String, ByVal wherewhen As String)

            If userName.Length < 1 Then
                userName = "unknown"
            End If

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
            Dim myCommand As New SqlCommand("UpdateEvent", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterItemID As New SqlParameter("@ItemID", SqlDbType.Int, 4)
            parameterItemID.Value = itemId
            myCommand.Parameters.Add(parameterItemID)

            Dim parameterUserName As New SqlParameter("@UserName", SqlDbType.NVarChar, 100)
            parameterUserName.Value = userName
            myCommand.Parameters.Add(parameterUserName)

            Dim parameterTitle As New SqlParameter("@Title", SqlDbType.NVarChar, 100)
            parameterTitle.Value = title
            myCommand.Parameters.Add(parameterTitle)

            Dim parameterWhereWhen As New SqlParameter("@WhereWhen", SqlDbType.NVarChar, 100)
            parameterWhereWhen.Value = wherewhen
            myCommand.Parameters.Add(parameterWhereWhen)

            Dim parameterExpireDate As New SqlParameter("@ExpireDate", SqlDbType.DateTime, 8)
            parameterExpireDate.Value = expireDate
            myCommand.Parameters.Add(parameterExpireDate)

            Dim parameterDescription As New SqlParameter("@Description", SqlDbType.NVarChar, 2000)
            parameterDescription.Value = description
            myCommand.Parameters.Add(parameterDescription)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

        End Sub

    End Class

End Namespace