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

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

---

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

---

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

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

---

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.

---

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

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