SQL connection problems

We have a helper class that we use to call stored procs on SQL Server. The helper function looks like this:

using (sqlCon = new SqlConnection(connectionString))
        {

            // Create database command object
            sqlCmd = sqlCon.CreateCommand();
            sqlCmd.CommandTimeout = commandTimeout;

            // Set command text AND command type
            sqlCmd.CommandText = procedureName;
            sqlCmd.CommandType = CommandType.StoredProcedure;

            // Set command parameters
            if (paramCollection != null)
            {
                foreach (DatabaseParameter dbParam in paramCollection)
                {
                    SqlParameter sqlParam = sqlCmd.Parameters.Add(dbParam.ParameterName, dbParam.ParameterType);
                    sqlParam.Direction = dbParam.ParameterDirection;
                    sqlParam.Value = dbParam.ParameterValue;
                    if (dbParam.ParameterSize != -1)
                        sqlParam.Size = dbParam.ParameterSize;
                    if (dbParam.ParamPrecision != -1)
                        sqlParam.Precision = (byte)dbParam.ParamPrecision;
                    if (dbParam.ParamScale != -1)
                        sqlParam.Scale = (byte)dbParam.ParamScale;
                }
            }

            try
            {
                sqlCon.Open();
            }
            catch
            {
                SqlConnection.ClearAllPools();
                sqlCon.Open();
            }

            // Prepare command
            sqlCmd.Prepare();

            // Execute the statement
            sqlCmd.ExecuteNonQuery();

            if (sqlCmd.Parameters.Contains("@Result"))
                return sqlCmd.Parameters["@Result"].Value;
            else
                return "Completed";
        }

So we are making sure connections are closed properly. The app is a multi threaded service and all the threads calls this method quite regularly. We do lock (thisobject) {} sections around the code that calls the above helper method to prevent the threads from stealing each others connections.

The connection.Open is inside a try catch with the ClearAllpools to clear broken connections.

However, we intermittently thro开发者_开发百科ugh the day get the following list of errors at random intervals.

The connection's current state is connecting.

or

The connection's current state is open.

The errors happen about once in every several thousand times the code is called, so it's quite hard to troubleshoot. Has anybody seen anything similar or ideas on what could be wrong?

I've run across these types of errors when dealing with a multi-threaded app, even when using a lock. After endless hours of debugging, I never found the problem and ended up trying to re-connect several times before giving up. To date, I haven't seen the error arise again.

int retries = 5;
while( true )
{
    try
    {
        DbCommand cmd = GetCommand( sql );
        using( DbConnection conn = cmd.Connection )
        {
            conn.Open();
            // do stuff
            break;
        }
    }
    catch
    {
        Thread.Sleep( 1000 );
        if( retries-- <= 0 )
        {
            throw;
        }
    }
}

Well, I can't say for certain why the connection is failing. But what's wrong is your catch block. You're ignoring the actual error and replacing it with an unhelpful one (the one(s) you're seeing).

Never ignore exceptions. They're thrown for a reason and tend to contain useful information about an unexpected error encountered by the system. You're having trouble figuring out the problem because you're throwing away that useful information without ever looking at it.

The errors you're seeing are coming out of the code in your catch block. This code is essentially trying to do the exact thing that just failed, but in a slightly different way. You need to log your error so you can find out why it's failing and reaching the catch block in the first place.

A good rule of thumb for try/catch blocks is that you should only catch the exception if you're prepared to handle the exception. Here you aren't handling it at all, so unless you do so you should just remove the try/catch entirely. Let the exception bubble up to somewhere that it can be handled. Alternatively, replace the code in the catch block with some error logging and exit the code block in a useful and meaningful way (return an error to the calling code block, throw a custom exception with the caught exception inside of it, etc.).

All else aside, have you tried proceeding conditionally, based on the connection's State?

using (sqlCon = new SqlConnection(connectionString))
{
    if (sqlCon.State == ConnectionState.Open)
    {
        ....
    }
}