Disabled SQL connection claims to be open C#

Question

A number of discussions like this are treating how to check if a SQL connection is open, using the ConnectionState enum. Recently I have experienced that a ConnectionState.Open may not always tell the truth in .NET 2.0.

If the connection is broken from outside while my C# application is running, the connection state is not updated. Since it still claims that the connection is open, I can not use the following assurance method:

if(Something_Connection.State != ConnectionState.Open)
{
Something_Connection.Close();
Something_Connection.Open();
}

The issue may be reproduced using the following test. Assuming that you initially have an open connection m_dbConnection and a working command line method CommandLineUtils.Run(...):

    [Test]
    public void ConnectionStateDoesNotLie()
    {
        // Close SQL service:
        Console.WriteLine(CommandLineUtils.Run("net", "stop \"SQL Server (MSSQLSERVER)\"", 10));
        System.Threading.Thread.Sleep(500);
        // Check state:
        bool stateIsCorrect = m_dbConnection.ConnectionState != ConnectionState.Open;

        // Finished testing, restart the SQL service:
        Console.WriteLine(CommandLineUtils.Run("net", "start \"SQL Server (MSSQLSERVER)\"", 30));
        Assert.IsTrue(stateIsCorrect, "Connection state of closed connetion claims to be open.");
    }

My question is if there is a better method to check if a connection has been broken? Prior to running a query.

Of cause I could run every query in a try-catch and then try to reopen the connection if an exception is thrown. But this seems like a clumsy solution. I also want to avoid running any dummy update to test the connection prior to every query in my program.

(Why would I want to stop my SQL service during runtime? I would not, but people using my program may sometimes leave it open for 5 hours and then come back expecting it to work. Sometimes their connection may have failed during this period)

`people using my program may sometimes leave it open for 5 hours and then come back expecting it to work` - Ideally your program should only open the connection long enough to run your query, and close it immediately after. — ElGavilan, Oct 14 '15 at 15:49
If you keep connections open for long periods of time you are going to run into connection pool issues because they will all be consumed and new users won't be able to get a connection. As stated previously you should dispose of your connection object as soon as you are done with it. Wrap it in a USING makes this easy and nearly error proof. — Sean Lange, Oct 14 '15 at 15:51

score 1 · Answer 1 · answered Oct 14 '15 at 15:51

I'd recommend this pattern: Get the SQL task request, Open the connection, perform the task, Close the connection, respond to the results.

Replace m_dbConnection with m_dbConnectionString.

Your app will be more reliable and any time you need to restart SQL, you won't have a ton of "are you sure -- there are 20 people connected" messages.

score 1 · Answer 2 · answered Nov 10 '17 at 11:50

The solution to this problem is to disable connection pooling. For example (I'm using PowerShell here, but it's the same for any other .NET language), using a disabled user named U with a password PW on a server S:

$conn = [System.Data.SqlClient.SqlConnection]::new("server=S;user id=U;password=PW")
$conn.Open();  #Throws no exception, reports connection state of Open

$conn = [System.Data.SqlClient.SqlConnection]::new("server=S;user id=DU;password=PW;Pooling=False")
$conn.Open();  #Throws an exception

score 0 · Accepted Answer · answered Oct 19 '15 at 08:54

Thanks for your comments and answers, I will keep your pattern in mind for future reference, @Dustin_00.

However in this special case I forgot to specify that the application in view is a database API. Therefore the responsibility for opening and closing connections should be forwarded to the programs integrating it, as one sometimes would desire to perform a number of operations on the same connection. But I want to make the API more invulnerable to errors concerning lost connections.

Since this is the case, and no answers concerning a non-lying connection.State test could be found, I landed on the solution mentioned as an option in my question. Where the API performs an extra try, if the query for some reason fails. Preferably I would like a trustworthy solution similar to

    if (Connection.State != ConnectionState.Open){}.

For others running into the same issue without possibility to avoid it, an example wrapper method is provided here:

    /// <summary>
    /// Wrapper method for OdbcCommand.ExecuteScalar, trying to open connection if the first attempt fails
    /// </summary>
    /// <param name="readyCommand"></param>
    /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
    internal static object ExecuteScalar(OdbcCommand readyCommand, bool throwExceptions)
    {
        if (readyCommand == null)
            return -1;
        object retVal;
        try
        {
            retVal = readyCommand.ExecuteScalar();
        }
        catch (Exception e)
        {
            if (readyCommand.Connection != null)
            {
                try
                {
                    // Try reopening connection and retry;
                    readyCommand.Connection.Close();
                    readyCommand.Connection.Open();
                    retVal = readyCommand.ExecuteScalar();
                }
                catch (Exception)
                {
                    if (throwExceptions)
                        throw e; // rethrow the original exception
                    retVal = null;
                }
            }
            else
            {
                if (throwExceptions)
                    throw e; // rethrow the original exception
                retVal = null;
            }
        }
        return retVal;
    }

Disabled SQL connection claims to be open C#

3 Answers3