Test sql connection without throwing exception

Question

To test if i can connect to my database, I execute the following code :

using (SqlConnection connection = new SqlConnection(myConnectionString))
{
   try
   {
      connection.Open();
      canConnect = true;
   }
   catch (SqlException) { }
}

This works except it throws an exception if the connection failed. Is there any other way to test a Sql connection that doesn't throw an exception ?

Edit : To add precision, i'm asking if there is a simple method that does that without having to open the connection and catch exceptions that can occur

Majunga

Why do you want to avoid the exception? – Hedelman

Answer 1

When attempting to open a connection, there is no way to avoid the exception if the connection can't be opened. It can be hidden away in a function somewhere, but you're going to get the exception, no matter what.

It was designed like this because generally you expect to be able to connect to the database. A failed connection is the exception.

That being said, you can test the current connection state at any time by checking the State property.

Comfortable

Answer 2

write an extension like so:

public static class Extension{
 public static bool CanOpen(this SqlConnection connection){
   try{
    if(connection == null){ return false; }

    connection.Open();
    var canOpen = connection.State == ConnectionState.Open;
    connection.close();
    return canOpen;
 }
 catch{
  return false;
 }
}

Then you can consume it like:

 using(var connection = new SqlConnection(myConnectionString)){
      if(connection.CanOpen()){
       // NOTE: The connection is not open at this point...
       // You can either open it here or not close it in the extension method...
       // I prefer opening the connection explicitly here...
     }
}

HTH.

Whorl

Nice answer but dude, you're throwing an exception – Hedelman

@CResults:there is no THROW in the catch block of the extension method effectively eating that exception. I prefer having this exception thrown, but the OP mentioned that he did not want an exception throw when checking if a connection can be opened, hence my solution. – Whorl

Care about this solution, SqlConnection.Open() will get the connection from the pool if pooling is available. Then the State will be Open even if the connection is corrupted (TCP channel down for example). And then the exception will only be raised when executing a command. A workaround is to use Connection.ChangeDatabase(Connection.Database); to check if the connection is available. – Worden

Answer 3

If it throws an an exception and you handle it in your catch block you already know the connection failed. I think you answered your own question.

Interdenominational

Answer 4

I think the real answer here is ping.

string data = "ismyserverpingable";
byte[] buffer = Encoding.ASCII.GetBytes (data);
int timeout = 120;
PingReply reply = pingSender.Send ("google.com", timeout, buffer, options);
if (reply.Status == IPStatus.Success)
{
}

Unless you are explicitly checking to see if a sql connection is possible 9/10 you should know if something is a sql server. This would save you that nasty memory usage of an exception which is what i am betting you are really after.

Ibson

Answer 5

You can not avoid exception coming while connecting database but have some function which handle this very well. I am using this function which return true if connection exist.

    public static bool IsSQLConnectionAvailable()
    {
        SqlConnection _objConn = new SqlConnection();

        try
        {
            _objConn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultSQLConnectionString"].ConnectionString;
            _objConn.Open();
        }
        catch
        {
            return false;
        }
        finally
        {
            if (_objConn.State == ConnectionState.Open)
                _objConn.Close();
        }

        return true;
    }

Elasticize

Answer 6

You could always use the ConnectionStringBuilder class and check for the existence of each piece that is required by a connection string before attempting to open it.

If the connection string is correct, but the database server you're connecting to is down, you're still going to get an excepton. Kind of pointless to check the quality of the string if the endpoint you're connecting to can potentially be offline.

Doyen

Answer 7

I'd like to share the whole solution I've implemented to avoid checking the connection at every call. If the connection string is wrong an exception stops the execution, otherwise the attempt to open the connectionstring is done just once for each connectionstring.

Since the connection is often multithreaded I've added a syncobj used by the lock which checks

#if DEBUG

private static object syncobj = new object();

private static ConcurrentDictionary<string, bool> CheckedConnection = new ConcurrentDictionary<string, bool>();

private static void CheckCanOpenConnection(SqlConnection connection)
{
    lock (syncobj)
    {
        try
        {
            CheckedConnection.TryGetValue(connection.ConnectionString, out bool found);
            if (found)
            {
                return;
            }
            else
            {
                connection.Open();
                var canOpen = connection.State == ConnectionState.Open;
                connection.Close();

                CheckedConnection.TryAdd(connection.ConnectionString, true);
                return;
            }
        }
        catch
        {
            throw new ApplicationException("Unable to connect to: " + connection.ConnectionString);
        }
    }
}

#endif

Here is the call to from the method which instantiate the connection

    private SqlConnection CreateConnection()
    {
        if (_connection == null)
        {
            _connection = new SqlConnection(this.ConnectionString);

#if DEBUG
            CheckCanOpenConnection(_connection);
#endif

        }
        return _connection;
    }

Actium