mysql - 将mysql结果保存到datatable

Question

我刚刚切换到使用参数进行 mysql 查询，但担心找不到将结果保存到数据表的方法。我想将此选择查询的结果保存到数据表“表”中。以前我会做

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前缀是&(我一直使用@或?)