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.

Mozellemozes

Do the Values come from another SQL Table? – Spriggs

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. – Spriggs

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. – Coptic

@DavidBeaumont: The values are stored in a List, they are simple logs that I want to save. – Mozellemozes

@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. – Mozellemozes

Answer 1

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!

Vincenty

@Mozellemozes 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 – Vincenty

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! – Mozellemozes

No problem, I hope it helps you – Vincenty

@Mozellemozes Please mark as an answer if this post was able to help you – Vincenty

Yes, I tried it and it is working. I am adding 100 lines in one query right now. Thank you very much! – Mozellemozes

Answer 2

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

Ammunition