Refresh mySQL table with C #

Question

I wrote some C # to update the MySql table, but I get an exception every time I call the ExecuteNonQuery () method. I researched this on the Internet, and every solution I find causes the same error. I have an open database connection and the database update request is spelled correctly. The code I have come up with so far is:

public int executeUpdate() { int result = 0; if (isConnected) { try { MySqlConnection cn = new MySqlConnection(connection.ConnectionString); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = cn; cmd.CommandText = "UPDATE test SET status_id = 1 WHERE test_id = 1"; int numRowsUpdated = cmd.ExecuteNonQuery(); } catch (MySqlException exSql) { Console.Error.WriteLine("Error - SafeMySql: SQL Exception: " + query); Console.Error.WriteLine(exSql.StackTrace); } catch (Exception ex) { Console.Error.WriteLine("Error - SafeMySql: Exception: " + query); Console.Error.WriteLine(ex.StackTrace); } } else Console.Error.WriteLine("Error - SafeMySql: executeQuery failed. Not connected to DB"); } 

Answer 1

I do not see an open connection .

Here is an example from MSDN : even inside the used block they open the connection openly

 private static void CreateCommand(string queryString, string connectionString) { using (SqlConnection connection = new SqlConnection( connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); command.Connection.Open(); command.ExecuteNonQuery(); } } 

Change The principle is the same for MySQL as it is for SQL Server:

 public void CreateMySqlCommand(string myExecuteQuery, MySqlConnection myConnection) { MySqlCommand myCommand = new MySqlCommand(myExecuteQuery, myConnection); myCommand.Connection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } 

Answer 2

Change your try section to the following code:

 try { using(MySqlConnection cn = new MySqlConnection(connection.ConnectionString)) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = cn; cmd.CommandText = "UPDATE test SET status_id = 1 WHERE test_id = 1"; cn.Open(); int numRowsUpdated = cmd.ExecuteNonQuery(); cmd.Dispose(); } } 

Before executing a command, you must open a connection. In the above example, the command object will be immediately deleted and the connection object will be closed and closed when exiting the use section.