c# - 尝试在 DataGrid 上显示表时出现 MySQL 错误

Question

这是我正在使用的代码:

private void btn_search_Click(object sender, EventArgs e)
{
    try
    {
        MySqlConnection con = new MySqlConnection(@"Data Source=localhost;port=3306;Initial Catalog=dp;User Id=root;password=''");
        con.Open();
        DataTable dt = new DataTable();
        MySqlDataAdapter SDA = new MySqlDataAdapter("SELECT * FROM dp WHERE id LIKE " + txt_id.Text, con);
        SDA.Fill(dt);
        dataGridView1.DataSource = dt;
    }
    catch (MySqlException ex)
    {
        Console.WriteLine("StackTrace:" + ex.StackTrace);
    }

问题是，它抛出一个 MySQL 异常，

Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll StackTrace: at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at Dispatching_Software.SearchUsers.btn_search_Click(Object sender, EventArgs e) in C:\Users\Owner\documents\visual studio 2015\Projects\Dispatching Software\Dispatching Software\SearchUsers.cs:line 34

我想做的是在表中搜索用户并显示它们，问题似乎是 SDA.Fill(dt);

Answer 1

当您使用MySqlDataAdapter时，不需要使用con.Open();。另外，您应该始终使用 parameterized queries避免SQL Injection :

MySqlDataAdapter SDA = new MySqlDataAdapter("SELECT * FROM dp WHERE id LIKE '%' + @a + '%'", con);
SDA.SelectCommand.Parameters.AddWithValue("@a", txt_id.Text);

虽然直接指定类型并使用 Value 属性比 AddWithValue 更好:

 MySqlDataAdapter SDA = new MySqlDataAdapter("SELECT * FROM dp WHERE id LIKE @a", con);
SDA.SelectCommand.Parameters.Add("@a", MySqlDbType.VarChar, 200).Value = "%" + txt_id.Text;

Can we stop using AddWithValue() already?