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.
4 Answers
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();
}
Comments
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.
Comments
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.
1 Comment
Gary.Ray
+1 - It's not enough to open the connection because if you are pooling connections the pool may return a connection that has in fact become invalid. This happens to us in our C# -> Oracle applications all the time because the TCP/IP connection times out while the DB Pool holds what is shown as an active connection.
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.
