SQL statement to check for connectivity?

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.

From stackoverflow

• 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.

• You could do this:

  Select 1
  

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

  Dave : Fine for SQL Server, but that won't work for DB2 or Oracle. See Pax Diablo's answer.

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

  Omar Kooheji : yes a a new connection is opened when the batch starts but the batch can take upwards of 15 mins.

  StingyJack : ok... so then what? does the batch abort after a few minutes? Is your connection timeout set high enough? (it defaults to 2 minutes.

• Loss of connectivity may happen anytime.

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

• select getdate()

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

  SELECT N'Test'
  

• 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.

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

• 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.