Alex Rivera | Logout

SQL statement to check for connectivity?

Asked 2008-11-04T12:35:09.590
10

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.

c# sql
Edit
Report

2 Answers

2

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

SELECT N'Test'
answered 2008-11-04T15:28:46.557
0

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;
answered 2008-11-04T17:38:01.337

Your Answer