blocks|key|1603368|text|试着这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1603369|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;
}|code-block|syntax|javascript|1603370|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this instead:

<pre><code>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;
}
</code></pre>

blocks|key|1388581|text|您需要在查询中使用命名参数。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1388582|String+readCommand+=+"SELECT+LEVEL+FROM+USERS+WHERE+VAL_1+=+?param1+AND+VAL_2+=+?param2";|code-block|syntax|javascript|1388583|然后，在实例化MySqlParameter对象时传递参数名称，如下所示：|1388584|m.Parameters.Add(new+MySqlParameter("param1",+val1));|1388585|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You need to use named parameters in your query. E.g.:

<pre><code>String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ?param1 AND VAL_2 = ?param2";
</code></pre>

Then, pass the parameter names when you instantiate your MySqlParameter objects like so:

<pre><code>m.Parameters.Add(new MySqlParameter("param1", val1));
</code></pre>

blocks|key|1603482|text|我不认为MySql.Data类支持未命名的参数。如果你热衷于使用它们，你可以通过Odbc驱动程序访问你的MySql数据库，他们支持这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1603483|您需要在查询中命名参数：|1603484|"SELECT+LEVEL+FROM+USERS+WHERE+VAL_1+=+@val1+AND+VAL_2+=+@val2;"|code-block|syntax|javascript|1603485|我选择了参数指示符"@"，但最近的MySql.Data版本同时支持"@“和"?”。|1603486|然后更新param构造函数以传入正确的param名称(您不需要在此处包含param指示符，尽管这样做也没有任何区别)。|1603487|m.Parameters.Add(new+MySqlParameter("val1",+val1));|1603488|PS。您可能已经知道这一点，或者只是在代码片段中省略了它，但我认为您忘记了在ExecuteReader实例上调用Read。|1603489|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|X|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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.

You'll need to name the parameters in your query:

<pre><code>"SELECT LEVEL FROM USERS WHERE VAL_1 = @val1 AND VAL_2 = @val2;"
</code></pre>

I've chosen the param indicator "@", but recent versions of MySql.Data support both "@" and "?". 

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).

<pre><code>m.Parameters.Add(new MySqlParameter("val1", val1));
</code></pre>

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.

blocks|key|1603526|text|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;
++++}
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1603527|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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;
 }
}
</code></pre>

blocks|key|1603549|text|m.Parameters.AddWithValue("parameter",value)+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1603550|将是参数化查询的更好选择。|unstyled|1603551|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>m.Parameters.AddWithValue("parameter",value) 
</code></pre>

will be better option for parametrized query.

blocks|key|1388739|text|如果要多次执行sql，则应使用以下方式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1388740|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%2B%2B)
{
++++cmd.Parameters["@number"].Value+=+i;
++++cmd.Parameters["@text"].Value+=+"A+string+value";

++++cmd.ExecuteNonQuery();
}|code-block|syntax|javascript|1388741|第一次没有"ExecuteNonQuery“，只是添加了带有伪造值的参数，然后在循环中添加真实值。|1388742|请参阅此链接：https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html|offset|length|1388743|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html^0|0|0|0|7|2K|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

If you want to execute the sql many times, then you should use this way:

<pre><code>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 &lt;= 1000; i++)
{
 cmd.Parameters["@number"].Value = i;
 cmd.Parameters["@text"].Value = "A string value";

 cmd.ExecuteNonQuery();
}
</code></pre>

First time is without "ExecuteNonQuery" just adding the parameters with faked values, then inside the loop you add the real values.

See this link:
<a href="https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html" rel="nofollow">https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html</a>

I have the code below (I've included what I believe are all relevant sections):

<pre><code>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;
}
</code></pre>

When I run this, I get an IndexOutOfBoundsException on adding the first parameter. What have I done wrong?

Parameterized Query for MySQL with C#

数据库

我有下面的代码(我已经包含了我认为所有相关的部分)：private String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ? AND VAL_@ = ?;";public bool read(string id){    level = -1;    M...

问用C#实现MySQL的参数化查询
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用C#实现MySQL的参数化查询EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用C#实现MySQL的参数化查询
EN