Inserting into an MySQL db using 1 connection (C#.NET)

Question

i'm having an issue using C# inserting multiple rows into a MySQL database, have the following code;

//Upload to mysql
             string connStr = "server=server;user=username;database=databasae;port=3306;password=password;";
             MySqlConnection conn = new MySqlConnection(connStr);

             conn.Open();

             foreach (Channel chan in results)
             {
                 // Perform databse operations
                 try
                 {
                     //Create sql statment with parameters
                     string sql = "INSERT INTO channels(ID, Name) VALUES (@id,@name)";
                     MySqlCommand cmd = new MySqlCommand(sql, conn);
                     cmd.Parameters.AddWithValue("@id", chan.ID);
                     cmd.Parameters.AddWithValue("@name", chan.Name);
                     cmd.ExecuteNonQuery();
                     updateStatus("Inserted");

                 }


                 catch (Exception ex)
                 {
                     updateStatus(ex.Message.ToString());
                 }
                 conn.Close();

I seem to be getting "connection must be valid and open". From what i can see i'm passing the connection string correctly and i'm using ExecuteNonQuery. And idea's?

thanks

Are you closing the connection at the end of the foreach? If so you should do that after the foreach loop not inside. Aside on this example you are also missing } to close the foreach loop. — Ruben
– Ruben, Commented May 9, 2011 at 9:16
It would be more efficient to create your Command and Parameter objects outside the loop. Then you can just change the value of the two parameters and call ExecuteNonQuery inside the loop. — Joel Mueller
– Joel Mueller, Commented May 9, 2011 at 19:05

Ruben · Accepted Answer · 2015-12-20 20:56:26Z

conn.Close(); should be outside the foreach. The following would work :

         //Upload to mysql
         string connStr = "server=server;user=username;database=databasae;port=3306;password=password;";
         MySqlConnection conn = new MySqlConnection(connStr);

         conn.Open();

         foreach (Channel chan in results)
         {
             // Perform databse operations
             try
             {
                 //Create sql statment with parameters
                 string sql = "INSERT INTO channels(ID, Name) VALUES (@id,@name)";
                 MySqlCommand cmd = new MySqlCommand(sql, conn);
                 cmd.Parameters.AddWithValue("@id", chan.ID);
                 cmd.Parameters.AddWithValue("@name", chan.Name);
                 cmd.ExecuteNonQuery();
                 updateStatus("Inserted");

             }


             catch (Exception ex)
             {
                 updateStatus(ex.Message.ToString());
             }
          }
          conn.Close();

Tushar · Accepted Answer · 2011-05-09 09:21:03Z

2

Looks like the connection is inside the foreach loop. It should be outside the foreach loop.

conn.Close(); should be outside the foreach loop.

answered May 9, 2011 at 9:21

Tushar

1,2721 gold badge9 silver badges19 bronze badges

2 Comments

Nathan Over a year ago

Silly silly mistake by me, what an idiot!!! lol I really need to learn this walk away for 15 minutes if your having an problem!!

Tushar Over a year ago

It's ok. Happens to everyone and that's the reason we have stackoverflow ;) . Cheers !!

V4Vendetta · Accepted Answer · 2011-05-09 09:23:12Z

1

How about using

using(MySqlConnection conn = new MySqlConnection(connStr))
{
//your stuff in here
}

This is transformed into a try final block .. so should take care of your connection woes.

answered May 9, 2011 at 9:23

V4Vendetta

38.5k9 gold badges83 silver badges84 bronze badges

Comments

Devjosh · Accepted Answer · 2011-05-09 09:18:34Z

0

add finally block to the try catch code and put conn.close() in it.like

finally
{
     if(conn.ConnectionSTate=Connectionstate.open)
           {
               conn.close()
           }
}

answered May 9, 2011 at 9:18

Devjosh

6,4824 gold badges41 silver badges61 bronze badges

Collectives™ on Stack Overflow

Inserting into an MySQL db using 1 connection (C#.NET)

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related