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.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(); }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):
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(); } }*The code that i want to have like that(cmd.ExecuteNonQuery(); after all iterations): *
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(); }最满意答案
尝试这个:
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:
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(); }更多推荐
发布评论