使用 C# 对 MySQL 进行参数化查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/652978/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parameterized Query for MySQL with C#
提问by Elie
I have the code below (I've included what I believe are all relevant sections):
我有下面的代码（我已经包含了我认为所有相关的部分）：
private String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ? AND VAL_@ = ?;";
public bool read(string id)
{
level = -1;
MySqlCommand m = new MySqlCommand(readCommand);
m.Parameters.Add(new MySqlParameter("", val1));
m.Parameters.Add(new MySqlParameter("", val2));
MySqlDataReader r = m.ExecuteReader();
if (r.HasRows)
level = Convert.ToInt32(r.GetValue(0).ToString());
r.Close();
return true;
}
When I run this, I get an IndexOutOfBoundsException on adding the first parameter. What have I done wrong?
当我运行它时，我在添加第一个参数时得到一个 IndexOutOfBoundsException。我做错了什么？
采纳答案by Chris
Try this instead:
试试这个：
private String readCommand =
"SELECT LEVEL FROM USERS WHERE VAL_1 = @param_val_1 AND VAL_2 = @param_val_2;";
public bool read(string id)
{
level = -1;
MySqlCommand m = new MySqlCommand(readCommand);
m.Parameters.AddWithValue("@param_val_1", val1);
m.Parameters.AddWithValue("@param_val_2", val2);
level = Convert.ToInt32(m.ExecuteScalar());
return true;
}
回答by Chry Cheng
You need to use named parameters in your query. E.g.:
您需要在查询中使用命名参数。例如：
String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ?param1 AND VAL_2 = ?param2";
Then, pass the parameter names when you instantiate your MySqlParameter objects like so:
然后，在实例化 MySqlParameter 对象时传递参数名称，如下所示：
m.Parameters.Add(new MySqlParameter("param1", val1));
回答by Matt Brindley
I don't think the MySql.Data classes support unnamed parameters. If you're keen to use them, you could access your MySql db via the Odbc drivers, they support this.
我认为 MySql.Data 类不支持未命名参数。如果您热衷于使用它们，您可以通过 Odbc 驱动程序访问您的 MySql 数据库，它们支持这一点。
You'll need to name the parameters in your query:
您需要在查询中命名参数：
"SELECT LEVEL FROM USERS WHERE VAL_1 = @val1 AND VAL_2 = @val2;"
I've chosen the param indicator "@", but recent versions of MySql.Data support both "@" and "?".
我选择了参数指示符“@”，但 MySql.Data 的最新版本同时支持“@”和“?”。
Then update your param constructor to pass in the correct param name (you don't need to include the param indicator here, although it doesn't make any difference if you do).
然后更新您的 param 构造函数以传入正确的 param 名称（您不需要在此处包含 param 指示符，尽管这样做没有任何区别）。
m.Parameters.Add(new MySqlParameter("val1", val1));
PS. You prob know this already, or it was just omitted in the snippet, but I think you forgot to call Read on your instance of ExecuteReader.
附注。您可能已经知道这一点，或者只是在代码片段中省略了它，但我认为您忘记在您的 ExecuteReader 实例上调用 Read。
回答by Ujjwal Wagle
protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
MySqlConnection con = new MySqlConnection("server=localhost;User Id=root;database=result;password=1234");
con.Open();
MySqlCommand cmd = new MySqlCommand("Select * from users where username=?username and password=?password", con);
cmd.Parameters.Add(new MySqlParameter("username", this.Login1.UserName));
cmd.Parameters.Add(new MySqlParameter("password", this.Login1.Password));
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows ==true)
{
e.Authenticated = true;
}
}
回答by Ranjit Singh
m.Parameters.AddWithValue("parameter",value)
will be better option for parametrized query.
将是参数化查询的更好选择。
回答by Tarek.Mh
If you want to execute the sql many times, then you should use this way:
如果你想多次执行sql，那么你应该使用这种方式：
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO myTable VALUES(NULL, @number, @text)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@number", 1);
cmd.Parameters.AddWithValue("@text", "One");
for (int i=1; i <= 1000; i++)
{
cmd.Parameters["@number"].Value = i;
cmd.Parameters["@text"].Value = "A string value";
cmd.ExecuteNonQuery();
}
First time is without "ExecuteNonQuery" just adding the parameters with faked values, then inside the loop you add the real values.
第一次没有“ExecuteNonQuery”，只是添加带有伪造值的参数，然后在循环内添加真实值。
See this link: https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html
请参阅此链接：https: //dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html
