How do multi rows insert with MySqlCommand and prepare statement?(#C)

Question

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

score 11 · Accepted Answer · edited Apr 10 '14 at 08:40

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

Can connection be open only before command.ExecuteNonQuery(); or connection have to be open before command.Parameters.AddWithValue ? — Ben, Sep 30 '12 at 14:10
@Yosef You can open the connection just before `command.ExecuteNonQuery();` — Nasreddine, Sep 30 '12 at 14:17

How do multi rows insert with MySqlCommand and prepare statement?(#C)

1 Answers1