mysql - 将文本分配给 Reader.GetString() 值？

Question

我有以下代码:

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
    MySqlConn = New MySqlConnection
    MySqlConn.ConnectionString = "server=localhost;userid=root;password=root;database=test"
    Dim READER As MySqlDataReader
    Try
        MySqlConn.Open()
        Dim Query As String
        Query = "select * from test.boxinformation where Box_SN='" & ComboBox1.Text & "'"
        COMMAND = New MySqlCommand(Query, MySqlConn)
        READER = COMMAND.ExecuteReader
        ComboBox2.Items.Clear()
        While READER.Read
            Dim CP = READER.GetString("CP_IP")
            Dim PC = READER.GetString("PC_IP")
            Dim M1 = READER.GetString("M1_IP")
            Dim M2 = READER.GetString("M2_IP")
            ComboBox2.Items.Add(CP)
            ComboBox2.Items.Add(PC)
            ComboBox2.Items.Add(M1)
            ComboBox2.Items.Add(M2)
        End While

        MySqlConn.Close()
    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        MySqlConn.Dispose()
    End Try
End Sub

基本上，发生的情况是我有一个 SQL 数据库，其中充满了特定于某个组件的 IP 地址。因此，当用户选择 ComboBox1 中的组件时，特定于该组件的 IP 地址将填充到 ComboBox2 中。

我的数据库设置在一个包含 5 列的表中。 ComboBox1 正在从名为“Box_SN”的列中提取数据，ComboBox2 正在从名为“CP_IP”、“PC_IP”、“M1_IP”和“M2_IP”的列中提取数据。当用户在 ComboBox1 中进行选择时，与 ComboBox1 中所选组件关联的 IP 地址将显示在 ComboBox2 中。因此，现在 ComboBox2 中填充了 4 个 IP 地址，这些 IP 地址特定于 ComboBox1 中选定的框。所有这一切都在按其应有的方式进行。

但我不想在 ComboBox2 中显示 IP 地址，而是想显示文本。因此，对于“CP_IP”，我希望它显示“组件 A”，而不是显示其 IP 地址，而对于“PC_IP”，我希望它显示“组件 B”

所以我想我想说的是，我想将通过数据库引入的 IP 地址分配给文本，而不仅仅是正在读入的 IP。

如果有人能帮助我，我将非常感激。

Answer 1

在网络上，html select 元素具有包含内容和值的选项。在 Windows 窗体中，ComboBox 元素只有对象。仅使用字符串对象是很常见的。

但是。

您可以在其中放置任何类型的对象，只要 .ToString() 方法能够生成有意义的内容。例如:

Public Class ComboItem(Of T)
    Public Property Name As String
    Public Property Value As T

    Public Sub New() 
    End Sub

    Public Sub New(name As String, value As T) 
        Me.Name = name 
        Me.Value = value
    End Sub

    Public Overrides Function ToString() As String {
        Return Name
    End Function
End Class

然后，还花时间修复 SQL 注入(inject)问题，您的原始方法如下所示:

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

    Dim Query As String = "select * from test.boxinformation where Box_SN= @BoxSN ;"

    Using MySqlConn As New MySqlConnection("server=localhost;userid=NOTROOT;password=root;database=test"), _
          COMMAND As New MySqlCommand(Query, MySqlConn)

        COMMAND.Parameters.Add("@BoxSN", MySqlDbType.VarChar, 15).Value = ComboBox1.Text
        MySqlConn.Open()

        Using READER As MySqlDataReader = COMMAND.ExecuteReader()

            While READER.Read
                ComboBox2.Items.Add(New ComboItem(Of String)("Component A", READER.GetString("CP_IP")))
                ComboBox2.Items.Add(New ComboItem(Of String)("Component B", READER.GetString("PC_IP")))
                'Guessing at the names here, since they aren't in the question
                ComboBox2.Items.Add(New ComboItem(Of String)("Machine 1", READER.GetString("M1_IP")))
                ComboBox2.Items.Add(New ComboItem(Of String)("Machine 2", READER.GetString("M2_IP")))
            End While
            READER.Close()
        End Using
    End Using
End Sub

现在，当用户在组合框二中进行选择时，他们可以根据“友好”文本做出选择，但您仍然可以访问所需的 IP 值。

最后，最好将所有数据访问移至与 UI 分开的位置，如下所示:

Public Module Data
    Private Const ConnectinString As String = "server=localhost;userid=NOTROOT;password=root;database=test"

    Public Function GetComponentIPs(BoxSN As String) As String()
        Dim Query As String = "select * from test.boxinformation where Box_SN= @BoxSN ;"

        Using MySqlConn As New MySqlConnection(ConnectionString), _
              COMMAND As New MySqlCommand(Query, MySqlConn)

            COMMAND.Parameters.Add("@BoxSN", MySqlDbType.VarChar, 15).Value = BoxSN
            MySqlConn.Open()

            Dim result(3) As String
            Using READER As MySqlDataReader = COMMAND.ExecuteReader()
                While READER.Read
                    'You could also do this with Tuples, or make another custom object (class) to hold this data to keep things strongly-typed

                    result(0) = READER.GetString("CP_IP")
                    result(1) = READER.GetString("PC_IP")
                    result(2) = READER.GetString("M1_IP")
                    result(3) = READER.GetString("M2_IP")
                    Return result
                End While
           End Using
        End Using
        Return result
    End Function
End Module

然后从原始方法中调用它，如下所示:

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
    Try
        Dim IPs As String() = Data.GetComponentIPs(ComboBox1.Text)

        ComboBox2.Items.Add(New ComboItem(Of String)("Component A", IPs(0)))
        ComboBox2.Items.Add(New ComboItem(Of String)("Component B", IPs(1)))
        'Guessing at the names here, since they aren't in the question
        ComboBox2.Items.Add(New ComboItem(Of String)("Machine 1", IPs(2)))
        ComboBox2.Items.Add(New ComboItem(Of String)("Machine 2", IPs(3)))
    Catch ex As MySqlException
       MessageBox.Show(ex.Message)
    End Try
End Sub