Блог

Можете ли вы подсказать мне, что делать для оптимизации моего кода. Как вы можете видеть на картинке, я отображаю все оценки учащихся по каждому предмету в текстовом поле, и всего их 51. текстовое поле.

В моей базе данных у меня есть sub_id от 1 до 51, и я повторяю один и тот же запрос 51 раз для sub_id от 1 до 51 для определенного учащегося. Вот что именно представляет собой мой код

 query = "SELECT grade " amp; _
       " FROM student_subject" amp; _
       " INNER JOIN subject_bsit ON subject_bsit.subject_id = student_subject.sub_id" amp; _
       " where student_subject.student_id='" amp; GetStudentNum amp; "' AND sub_id = 1 AND enrolled = 1 "

    cmd = New MySqlCommand(query, myconn)
    dr = cmd.ExecuteReader

    If dr.Read Then
        textbox_Sub1.Text = dr.Item("grade").ToString
        dr.Close()
    End If

    query = "SELECT grade " amp; _
       " FROM student_subject" amp; _
       " INNER JOIN subject_bsit ON subject_bsit.subject_id = student_subject.sub_id" amp; _
       " where student_subject.student_id='" amp; GetStudentNum amp; "' AND sub_id = 2 AND enrolled = 1 "

    cmd = New MySqlCommand(query, myconn)
    dr = cmd.ExecuteReader

    If dr.Read Then
        textbox_Sub2.Text = dr.Item("grade").ToString
        dr.Close()
    End If

    query = "SELECT grade " amp; _
      " FROM student_subject" amp; _
      " INNER JOIN subject_bsit ON subject_bsit.subject_id = student_subject.sub_id" amp; _
      " where student_subject.student_id='" amp; GetStudentNum amp; "' AND sub_id = 3 AND enrolled = 1 "

    cmd = New MySqlCommand(query, myconn)
    dr = cmd.ExecuteReader

    If dr.Read Then
        textbox_Sub3.Text = dr.Item("grade").ToString
        dr.Close()
    End If

    query = "SELECT grade " amp; _
      " FROM student_subject" amp; _
      " INNER JOIN subject_bsit ON subject_bsit.subject_id = student_subject.sub_id" amp; _
      " where student_subject.student_id='" amp; GetStudentNum amp; "' AND sub_id = 4 AND enrolled = 1 "

    cmd = New MySqlCommand(query, myconn)
    dr = cmd.ExecuteReader

    If dr.Read Then
        textbox_Sub4.Text = dr.Item("grade").ToString
        dr.Close()
    End If
 

РЕДАКТИРОВАТЬ ОБНОВИТЬ:

 '** DECLARTING ALL VARIABLES NEEDED
Public GetStudentNum As String
Dim connstring As String = "Data Source=localhost;Database=csais;User ID=root;Password=;"
Dim myconn As MySqlConnection = New MySqlConnection(connstring)
Dim cmd As MySqlCommand
Dim dr As MySqlDataReader
Dim query


'**START FORM LOAD
Private Sub flow1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    GetStudentNum = enrollStd.tempStudentNum
    myconn.Open()

    For i As Integer = 1 To 51
        query = "SELECT grade " amp; _
        " FROM student_subject" amp; _
        " INNER JOIN subject_bsit ON subject_bsit.subject_id = student_subject.sub_id" amp; _
        " where student_subject.student_id='" amp; GetStudentNum amp; "' AND sub_id = " amp; i.ToString() amp; " AND enrolled = 1 "

        cmd = New MySqlCommand(query, myconn)
        dr = cmd.ExecuteReader

        If dr.Read Then
            Dim tb As TextBox = CType(Me.Controls.Find("textbox_Sub" amp; i.ToString(), True)(0), TextBox)
            tb.Text = dr.Item("grade").ToString
            dr.Close()
        End If
    Next

    myconn.Close()
 

Попробуйте использовать цикл для этого:

 For i As Integer = 1 to 51
    query = "SELECT grade " amp; _
   " FROM student_subject" amp; _
   " INNER JOIN subject_bsit ON subject_bsit.subject_id = student_subject.sub_id" amp; _
   " where student_subject.student_id='" amp; GetStudentNum amp; "' AND sub_id = " amp; i.ToString() amp; " AND enrolled = 1 "

    cmd = New MySqlCommand(query, myconn)
    dr = cmd.ExecuteReader

    If dr.Read Then
        Dim tb As TextBox = CType(Me.Controls.Find("textbox_Sub" amp; i.ToString(), True)(0), TextBox)
        tb.Text = dr.Item("grade").ToString
        dr.Close()
    End If
Next