omer schleifer omer schleifer - 4 months ago 11
SQL Question

How to check for database availability

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 aobut 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?

Thanks in advance, Omer

Answer

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