How to detect whether the connection to SQL Server is broken or not

  • Article

 

For managed code, use SqlConnection’s StateChange event:

 

        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection();

            conn.StateChange += ConnStateChangeEventHandler;

            conn.ConnectionString = "server=yourServerName; uid=yourUserName; pwd=xxxx; database=northwind";

            conn.Open();

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select * from customer";

                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }

            Console.ReadKey();

        }

        private static void ConnStateChangeEventHandler(Object sender, StateChangeEventArgs args)
        {
            Console.WriteLine("old state: {0}, new state: {1}", args.OriginalState, args.CurrentState);
        }

 

For ODBC application, use SQLGetConnectAttr to check the SQL_COPT_SS_CONNECTION_DEAD attribute:

 

UINT connection_state = SQL_CD_FALSE;
 
// check if the connection is broken
SQLGetConnectAttr(hdbc, SQL_COPT_SS_CONNECTION_DEAD, &connection_state, SQL_IS_UINTEGER, NULL);

if (connection_state == SQL_CD_TRUE )
{
    cout << “connection broken” << endl;
}