Multiple INSERT in one query using MySqlCommand

Question

I am using the following code:

        string cmd = "INSERT INTO " + Tables.Lux() + " VALUES(NULL, @Position, @Mode, @Timer)";
        try
        {
            using (var MyConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings["DataFormConnection"].ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand(cmd, MyConnection))
                {
                    MyConnection.Open();
                    command.Parameters.Add(new MySqlParameter("Position", Element.Position));
                    command.Parameters.Add(new MySqlParameter("Mode", Element.Mode));
                    command.Parameters.Add(new MySqlParameter("Timer", Element.Timer));
                    command.ExecuteNonQuery();
                }
            }
        }

I am using the above code to insert data from a list of Element containing 100 items. I would like to add 100 values in only one query, and I know that the SQL statement looks like:

INSERT INTO table (a,b) VALUES (1,2), (2,3), (3,4);

but I don't know how to apply that structure using the MySqlCommand.Parameters approach.

My goal is to pass this function List<Element> instead of just Element and create an INSERT statement with all the items in the list to be executed in only one query. Any help please?

Thank you.

You could create a stored procedure call this once, and then in here do your insert statement, if you are inserting from another table you can use this table to generate your data that will be inserted. — David B, Jun 30 '15 at 11:15
The answer to your question is in your post/code. You just need to add parameters not values. Later you can replace parameter value just before calling ExecuteNonQuery() method. — KV Prajapati, Jun 30 '15 at 11:24
@DavidBeaumont: The values are stored in a List, they are simple logs that I want to save. — Hamma, Jun 30 '15 at 11:35
@AVD: That is exactly what I would like to do, but I have no experience in this field and I can't find how to do that. All the information about multiple queries I found address the SQL statement point of view, not the application from a programmer point of view. — Hamma, Jun 30 '15 at 11:35

Xenogenesis · Accepted Answer · 2015-06-30T11:46:41.487

9

Try it like this:

string cmd = "INSERT INTO " + Tables.Lux() + " VALUES ";
int counter = 0;

foreach (Element e in list) 
{
    sql += "(NULL, @Position" + counter + ", @Mode" + counter + ", @Timer" + counter + "),";
    command.Parameters.Add(new MySqlParameter("Position" + counter, e.Position));
    command.Parameters.Add(new MySqlParameter("Mode" + counter, e.Mode));
    command.Parameters.Add(new MySqlParameter("Timer" + counter, e.Timer));
    counter++;
}

command.CommandText = sql.Substring(0, sql.Length-1); //Remove ',' at the end

This way you can have a variable number of parameters in your query and you only have to fire it once against the database, not n times

This is untested, just out of my head!

edited Jun 30 '15 at 11:46

answered Jun 30 '15 at 11:23

Xenogenesis

390
4
23

@Hamma Please discuss in the comments, not by editing Posts A and B are placeholders for your parameter names, sry I hadn't written them with the `@` in front – Xenogenesis Jun 30 '15 at 11:35
Ok it makes sense now. Sorry for editing, I was trying to comment but writing code looked so messy that I figured it would be so much easier to read the other way. I will try this, thank you! – Hamma Jun 30 '15 at 11:43
No problem, I hope it helps you – Xenogenesis Jun 30 '15 at 11:46
@Hamma Please mark as an answer if this post was able to help you – Xenogenesis Jul 01 '15 at 12:35
Yes, I tried it and it is working. I am adding 100 lines in one query right now. Thank you very much! – Hamma Jul 01 '15 at 13:40

score -1 · Answer 2 · answered Oct 30 '20 at 22:42

I used @xenogenesis answer to solve my problem, maybe can help someone else

List<EntregadorRPC.Address> addresstoinsert = eoSendAddressList.addresses;

int      batteryLvl = eoSendAddressList.batteryLevel;
DateTime dtHoraLocal;
int      size = addresstoinsert.Count;

const string insertHeader = "Insert into historicolocalizacao (Provedor, CodUsuario, Latitude, Longitude, Precisao, Logradouro, " +
                            "Complemento, Setor, Cidade, UF, Cep, DataHoraLocal, bateria) Values";

StringBuilder insertValues = new StringBuilder("");

using (MySqlCommand cmd = new MySqlCommand()) {

    for (int i = 0; i < size; i++) {
        EntregadorRPC.Address address = addresstoinsert[i];

        dtHoraLocal = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        dtHoraLocal = dtHoraLocal.AddMilliseconds(address.dataHoraLocal).ToLocalTime();
        /*address.logradouro       = WSUtils.RemoveSpecialCharacters(address.logradouro);
        address.formattedAddress = WSUtils.RemoveSpecialCharacters(address.formattedAddress);*/

        cmd.Parameters.AddWithValue($"@provedor{i}",      address.provedor);
        cmd.Parameters.AddWithValue($"@user_id{i}",       user.user_id);
        cmd.Parameters.AddWithValue($"@latitude{i}",      address.latitude);
        cmd.Parameters.AddWithValue($"@longitude{i}",     address.longitude);
        cmd.Parameters.AddWithValue($"@precisao{i}",      address.precisao);
        cmd.Parameters.AddWithValue($"@logradouro{i}",    address.logradouro);
        cmd.Parameters.AddWithValue($"@complemento{i}",   address.complemento);
        cmd.Parameters.AddWithValue($"@setor{i}",         address.setor);
        cmd.Parameters.AddWithValue($"@cidade{i}",        address.cidade);
        cmd.Parameters.AddWithValue($"@uf{i}",            address.uf);
        cmd.Parameters.AddWithValue($"@cep{i}",           address.cep);
        cmd.Parameters.AddWithValue($"@datahoralocal{i}", dtHoraLocal.ToString("yyyy-MM-dd HH:mm:ss"));
        cmd.Parameters.AddWithValue($"@bateria{i}",       batteryLvl);

        insertValues.Append($"(@provedor{i}, @user_id{i}, @latitude{i}, @longitude{i}, @precisao{i}, @logradouro{i}, @complemento{i}, @setor{i}, @cidade{i}, @uf{i}, @cep{i}, @datahoralocal{i}, @bateria{i})");

        if (i < size - 1) {
            insertValues.Append(",");
        }
    }

    cmd.Connection  = conn;
    cmd.CommandText = $"{insertHeader}{insertValues}";
    await cmd.ExecuteNonQueryAsync();
}

Multiple INSERT in one query using MySqlCommand

2 Answers2