SQL to test connectivity?

Question

I am looking for a dummy SQL statement that will work with a C # SQL connection to test 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 still exists and throws an exception if the database is not.

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

Answer 1

Most SQL databases have a “table” for this purpose.

In DB2, this is:

select * from sysibm.sysdummy1 

while Oracle has memory,

 select * from dual 

This will depend on the database on the back panel.

Answer 2

You can do it:

 Select 1 

Good, how about sending an empty string or space. These are valid commands for Sql Server.

Answer 3

Loss of communication can occur at any time.

What if the proposed SELECT statements execute normally, but the connection is terminated immediately after (successfully) executing them?

Answer 4

You should get an error if you cannot open a new connection because db is not available.

It seems to me that you constantly open a connection (which is usually bad - a new connection should be open before the game is executed). Is this the case?

Answer 5

select getdate ()

Answer 6

The easiest way is to execute select, which does nothing.

 SELECT N'Test' 

Answer 7

It is better to catch the implementation-related exception only for the statement EVERY sql you are running, instead of using a dummy statement to test connectivity. I have seen systems in which up to 10% of the CPU time of the database is spent on these dummy queries.

Answer 8

Delphi sample code that I hope 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 to find out if a database connection really exists is to try to perform some operation on the connection. If the connection fails, the ConnectionState property is still "open", but when you try to 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" after this type of operation is completed and fails, so you can also check the status if you want.

Hope this helps.