When is DbConnection.StateChange called?

Question

I have the following code:

   class Program
{
    static void Main()
    {
        var connection = new SqlConnection("myConnectionString");
        connection.Open();
        connection.StateChange += HandleSqlConnectionDrop;
        Console.WriteLine("Hi");
        Console.ReadLine();
    }

    private static void HandleSqlConnectionDrop(object connection, StateChangeEventArgs args)
    {
        Console.WriteLine("DB change detected");
    }
}

I start the above code while the SQL server instance is running. I then proceed to execute

SHUTDOWN WITH NOWAIT;

on the sql server instance that the program is connected to. I then observer the SQL server service stopping. However, I never see the "DB change detected" message in the output. Why is this?

Aside: I will see the StateChange handler get called if I then attempt to perform an operation on the SQL connection, but never before hand. Is there a way this behavior can be changed?

Answer 1

When is DbConnection.StateChange called?

You can find out by looking at the Microsoft reference source code.

The StateChange event is raised by the DbConnection.OnStateChange function. Looking for references to this function yields only a few instances:

Firstly, in the SqlConnection class, OnStateChange is called only in the Close method.

Then in the DbConnectionHelper.cs file, there's a partial class called DBCONNECTIONOBJECT. It looks like it's used for all DbConnection-derived classes using some build-time shenanigans. So you can consider it to be part of SqlConnection. In any case, it calls OnStateChange only from within the SetInnerConnectionEvent function.

As far as I can tell (the partial class nonsense makes it difficult), the SqlConnection.SetInnerConnectionEvent is only called from SqlConnectionFactory.SetInnerConnectionEvent. And that is called from:

• DbConnectionClosed.TryOpenConnection
• DbConnectionInternal.TryOpenConnectionInternal
• DbConnectionInternal.CloseConnection

So, in summary - the event is only raised in response to client-side actions - there does not appear to be any polling of the connection-state built into SQLConnection.

Is there a way this behavior can be changed?

Looking at the source code, I can't see one. As others have suggested, you could implement your own polling, of course.

Answer 2

The StateChange event is meant for the state of the connection, not the instance of the database server. To get the state of the database server,

The StateChange event occurs when the state of the event changes from closed to opened, or opened to closed.

From MSDN: https://msdn.microsoft.com/en-us/library/system.data.common.dbconnection.statechange(v=vs.110).aspx

If you're going to roll your own monitor for the database, then you may consider using a method that returns true/false if the connection is available and ping that method on a schedule. You could even wrap a method to do this in an endless loop repeating after a duration of time and raise it's own event when this "state" really changes then.

Here's a quick method from another SO answer that is a simple approach:

/// <summary>
/// Test that the server is connected
/// </summary>
/// <param name="connectionString">The connection string</param>
/// <returns>true if the connection is opened</returns>
private static bool IsServerConnected(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (SqlException)
        {
            return false;
        }
    }
}

Source: https://stackoverflow.com/a/9943871/4154421