Imports System.Data
Imports System.Data.sqlclient

Public Class FacultyForm
    Private FacultyLabel(4) As Label        'Faculty table has 7 columns, but we only filled 5 columns

    Private Sub FacultyForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If LogInForm.sqlConnection.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 cmdString1 As String = "SELECT office, phone, college, title, email FROM Faculty "
        Dim cmdString2 As String = "WHERE name LIKE @facultyName"
        Dim cmdString As String = cmdString1 & cmdString2
        Dim paramFacultyName As New SqlParameter
        Dim FacultyTableAdapter As New SqlDataAdapter
        Dim sqlCommand As New SqlCommand
        Dim sqlDataReader As SqlDataReader
        Dim sqlDataTable As New DataTable

        paramFacultyName.ParameterName = "@facultyName"
        paramFacultyName.Value = ComboName.Text
        sqlCommand.Connection = LogInForm.sqlConnection
        sqlCommand.CommandType = CommandType.Text
        sqlCommand.CommandText = cmdString
        sqlCommand.Parameters.Add(paramFacultyName)
        Call ShowFaculty(ComboName.Text)

        If ComboMethod.Text = "TableAdapter Method" Then
            FacultyTableAdapter.SelectCommand = sqlCommand
            FacultyTableAdapter.Fill(sqlDataTable)
            If sqlDataTable.Rows.Count > 0 Then
                Call FillFacultyTable(sqlDataTable)
            Else
                MessageBox.Show("No matched faculty found!")
            End If
            sqlDataTable.Dispose()
            sqlDataTable = Nothing
            FacultyTableAdapter.Dispose()
            FacultyTableAdapter = Nothing
        Else
            sqlDataReader = sqlCommand.ExecuteReader
            If sqlDataReader.HasRows = True Then
                Call FillFacultyReader(sqlDataReader)
            Else
                MessageBox.Show("No matched faculty found!")
            End If
            sqlDataReader.Close()
            sqlDataReader = Nothing
        End If
        sqlCommand.Dispose()
        sqlCommand = Nothing
    End Sub

    Private Sub FillFacultyTable(ByVal FacultyTable As DataTable)
        Dim pos1 As Integer
        Dim column As DataColumn
        Dim row As DataRow

        For pos2 As Integer = 0 To 4         'Initialize the object array
            FacultyLabel(pos2) = New Label()
        Next pos2
        Call MapFacultyTable(FacultyLabel)
        For Each row In FacultyTable.Rows
            For Each column In FacultyTable.Columns
                FacultyLabel(pos1).Text = row(column)
                pos1 = pos1 + 1
            Next
        Next
    End Sub

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

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

    Private Sub MapFacultyTable(ByRef fLabel As Object)
        fLabel(0) = OfficeLabel     'The order must be identical
        fLabel(1) = PhoneLabel      'with the real order in the query string cmdString
        fLabel(2) = CollegeLabel
        fLabel(3) = TitleLabel
        fLabel(4) = EmailLabel
    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
                FacultyImage = ""
                MessageBox.Show("No match faculty found!")
        End Select
        PhotoBox.Image = System.Drawing.Image.FromFile(FacultyImage)
    End Sub
    Private Sub cmdBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdBack.Click
        Me.Close()
    End Sub
End Class