Multiple INSERTs in one query using MySqlCommand
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 an Item list containing 100 items. I would like to add 100 values in just one query and I know that the SQL expression looks like this:
INSERT INTO table (a,b) VALUES (1,2), (2,3), (3,4);
but I dont know how to apply this structure with an approach MySqlCommand.Parameters
.
My goal is to pass this function List<Element>
instead of simply Element
and create a statement INSERT
with all the items in the list that will be executed in only one request. Any help please?
Thank.
source to share
Try the following:
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 need to run it once against the database, not n times
This is untested, just out of my head!
source to share