MySql / Odbc connection problem

Question

I'm accessing a MySql database via ODBC. It normally works fine, but if the database is stopped and restarted I have to restart my application in order to reconnect to the database.

The code for accessing the database is like this:

        OdbcConnection connection = new OdbcConnection(connectString);
        OdbcCommand command = connection.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = "select * from cds";
        OdbcDataAdapter dataAdapter = new OdbcDataAdapter(command);
        DataSet dataSet = new DataSet();
        connection.Open();
        dataAdapter.Fill(dataSet);
        connection.Close();

After a restart of the database, I get a 'MySql server has gone away' exception in dataAdapter.Fill method. Is there any way I can reconnect to the database when I detect that the connection has broken?

I use VS2008 and MySql 5.1.30.

Answer 1

This "restart the application in order to reconnect to the database" is simply a bad design decision. The initialization of the database connection should not be put in the startup code, instead put it in a separate procedure, and in case one of your methods got the exception "MySql server has gone away" simply call this procedure in order to re-initialize the connection to the DB.