Re: Open method: Connection unexpectedly terminated

[Scott Lambeth]

I'm replying to an old thread, but I'll post what I did. The problem happens when either the server kills the connection due to inactivity (reaches timeout), the server is restarted, or the connection was killed by an administrator. This is standard, and should be automagically handled by .NET's built in connection pooling, but, alas, it isn't Once it fails with this error it does seem to get removed though. So I just retry the connecting until I get a good one, and this seems to clean up the pool. This is what I did to solve the problem... It may prove to fail in the future as this has not run in prod yet, but it works so far in testing.

The .Number property of the exception is 0 for this error, which makes me nervous. I wonder how many other errors are "0".

...
private MySqlConnection _sqlConn;  //to be use throughout the class
...

private void OpenDB()
{
    bool bRetry;
    int iRetryCount = 0;
    int iMaxRetry = 10;
    do
    {
        try
        {
            string sConnStr = ConfigurationSettings.AppSettings.Get("YourConnStringInAppSettings");
            _sqlConn = new MySqlConnection(sConnStr);
            _sqlConn.Open();
            bRetry = false;
        }
        catch (MySqlException mySqlEx)
        {
            switch (mySqlEx.Number)
            {
                case 0: //connection terminated (db was either restarted, or the connection timed out or was killed)
                    if (iRetryCount <= iMaxRetry)
                    {
                        bRetry = true;
                        iRetryCount++;
                        break;
                    } else {
                        throw new Exception("Error Connectiong to database:" + mySqlEx.Message);
                    }
                default: throw new Exception("Error Connecting to database:" + mySqlEx.Message);
            }
        }
    } while (bRetry && iRetryCount < iMaxRetry);
}

Edited 1 time(s). Last edit at 09/08/2006 04:02PM by Scott Lambeth.