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
– ChristopheD, Commented May 9, 2012 at 21:27
Your Id = (Int64)cmd.ExecuteScalar(); getting override every time in loop. — Amit
– Amit, Commented May 9, 2012 at 21:32

Steve · Accepted Answer · 2012-05-09 21:51:52Z

2

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

edited May 9, 2012 at 21:51

answered May 9, 2012 at 21:37

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Conrad Frix Over a year ago

Why would you use dummy values. Why not just create the parameters with explicit types?

Steve Over a year ago

@ConradFrix I'm trying to put up an example but can't format the code right

Community · Accepted Answer · 2017-05-23 12:11:28Z

1

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?

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered May 9, 2012 at 21:35

Amit

22.2k28 gold badges76 silver badges94 bronze badges

1 Comment

FlyingStreudel Over a year ago

Curse my slow fingers! This is probably the best way to solve your problem without changing your method of insertion.

Rich · Accepted Answer · 2012-05-09 21:31:41Z

0

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

answered May 9, 2012 at 21:31

Rich

2,0961 gold badge15 silver badges16 bronze badges

Collectives™ on Stack Overflow

Insert multiple object to MySQL

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related