SQL statement to check for connectivity?

Question

I'm looking for a dummy SQL statement that will work from a C# SQL connection to check for connectivity.

Basically I need to send a request to the database, I don't care what it returns I just want it to be successful if the database is still there and throw an exception if the database isn't.

The scenario I'm testing for is a loss of connectivity to the database, where the SQLConnections State property seems to still be "Open" but there is no connectivity.

Answer 1

Most SQL databases have a 'table' for this purpose.

In DB2, it's:

select * from sysibm.sysdummy1

while Oracle has, from memory,

select * from dual

It'll depend on the database at the back end.

Answer 2

You could do this:

Select 1

Ok, how about sending an empty string or blank space. Those are valid commands for Sql Server.

Answer 3

You should get an error if you are unable to open a new connection because the db is unavailable.

It sounds to me like you are keeping a connection open all the time (which is usually a bad idea - a new connection should be opened before a batch is executed). Is this the case?

Answer 4

Loss of connectivity may happen anytime.

What if the proposed SELECT statements execute fine, but the connection breaks immediately after (successfully) executing them?

Answer 5

select getdate()

Answer 6

The simplest method is to execute a select that does nothing.

SELECT N'Test'

Answer 7

It would be better to catch your implementation's not-connected exception for EVERY sql statement you execute, rather than using a dummy statement to test for connectivity. I have seen systems where upwards of 10% of database CPU time is spent responding to these dummy queries.

Answer 8

Example Delphi code, which I trust will be easy to adapt:

function IsConnValid(var Conn: TADOConnection; DBType: TDBType): boolean;
var
  qry : TADOQuery;
begin
  //gimme a connection, and i'll create a query, try to retrieve dummy data.
  //if retrieval works, return TRUE. otherwise, return FALSE.
  qry := TADOQuery.Create(nil);
  try
    qry.Connection := Conn;

    case DBType of
      //syntax for a dummy query varies by vendor.
      dbOracle    : qry.Sql.Add('SELECT 1 FROM DUAL');
      dbSqlServer : qry.Sql.Add('SELECT 1');
    end;  //case

    try
      qry.Open;
      //try to open the query.
      //if we lost the connection, we'll probably get an exception.
      Result := not(qry.Eof);  //a working connection will NOT have EOF.
      qry.Close;
    except on e : exception do
      //if exception when we try to open the qry, then connection went bye-bye.
      Result := False;
    end;  //try-except
  finally
    qry.Free;
  end;  //try-finally
end;

Answer 9

One way of finding out if the connection to the database still does actually exist, is to try to perform some operation on the connection. If the connection has died, the ConnectionState property still remains as "Open", but when you try and do something with it you will get your exception. For example:

SqlConnection sqlConn;
    private bool dbConnectionExists() {
        try
        {
            sqlConn.ChangeDatabase("MyDBname");
            return true;
        }
        catch
        {
            return false;
        }
    }

    private void button1_Click(object sender, EventArgs e)
    {
        if (dbConnectionExists())
        {
            // Connection ok so do something            
        }
    }

The connectionState property changes to "Closed" once this type of operation is performed and fails, so you can then check the state if you want aswell.

Hope that helps.