How to check for database availability

Question

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?

I cannot comment so... ... also avoid catching general Exceptions "catch(Exception ex)" and try to catch specific exceptions like the examples above "catch(SqlException ex)" — shadow, Jun 26 '15 at 06:57

score 56 · Accepted Answer · answered Apr 23 '13 at 13:56

56

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

answered Apr 23 '13 at 13:56

Ramesh Durai

2,666
9
32
55

what if l_oConnection.Close(); will throw an exception in the finally block? – omer schleifer Apr 23 '13 at 13:57
1

using ... close and dispose the connection, no need to close then – Steve Apr 23 '13 at 13:58
I was using this but found it returned true on the first call after the database was taken offline. This was using .Net 3.5 against a sql server 2012 db. – monty Mar 05 '18 at 01:41

Dustin Kingen · Answer 2 · 2013-04-23T14:03:19.940

SqlConnection will throw a SqlException when it cannot connect to the server.

public static class SqlExtensions
{
    public static bool IsAvailable(this SqlConnection connection)
    {
        try
        {
            connection.Open();
            connection.Close();
        }
        catch(SqlException)
        {
            return false;
        }

        return true;
    }
}

Usage:

using(SqlConnection connection = GetConnection())
{
    if(connection.IsAvailable())
    {
        // Success
    }
}

Cyril Gandon · Answer 3 · 2013-04-23T14:08:36.340

Your code seems fine, but you really need to use the IDisposable pattern, and some naming convention too:

private bool CheckDbConnection(string connectionString)
{
    try
    {
        using(var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            return true;
        }
    }
    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
    }
}

And connection.Close() is not supposed to throw. Just use the using block and your are fine.

No need to test the Close state, since you have just opened it.
More about the Broken state:

Broken The connection to the data source is broken. This can occur only after the connection has been opened. A connection in this state may be closed and then re-opened. (This value is reserved for future versions of the product.)

So really, no need to test that.

The Connecting state could be catch if you are in a multithread context and your instance of connection is shared. But it is not your case here.

score 3 · Answer 4 · answered Jan 30 '21 at 05:53

This code does not block a UI if called.

public static class DatabaseExtensions
{
    public static async Task<bool> IsConnectionViable(this string connectionStr)
    {
        await using var sqlConn = new SqlConnection(connectionStr);
        return await sqlConn.IsConnectionViable();
    }

    public static async Task<bool> IsConnectionViable(this SqlConnection connection)
    {
        var isConnected = false;

        try
        {
            await connection.OpenAsync();
            isConnected = (connection.State == ConnectionState.Open);
        }
        catch (Exception)
        {
            // ignored
        }

        return isConnected;
    }
}

score 0 · Answer 5 · answered Jul 18 '16 at 20:22

0

actually, in visual studio, connection class has sonnectionstate property.

when connection state changes, connections statechange event is been trigerred.

you might want to check this article.

https://msdn.microsoft.com/en-us/library/aa326268(v=vs.71).aspx

answered Jul 18 '16 at 20:22

nadir

63
1
11

Not sure how it's related to visual studio , plus if you look at the post , it has connectionState in it... – omer schleifer Jul 19 '16 at 12:04

score 0 · Answer 6 · answered Mar 05 '18 at 01:51

I was using @Ramesh Durai's solution but found that on my setup at least (the app calling/testing periodically after the app had started; using .Net 3.5 with Sql Server 2012 database) that the first call to IsConnected() after taking the database offline was returning true. However, it was throwing the expected exception on the ExecuteScalar() line below:

public bool IsConnected() {
    using (var conn = new SqlConnection(DBConnection.ConnectionString)) {
        using (var cmd = New SqlCommand("SELECT 1", conn)) {
            try {
                conn.Open();
                cmd.ExecuteScalar();
                return true;
            } catch (SqlException) {
                return false;
            }
        }
    }
}

score 0 · Answer 7 · answered Jan 21 '19 at 21:30

This code is for Mysql.

public class Program
{
string connection = "SERVER=localhost; user id=root; password=; database=dbname";
private void Form1_Load(object sender, System.EventArgs e)
{
checkifconnected();
}

private void checkifconnected()
{
MySqlConnection connect = new MySqlConnection(connection);
try{
connect.Open();
MessageBox.Show("Database connected");
}
catch
{
MessageBox.Show("you are not connected to database");
}
}

public static void Main()
{

}
}

How to check for database availability

7 Answers7

Linked