Checking whether a database is available?

Question

My problem involves checking if I have a valid database connection before reading from the database. If the database is down I'd like to write to a xml file instead. I have the location of the database (if it's up) at runtime so if the database was working I can create a new sqlConnection to it.

Answer 1

You cannot really tell whether the DB is up and running without actually opening a connecting to it. But still, connection might be dropped while you're working with it, so this should be accounted for.

Answer 2

I would just use something like:

using(SqlConnection conn = new SqlConnection(c)) { conn.Open(); }

It will throw an exception if invalid. You could write to the xml in the exception.

Answer 3

An easy way would be to execute a simple query and see if an error occurs:

For Oracle:

SELECT * FROM DUAL

For SQL Server

SELECT 1

Basicly just some kind of relatively "free" query that will let you know that the database is up and running and responding to requests and your connection hasn't timed out.

Answer 4

Use a typical try...catch...finally structure, and based on the specific exception type and message, decide whether you want to write to xml or not.

try
{
SqlConnection connection = new SqlConnection(DB("Your DB Name"));
connection.Open();
}
catch (Exception ex)
{
// check the exception message here, if it's telling you that the db is not available. then 
//write to xml file.
    WriteToXml();   
}
finally
{
  connection.Close();
}