将mysql结果保存到datatable

Question

我刚开始使用mysql查询的参数，担心找不到如何将结果保存到数据表中。我希望将此select查询的结果保存到datatable "table“。以前我会做的

adptr = New MySqlDataAdapter("SELECT * from users WHERE uname='" & MySqlHelper.EscapeString(UsernameTextBox.Text) & "' and pword=md5('" & MySqlHelper.EscapeString(PasswordTextBox.Text) & "')", con)
    Try
        adptr.Fill(table)
    Catch err As Exception
        Dim strError As String = "Exception: & err.ToString()"
    End Try

现在我有了

    Public table As New DataTable
    Dim cb As New MySqlConnectionStringBuilder
    cb.Database = dbfile
    cb.Server = dbserver
    cb.UserID = dbuser
    cb.Password = dbpassw

    Using cnn As New MySqlConnection(cb.ConnectionString)
        Using cmd As New MySqlCommand("SELECT * from users WHERE uname=&uname and pword=md5(&pword)", cnn)
            cmd.Parameters.AddWithValue("@uname", UsernameTextBox.Text)
            cmd.Parameters.AddWithValue("@pword", PasswordTextBox.Text)

            Try
                cnn.Open()
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Using
    End Using

如何将结果保存到表中？

Answer 1

使用命令初始化适配器，然后像以前一样工作

Using cnn As New MySqlConnection(cb.ConnectionString)
    Using cmd As New MySqlCommand("SELECT * from users WHERE uname=&uname and pword=md5(&pword)", cnn)
        cnn.Open
        cmd.Parameters.AddWithValue("&uname", UsernameTextBox.Text)
        cmd.Parameters.AddWithValue("&pword", PasswordTextBox.Text)
        adptr = New MySqlDataAdapter(cmd)
        adptr.Fill(table)
    End Using
End Using

ExecuteNonQuery应用于更改数据库表(INSERT/UPDATE/DELETE)或其他DDL命令的查询。在您的示例中，可以将准备好的命令用于MySqlDataAdapter的一个超带构造函数(查看这个SqlDataAdapter文档，它们具有相同的逻辑效果)

此外，我也不确定MySqlParameter前缀是& (我一直使用@或?)