CODEWITHSUNDEEP
c#.netsql-serverdatabase-connectionconnection-pooling
Håkon Seljåsen
Håkon Seljåsen

Reputation: 597

Disabled SQL connection claims to be open C#

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)

Upvotes: 1

Views: 1147

Answers (3)

Dave Markle
Dave Markle

Reputation: 97691

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

Upvotes: 1

Håkon Seljåsen
Håkon Seljåsen

Reputation: 597

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;
    }

Upvotes: 0

Dustin_00
Dustin_00

Reputation: 445

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.

Upvotes: 1

Related Questions