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.

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!