How to handle database exceptions/ issue?

Question

I one of my c# application, i have written sql connection code as following

try 
{ 
   myConnection = new SqlConnection(m_resourceDB.GetResourceString(nSiteID,  ApplicationID.XClaim,(short)nResID ) ); 
   myConnection.open();
}

I want to handle unkown issue of sqlserver like database down, time out.

For this i though to introduce for loop 3 times with 3 minute sleep between loop and if at all problem is there then i will exit from loop

I don't know my though is right or not? I want some expert advice on this? Any example?

Answer 1

I would say simply: the code that talks to connections etc should not be doing a sleep/retry, unless that code is already asynchronous. If the top-level calling code wants to catch an exception and set up a timer (not a sleep) and retry, then fine - but what you want to avoid is things like:

var data = dal.GetInfo();

suddenly taking 3 minutes. You might get away with it if it is an async/callback, and you have clearly advertised that this method may take minutes to execute. But even that feels like a stretch. And if you are up at the application logic, why not just catch the exception the first time, tell the user, and let the user click the button again at some point in the future?