How are multiple rows inserted with MySqlCommand and prepared for application? (# C)

Question

Mysql give an example of how to insert strings with preparation and .NET instructions: http://dev.mysql.com/doc/refman/5.5/en/connector-net-programming-prepared.html

Its appearance looks like this because at the end of each iterative call: cmd.ExecuteNonQuery() :

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

Can this be done using the make statement:

 INSERT INTO all values... 

Other explanations ::

---

The code in the mysql example (cmd.ExecuteNonQuery () at 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 I want to have is ( 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(); } 

Answer 1

Try the following:

 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(); }