Imports System.Data
Imports System.Data.OracleClient
Public Class CourseForm
    Private CourseTextBox(4) As TextBox        'we only query 5 columns from the Course table
    Private CourseID() As String               'create a dynamic CourseID string array

    Private Sub CourseForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If LogInForm.oraConnection.State <> ConnectionState.Open Then
            MessageBox.Show("Database has not been opened!")
            Exit Sub
        End If
        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")
        ComboName.SelectedIndex = 0

        ComboMethod.Items.Add("TableAdapter Method")
        ComboMethod.Items.Add("DataReader Method")
        ComboMethod.SelectedIndex = 0
    End Sub

    Private Sub cmdSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelect.Click
        Dim cmdString As String = "Faculty_Course.SelectFacultyCourse"
        Dim paramFacultyName As New OracleParameter
        Dim paramFacultyCourse As New OracleParameter
        Dim CourseTableAdapter As New OracleDataAdapter
        Dim oraCommand As New OracleCommand
        Dim oraDataReader As OracleDataReader
        Dim oraDataTable As New DataTable

        paramFacultyName.ParameterName = "FacultyName"
        paramFacultyName.OracleType = OracleType.VarChar
        paramFacultyName.Value = ComboName.Text
        paramFacultyCourse.ParameterName = "FacultyCourse"
        paramFacultyCourse.OracleType = OracleType.Cursor
        paramFacultyCourse.Direction = ParameterDirection.Output
        oraCommand.Connection = LogInForm.oraConnection
        oraCommand.CommandType = CommandType.StoredProcedure
        oraCommand.CommandText = cmdString
        oraCommand.Parameters.Add(paramFacultyName)
        oraCommand.Parameters.Add(paramFacultyCourse)

        If ComboMethod.Text = "TableAdapter Method" Then
            CourseTableAdapter.SelectCommand = oraCommand
            CourseTableAdapter.Fill(oraDataTable)
            If oraDataTable.Rows.Count > 0 Then
                Call FillCourseTable(oraDataTable)
            Else
                MessageBox.Show("No matched course found!")
            End If
            oraDataTable.Dispose()
            oraDataTable = Nothing
            CourseTableAdapter.Dispose()
            CourseTableAdapter = Nothing
        Else
            oraDataReader = oraCommand.ExecuteReader
            If oraDataReader.HasRows = True Then
                Call FillCourseReader(oraDataReader)
            Else
                MessageBox.Show("No matched course found!")
            End If
            oraDataReader.Close()
            oraDataReader = Nothing
        End If
        oraCommand.Dispose()
        oraCommand = Nothing
        CourseList.SelectedIndex = 0
    End Sub

    Private Sub FillCourseTable(ByVal CourseTable As DataTable)
        Dim row As DataRow
        Dim pos As Integer
        ReDim CourseID(CourseTable.Rows.Count)   'redefine the CourseID array with the 
        CourseList.Items.Clear()                 'definite demission
        For Each row In CourseTable.Rows         'in the Course table
            CourseList.Items.Add(row(1))         'the 2nd column is course name
            CourseID(pos) = row(0)               ''the 1st column is course_id 
            pos = pos + 1
        Next
    End Sub

    Private Sub FillCourseReader(ByVal CourseReader As OracleDataReader)
        Dim strCourse As String
        Dim pos As Integer
        ReDim CourseID(9)      'redefine the CourseID array with the definite demission
        CourseList.Items.Clear()
        While CourseReader.Read()                   'in the Course table
            strCourse = CourseReader.GetString(1)   'the 2nd column is course name
            CourseList.Items.Add(strCourse)         'the 1st column is course_id
            CourseID(pos) = Convert.ToString(CourseReader.GetString(0))
            pos = pos + 1
        End While
    End Sub

    Private Sub cmdBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdBack.Click
        Me.Close()
    End Sub

    Private Sub CourseList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CourseList.SelectedIndexChanged
        Dim cmdString1 As String = "SELECT course_id, credit, classroom, schedule, enrollment FROM Course "
        Dim cmdString2 As String = "WHERE course_id =:courseid"
        Dim cmdString As String = cmdString1 & cmdString2
        Dim CourseTableAdapter As New OracleDataAdapter
        Dim oraCommand As New OracleCommand
        Dim oraDataReader As OracleDataReader
        Dim oraDataTable As New DataTable

        oraCommand.Connection = LogInForm.oraConnection
        oraCommand.CommandType = CommandType.Text
        oraCommand.CommandText = cmdString
        oraCommand.Parameters.Add("courseid", OracleType.Char).Value = CourseID(CourseList.SelectedIndex)

        If ComboMethod.Text = "TableAdapter Method" Then
            CourseTableAdapter.SelectCommand = oraCommand
            CourseTableAdapter.Fill(oraDataTable)
            If oraDataTable.Rows.Count > 0 Then
                Call FillCourseTextBox(oraDataTable)
            Else
                MessageBox.Show("No matched course information found!")
            End If
            oraDataTable.Dispose()
            oraDataTable = Nothing
            CourseTableAdapter.Dispose()
            CourseTableAdapter = Nothing
        Else
            oraDataReader = oraCommand.ExecuteReader
            If oraDataReader.HasRows = True Then
                Call FillCourseReaderTextBox(oraDataReader)
            Else
                MessageBox.Show("No matched course information found!")
            End If
            oraDataReader.Close()
            oraDataReader = Nothing
        End If
        oraCommand.Dispose()
        oraCommand = Nothing
    End Sub

    Private Sub FillCourseTextBox(ByVal CourseTable As DataTable)
        Dim pos1 As Integer
        Dim row As DataRow
        Dim column As DataColumn

        For pos2 As Integer = 0 To 4                'Initialize the object array
            CourseTextBox(pos2) = New TextBox
        Next pos2
        Call MapCourseTable(CourseTextBox)
        For Each row In CourseTable.Rows
            For Each column In CourseTable.Columns
                CourseTextBox(pos1).Text = row(column)
                pos1 = pos1 + 1
            Next
        Next
    End Sub

    Private Sub FillCourseReaderTextBox(ByVal CourseReader As OracleDataReader)
        Dim intIndex As Integer

        For intIndex = 0 To 4                       'Initialize the object array
            CourseTextBox(intIndex) = New TextBox
        Next intIndex
        Call MapCourseTable(CourseTextBox)
        While CourseReader.Read()
            For intIndex = 0 To CourseReader.FieldCount - 1
                CourseTextBox(intIndex).Text = CourseReader.Item(intIndex).ToString
            Next intIndex
        End While
    End Sub

    Private Sub MapCourseTable(ByRef fCourse As Object)
        fCourse(0) = txtID          'The order must be identical
        fCourse(1) = txtCredits     'with the real order in the query string
        fCourse(2) = txtClassRoom
        fCourse(3) = txtSchedule
        fCourse(4) = txtEnroll
    End Sub

    Private Sub cmdInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdInsert.Click
        Dim cmdString As String = "Faculty_Package.SelectFacultyID"
        Dim paramFacultyName As New OracleParameter
        Dim paramFacultyID As New OracleParameter
        Dim FacultyTableAdapter As New OracleDataAdapter
        Dim oraCommand As New OracleCommand
        Dim oraDataTable As New DataTable

        paramFacultyName.ParameterName = "FacultyName"
        paramFacultyName.OracleType = OracleType.VarChar
        paramFacultyName.Value = ComboName.Text
        paramFacultyID.ParameterName = "FacultyID"
        paramFacultyID.OracleType = OracleType.Cursor
        paramFacultyID.Direction = ParameterDirection.Output
        oraCommand.Connection = LogInForm.oraConnection
        oraCommand.CommandType = CommandType.StoredProcedure
        oraCommand.CommandText = cmdString
        oraCommand.Parameters.Add(paramFacultyName)
        oraCommand.Parameters.Add(paramFacultyID)

        FacultyTableAdapter.SelectCommand = oraCommand
        FacultyTableAdapter.Fill(oraDataTable)
        If oraDataTable.Rows.Count > 0 Then
            Call FillCourseTable(oraDataTable)
        Else
            MessageBox.Show("No matched course found!")
        End If
        oraDataTable.Dispose()
        oraDataTable = Nothing
        FacultyTableAdapter.Dispose()
        FacultyTableAdapter = Nothing
    End Sub
End Class