CODEWITHSUNDEEP
c#mysql
mortenstarck
mortenstarck

Reputation: 2803

Insert multiple object to MySQL

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

Upvotes: 0

Views: 2590

Answers (3)

Steve
Steve

Reputation: 216263

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

Upvotes: 2

Amit
Amit

Reputation: 22076

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?

Upvotes: 1

Rich
Rich

Reputation: 2096

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

Upvotes: 0

Related Questions