VB.Net:在 Windows 窗体上使用 SQL Queires、for 循环和标签创建 StackRanking

Question

我有几个关于这段代码的问题。有没有更好的方法来编写这些 SQL 语句，以便我可能只使用一个查询？另一个问题是关于

"Controls.Item（"LblStckRnk" & i + 1）.文本（） = 读者（"员工"）"

部分代码。我想做的是根据查询为每个"LblStckRnkXX"标签提供一个唯一的人名，但正在发生的事情是我得到一个名字 10 次。

任何人都可以查看此代码（我知道我可以进行代码审查，但我已经在这里）并帮助我吗？这是我项目的最后一部分，在工作中，我已经完成了。

将查询字符串（10） 作为字符串

            queryString(0) = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
            queryString(1) = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
            queryString(2) = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk2.Text & "' GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
            queryString(3) = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk2.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk3.Text & "' GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
            queryString(4) = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk2.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk3.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk4.Text & "' GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
            queryString(5) = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk2.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk3.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk4.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk5.Text & "' GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
            queryString(6) = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk2.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk3.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk4.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk5.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk6.Text & "' GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
            queryString(7) = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk2.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk3.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk4.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk5.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk6.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk7.Text & "' GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
            queryString(8) = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk2.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk3.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk4.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk5.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk6.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk7.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk8.Text & "' GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
            queryString(9) = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk2.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk3.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk4.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk5.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk6.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk7.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk8.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk9.Text & "' GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
            Using connection As New SqlConnection(SQLConnectionStr)
                For i As Integer = 0 To 9
                    Dim command As New SqlCommand(queryString(i), connection)
                    If connection.State = ConnectionState.Open Then
                    Else
                        connection.Open()
                    End If
                    Dim reader As SqlDataReader = command.ExecuteReader()
                    While reader.Read()
                        Controls.Item("LblStckRnk" & i + 1).Text() = reader("EMPLOYEE")
                    End While
                    reader.Close()
                Next
            End Using
            '/////////////////////////////////////////////////////////////////////////////////////////////////////

Answer 1

下面的重写应该可以得到你要找的东西：

    Const MAX_CONTROLS As Integer = 10
    ' Clear the existing controls
    For i = 0 To MAX_CONTROLS - 1
        Controls.Item("LblStckRnk" & i).Text = ""
    Next
    Dim queryString As String
    queryString = "SELECT EMPLOYEE_NAME AS EMPLOYEE, AVG(EFFICIENCY_YIELD) AS YIELD FROM dbo.APE_BUSDRIVER_MAIN WHERE YEAR_TIME = " & cbYear.Text & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""
    Using connection As New SqlConnection(SQLConnectionStr)
        Using command As New SqlCommand(queryString, connection)
            connection.Open()
            Using reader As SqlDataReader = command.ExecuteReader()
                Dim i As Integer = 0
                While reader.Read() AndAlso i < MAX_CONTROLS
                    Controls.Item("LblStckRnk" & i).Text = reader("EMPLOYEE")
                    i += 1
                End While
                reader.Close()
            End Using
        End Using
    End Using