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?

Wifeless

"I will see the StateChange handler get called if I then attempt to perform an operation on the SQL connection" - In fact you answer your question. Connection object doesn't ping server and check state only when needed. Place connection.Open() after .StateChange+=... to see that it works. – Pantelegraph

@AlexKudryashev Shouldn't there be some sort of keep alive between the SQL client and the SQL server? My understanding is that SqlConnection objects should map one-to-one to SQL server sessions. If the server kills the session, is there a way that I can have that information passed to my code? – Wifeless

you could try to poll the server with something harmless like select 1 – Hesson

It is usually called when you try to send an sql command to you server, and the connection is broken or timed out. Since SqlConnection doesn't keep-alive the server, you may know that state changed only when you try to execute a command using this connection. – Seldom

@Seldom Is there some official documentation that you can point me to which supports the idea that "SqlConnection doesn't keep-alive the server"? – Wifeless

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.

Alberich

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://mcmap.net/q/211283/-what-39-s-the-best-way-to-test-sql-server-connection-programmatically

Pseudaxis

"The StateChange event is meant for the state of the connection, not the instance of the database server." -> But if the state of the server changes, then the state of the connection should also change (i.e. become "no longer functional".) – Wifeless

But the connection won't detect the state change of the database server unless you actually query something against the server, which is why you should have a wrapper like noted in the answer so you have a reliable way to identify the true state changes and not just at your application layer. – Pseudaxis