带有 MySQL INSERT 参数的 C#

Question

大家好，我正在使用 Visual C# 2010 和 MySQL 版本 5.1.48-community。我希望你能帮助我处理这段代码。我不觉得它对我有用。我错过了什么？

string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO room(person,address) VALUES(@person, @address)";
comm.Parameters.Add("@person", "Myname");
comm.Parameters.Add("@address", "Myaddress");
comm.ExecuteNonQuery();
conn.Close();

当我尝试编译它时。它说:

Person column cannot be null

已编辑:

但是当我尝试这段代码时。

comm.CommandText = "INSERT INTO room(person,address) VALUES('Myname', 'Myaddress')";

但是这段代码很容易受到 sql 注入(inject)攻击，但它可以工作，不会给我一个错误。

已编辑:

我尝试使用它。我找到了here所以我认为它会起作用，但给了我这个错误

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

有什么想法吗？

    string a = "myname";
    MySqlCommand cmd = new MySqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = "INSERT INTO room(person,address) VALUES(?,?)";
    //cmd.Prepare();

    cmd.Parameters.Add("person", MySqlDbType.VarChar).Value = a;
    cmd.Parameters.Add("address", MySqlDbType.VarChar).Value = "myaddress";
    cmd.ExecuteNonQuery(); // HERE I GOT AN EXCEPTION IN THIS LINE

任何帮助将不胜感激。

编辑:已解决我使用了这段代码:

cmd.CommandText = "INSERT INTO room(person,address) VALUES(?person,?address)";
cmd.Parameters.Add("?person", MySqlDbType.VarChar).Value = "myname";
cmd.Parameters.Add("?address", MySqlDbType.VarChar).Value = "myaddress";
cmd.ExecuteNonQuery();

谢谢!

Answer 1

您可以使用 AddWithValue 方法，例如:

string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO room(person,address) VALUES(@person, @address)";
comm.Parameters.AddWithValue("@person", "Myname");
comm.Parameters.AddWithValue("@address", "Myaddress");
comm.ExecuteNonQuery();
conn.Close();

或

尝试使用 ? 而不是 @，例如:

string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO room(person,address) VALUES(?person, ?address)";
comm.Parameters.Add("?person", "Myname");
comm.Parameters.Add("?address", "Myaddress");
comm.ExecuteNonQuery();
conn.Close();

希望对你有帮助...