开发者

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);
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜