Insert multiple object to MySQL

Question

Is there an other way to insert multiple objects to an MySQL database than the way shown here. This works but takes time to execute.

  using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr))
        {
            //Goes thrue the List<object>
            foreach(List<object> sub in listSubject)
            {
            MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "CALL stp_InsertSubject(@param_SubjectId, @param_ProjectId, @param_Used);";
            cmd.Parameters.AddWithValue("@param_SubjectId",Convert.ToInt32(sub[0]) );
            cmd.Parameters.AddWithValue("@param_ProjectId", Convert.ToInt32(sub[1]));
            cmd.Parameters.AddWithValue("@param_Used", Convert.ToBoolean(sub[2]) );

            conn.Open();

            cmd.ExecuteNonQuery();

            conn.Close();
                }

My Stored procedure:

CREATE DEFINER=`mortenstarck`@`%` PROCEDURE `stp_InsertSubject`(param_SubjectId int(45), param_ProjectId int(45), param_Used tinyint(1))

BEGIN INSERT INTO Subject_has_Projects(Subject_Id, Projects_Id, Used) VALUES (param_SubjectId, param_ProjectId, param_Used); END

You would need to show us what `stp_InsertSubject` does exactly to answer that... — ChristopheD, May 09 '12 at 21:27
Your `Id = (Int64)cmd.ExecuteScalar();` getting override every time in loop. — Amit, May 09 '12 at 21:32

Steve · Accepted Answer · 2012-05-09T21:51:52.200

Few things to improve:

Open the connection just one time outside the loop (no need to close with using)
Create the command, assign connection just one time before the loop
Create the parameters all before the loop with dummy values
Assign only the value inside the loop and call the ExecuteScalar()


using(MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr))
{  
    conn.Open(); 
    MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();   
    cmd.Connection = conn;   
    cmd.CommandText = "CALL stp_InsertSubject(@param_SubjectId, @param_ProjectId, @param_Used);";  
    cmd.Parameters.AddWithValue("@param_SubjectId",0 );   
    cmd.Parameters.AddWithValue("@param_ProjectId", 0);   
    cmd.Parameters.AddWithValue("@param_Used", false );   
    foreach(List<object> sub in listSubject)   
    {   
        cmd.Parameters["@param_SubjectId"].Value = Convert.ToInt32(sub[0]) ;   
        cmd.Parameters["@param_ProjectId"].Value = Convert.ToInt32(sub[1]);   
        cmd.Parameters["@param_Used"].Value = Convert.ToBoolean(sub[2]);   
        Id = (Int64)cmd.ExecuteScalar();   
    }   
}

Why would you use dummy values. Why not just create the parameters with explicit types? — Conrad Frix, May 09 '12 at 21:41
@ConradFrix I'm trying to put up an example but can't format the code right — Steve, May 09 '12 at 21:46

score 1 · Answer 2 · edited May 23 '17 at 12:11

You can try. Open connection outside foreach loop. This will save time in opening and closing connection every time in loop. This will improve performance.

Int64 Id = 0;
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr))
{
    //Goes through the List<object>
    conn.Open();
    foreach(List<object> sub in listSubject)
    {
        MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "CALL stp_InsertSubject(@param_SubjectId, @param_ProjectId, @param_Used);";
        cmd.Parameters.AddWithValue("@param_SubjectId",Convert.ToInt32(sub[0]) );
        cmd.Parameters.AddWithValue("@param_ProjectId", Convert.ToInt32(sub[1]));
        cmd.Parameters.AddWithValue("@param_Used", Convert.ToBoolean(sub[2]) );
        Id = (Int64)cmd.ExecuteScalar();
    }
     conn.Close();
}

How bad is opening and closing a SQL connection for several times? What is the exact effect?

Curse my slow fingers! This is probably the best way to solve your problem without changing your method of insertion. — FlyingStreudel, May 09 '12 at 21:37

score 0 · Answer 3 · answered May 09 '12 at 21:31

0

Have you thought about surrounding these calls with a single transaction?

answered May 09 '12 at 21:31

Rich

2,076
1
15
16

Insert multiple object to MySQL

3 Answers3

Linked