How to check for database availability

.net, c#, connection, sql, sql-server

Solution

You can try like this.

    public bool IsServerConnected()
    {
        using (var l_oConnection = new SqlConnection(DBConnection.ConnectionString))
        {
            try
            {
                l_oConnection.Open();
                return true;
            }
            catch (SqlException)
            {
                return false;
            }
        }
    }

Problem

I have the following code to test DB connection, it runs periodically to test for DB availability: ``` private bool CheckDbConn() { SqlConnection conn = null; bool result = true; try { conn = DBConnection.getNewCon(); ConnectionState conState = conn.State; if (conState == ConnectionState.Closed || conState == ConnectionState.Broken) { logger.Warn(LogTopicEnum.Agent, "Connection failed in DB connection test on CheckDBConnection"); return false; } } catch (Exception ex) { logger.Warn(LogTopicEnum.Agent, "Error in DB connection test on CheckDBConnection", ex); return false; // any error is considered as db connection error for now } finally { try { if (conn != null) { conn.Close(); } } catch (Exception ex) { logger.Warn(LogTopicEnum.Agent, "Error closing connection on CheckDBConnection", ex); result = false; } } return result; } ``` And: ``` static public SqlConnection getNewCon() { SqlConnection newCon = new SqlConnection(); newCon.ConnectionString = DBConnection.ConnectionString; // m_con.ConnectionString; newCon.Open(); return newCon; } ``` My question is: will this work as expected? Specifically, I'm concerned about the test of the `ConnectionState`. Is it possible that the state will be: connecting (since `Open()` is synchronous)? What should I do in that case?

Original source