Imports System.Data
Imports System.Data.OracleClient

Partial Class Faculty
    Inherits System.Web.UI.Page
    Private FacultyTextBox(6) As TextBox        'Faculty table has 7 columns, we used all of them

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Application("oraConnection").State <> ConnectionState.Open Then
            Application("oraConnection").Open()
        End If
        If Not IsPostBack Then
            ComboName.Items.Add("Ying Bai")
            ComboName.Items.Add("Satish Bhalla")
            ComboName.Items.Add("Black Anderson")
            ComboName.Items.Add("Steve Johnson")
            ComboName.Items.Add("Jenney King")
            ComboName.Items.Add("Alice Brown")
            ComboName.Items.Add("Debby Angles")
            ComboName.Items.Add("Jeff Henry")
        End If
        If Application("FacultyName") <> String.Empty Then
            ComboName.Items.Add(Application("FacultyName"))
            Application("FacultyName") = String.Empty
        End If
    End Sub

    Protected Sub cmdSelect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSelect.Click
        Dim cmdString As String = "SELECT faculty_id, name, office, phone, college, title, email FROM Faculty " & _
                                  "WHERE name=:facultyName"
        Dim paramFacultyName As New OracleParameter
        Dim oraCommand As New OracleCommand
        Dim oraDataReader As OracleDataReader

        Application("oldFacultyName") = ComboName.Text      'reserve the old name for data updating
        paramFacultyName.ParameterName = "facultyName"
        paramFacultyName.Value = ComboName.Text
        oraCommand.Connection = Application("oraConnection")
        oraCommand.CommandType = CommandType.Text
        oraCommand.CommandText = cmdString
        oraCommand.Parameters.Add(paramFacultyName)
        Call ShowFaculty(ComboName.Text)
        oraDataReader = oraCommand.ExecuteReader
        If oraDataReader.HasRows = True Then
            Call FillFacultyReader(oraDataReader)
        Else
            Response.Write("<script>alert('No matched faculty found!')</script>")
        End If
        oraDataReader.Close()
        oraDataReader = Nothing
        oraCommand.Dispose()
        oraCommand = Nothing
    End Sub

    Private Sub FillFacultyReader(ByVal FacultyReader As OracleDataReader)
        Dim intIndex As Integer

        For intIndex = 0 To 6                       'Initialize the object array
            FacultyTextBox(intIndex) = New TextBox()
        Next intIndex
        Call MapFacultyTable(FacultyTextBox)
        While FacultyReader.Read()
            For intIndex = 0 To FacultyReader.FieldCount - 1
                FacultyTextBox(intIndex).Text = FacultyReader.Item(intIndex).ToString
            Next intIndex
        End While
    End Sub

    Private Sub MapFacultyTable(ByRef fText As Object)
        fText(0) = txtID            'The order must be identical
        fText(1) = txtName          'with the real order in the query string cmdString
        fText(2) = txtOffice
        fText(3) = txtPhone
        fText(4) = txtCollege
        fText(5) = txtTitle
        fText(6) = txtEmail
    End Sub

    Private Sub ShowFaculty(ByVal fName As String)
        Dim FacultyImage As String

        Select Case fName
            Case "Ying Bai"
                FacultyImage = "Bai.jpg"
            Case "Satish Bhalla"
                FacultyImage = "Satish.jpg"
            Case "Black Anderson"
                FacultyImage = "Anderson.jpg"
            Case "Steve Johnson"
                FacultyImage = "Johnson.jpg"
            Case "Jenney King"
                FacultyImage = "King.jpg"
            Case "Alice Brown"
                FacultyImage = "Brown.jpg"
            Case "Debby Angles"
                FacultyImage = "Angles.jpg"
            Case "Jeff Henry"
                FacultyImage = "Henry.jpg"
            Case Else
                If Application("FacultyImage") = String.Empty Then
                    FacultyImage = "Default.jpg"
                Else
                    FacultyImage = Application("FacultyImage")
                End If
        End Select
        PhotoBox.ImageUrl = FacultyImage
    End Sub

    Protected Sub cmdBack_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdBack.Click
        Response.Redirect("Selection.aspx")
    End Sub

    Protected Sub cmdInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdInsert.Click
        Response.Redirect("Insert.aspx")
    End Sub

    Protected Sub cmdUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
        Dim cmdString As String = "UPDATE Faculty SET name =: name, office =: office, phone =: phone, " & _
                                  "college =: college, title =: title, email =: email " & _
                                  "WHERE (name =: oldName)"
        Dim oraCommand As New OracleCommand
        Dim intUpdate As Integer

        txtID.Text = String.Empty               'clean the faculty_id textbox 
        If txtName.Text <> Application("oldFacultyName") Then
            ComboName.Items.Add(txtName.Text)
            ComboName.Items.Remove(Application("oldFacultyName"))
        End If
        oraCommand.Connection = Application("oraConnection")
        oraCommand.CommandType = CommandType.Text
        oraCommand.CommandText = cmdString
        UpdateParameters(oraCommand)
        intUpdate = oraCommand.ExecuteNonQuery()
        oraCommand.Dispose()
        oraCommand = Nothing
        If intUpdate = 0 Then
            Response.Write("<script>alert('The data updating is failed')</script>")
            Exit Sub
        End If
    End Sub

    Private Sub UpdateParameters(ByRef cmd As OracleCommand)
        cmd.Parameters.Add("name", OracleType.Char).Value = txtName.Text
        cmd.Parameters.Add("office", OracleType.Char).Value = txtOffice.Text
        cmd.Parameters.Add("phone", OracleType.Char).Value = txtPhone.Text
        cmd.Parameters.Add("college", OracleType.Char).Value = txtCollege.Text
        cmd.Parameters.Add("title", OracleType.Char).Value = txtTitle.Text
        cmd.Parameters.Add("email", OracleType.Char).Value = txtEmail.Text
        cmd.Parameters.Add("oldName", OracleType.Char).Value = Application("oldFacultyName")
    End Sub

    Protected Sub cmdDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        Dim cmdString As String = "DeleteFaculty"
        Dim oraCommand As New OracleCommand
        Dim intDelete As Integer

        oraCommand.Connection = Application("oraConnection")
        oraCommand.CommandType = CommandType.StoredProcedure
        oraCommand.CommandText = cmdString
        oraCommand.Parameters.Add("FacultyName", OracleType.Char).Value = ComboName.Text
        intDelete = oraCommand.ExecuteNonQuery()
        oraCommand.Dispose()
        oraCommand = Nothing
        If intDelete = 0 Then
            Response.Write("<script>alert('The data Deleting is failed')</script>")
            Exit Sub
        End If
        CleanFaculty()
    End Sub

    Private Sub CleanFaculty()
        txtID.Text = String.Empty
        txtName.Text = String.Empty
        txtTitle.Text = String.Empty
        txtOffice.Text = String.Empty
        txtPhone.Text = String.Empty
        txtCollege.Text = String.Empty
        txtEmail.Text = String.Empty
    End Sub
End Class
