CODEWITHSUNDEEP
c#sqlmysqlarrays
lolalola
lolalola

Reputation: 3823

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

Upvotes: 1

Views: 3414

Answers (4)

MJB
MJB

Reputation: 7686

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

Upvotes: 0

vittore
vittore

Reputation: 17579

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

Upvotes: 0

Aaron Daniels
Aaron Daniels

Reputation: 9664

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.

Upvotes: 1

AndiDog
AndiDog

Reputation: 70148

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

Upvotes: 3

Related Questions