array insert in db
How best to put the array (100 or more length) in the database (MySQL)?
I do not want multiple access to the database because it is so loaded.
So my solution is as follows:
string insert = "INSERT INTO programs (name, id) VALUES ";
for(int i = 0; i < name.Length; i++)
{
if (i != 0)
{
insert = insert + ",(";
开发者_运维知识库 }
else
{
insert = insert + "(";
}
insert = insert + "'" + name[i] + "','" + id[i] + "'";
insert = insert + ")";
}
//INSERT INTO programs (name, id) VALUES ('Peter','32'),('Rikko','343') ....
But maybe is a faster version?
Thanks
Your solution is very insecure if name and ID can be changed by users (possible SQL injection). I recommend using a prepared statement to speed things up. There's an example in the MySQL documentation
I've never used MySql before, and I haven't compiled this, but this is how I'd approach it.
I would define a parameterized sql string. Then I would define my parameter objects, open the connection, then loop through the array assigning the values to the parameter objects and executing the statement.
using(var connection = new MySqlConnection("your connection string"))
{
using(var command = new MySqlCommand("INSERT INTO programs (name, id) VALUES (?name, ?id)", connection))
{
var nameParameter = new MySqlParameter("name");
var idParameter = new MySqlParameter("id");
command.Parameters.Add(nameParameter);
command.Parameters.Add(idParameter);
connection.Open();
for(int i = 0; i < name.Length; i++)
{
nameParameter.Value = name[i];
idParameter.Value = id[i];
command.ExecuteNonQuery();
}
connection.Close(); //Dispose being called by the using should close connection, but it doesn't hurt to close it here/sooner either.
}
}
Like I said, I haven't used MySql from C# before, so I don't know if the MySqlParameter class has a constructor that takes in the parameter name, but you get the idea.
I feel you can do something like
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
And insert everything by one query
I think you need to use bind variables. MySQL, just like most other SQL databases, does allow it and it is much faster than creating and running 100 insert statements one at a time.
First prepare
your SQL statement, putting in ?
where the bind vars go. Then execute
it using an array which contains all the values for a single insert.
For example,
$stmt = $db->prepare("insert into table values (?, ?)");
$array = array(1, 2);
$result = $db->execute($stmt, $array);
精彩评论