如何使用MySqlCommand插入多行并准备语句？（＃C）(How do multi rows insert with MySqlCommand and prepare statement?(#C)

如何使用MySqlCommand插入多行并准备语句？（＃C）(How do multi rows insert with MySqlCommand and prepare statement?(#C))

Mysql举例说明如何在准备语句和.NET中插入行： http : //dev.mysql.com/doc/refman/5.5/en/connector-net-programming-prepared.html

它看起来像它的工作，因为在每次迭代结束时调用： cmd.ExecuteNonQuery() ：

INSERT INTO VALUES()...;INSERT INTO VALUES()...;INSERT INTO VALUES()...;

它可以通过使用如下准备语句来完成：

INSERT INTO all values...

更多解释::

mysql示例中的代码（每次迭代中的cmd.ExecuteNonQuery（））：

MySql.Data.MySqlClient.MySqlConnection conn; MySql.Data.MySqlClient.MySqlCommand cmd; conn = new MySql.Data.MySqlClient.MySqlConnection(); cmd = new MySql.Data.MySqlClient.MySqlCommand(); conn.ConnectionString = strConnection; try { 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(); } }

* 我想要的代码（ cmd.ExecuteNonQuery();在所有迭代之后）：*

Mysql give example how insert rows with prepare statement and .NET: http://dev.mysql.com/doc/refman/5.5/en/connector-net-programming-prepared.html

Its looks that its works like that,because in the end of each iteration call to:cmd.ExecuteNonQuery():

INSERT INTO VALUES()...;INSERT INTO VALUES()...;INSERT INTO VALUES()...;

Can it done with use of prepare statement like that:

INSERT INTO all values...

More explanations::

The code in mysql example (cmd.ExecuteNonQuery() in each iteration):

*The code that i want to have like that(cmd.ExecuteNonQuery(); after all iterations): *

最满意答案

尝试这个：

using (var connection = new MySqlConnection("your connection string")) { connection.Open(); // first we'll build our query string. Something like this : // INSERT INTO myTable VALUES (NULL, @number0, @text0), (NULL, @number1, @text1)...; StringBuilder queryBuilder = new StringBuilder("INSERT INTO myTable VALUES "); for (int i = 0; i < 10; i++) { queryBuilder.AppendFormat("(NULL,@number{0},@text{0}),", i); //once we're done looping we remove the last ',' and replace it with a ';' if (i == 9) { queryBuilder.Replace(',', ';', queryBuilder.Length - 1, 1); } } MySqlCommand command = new MySqlCommand(queryBuilder.ToString(), connection); //assign each parameter its value for (int i = 0; i < 10; i++) { command.Parameters.AddWithValue("@number" + i, i); command.Parameters.AddWithValue("@text" + i, "textValue"); } command.ExecuteNonQuery(); }

Try this:

更多推荐