How to handle database exceptions/ issue?

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?

If you are running a service with no user interface, then by all means, keep on looping until things start working, but at least log the errors to the EventLog while you're at it, so that the server admin can figure out when and why things go wrong.

For a client application, I would no suggest that you make the user wait 9 minutes before telling them things are not working like they should. Try to connect, assess the error condition, and let the user know what is going wrong so that they can take it further.

If you are using the SqlException class you can check the Exception Class and decide based on that what is going wrong, for example:

switch (sqlEx.Class)
            {
                case 20:
                    //server not found
                case 11:
                    //database not found

All the classes have the SQL Server message on them, it is a matter of testing the different conditions.

It really depends on how you want your application to behave.

If your database access is dealt with on the same thread as your UI then whilst you are attempting to connect to a database it will become unresponsive.

The default time period for a connection timeout is already pretty long and so running it in a for loop 3 times would triple that and leave you with frustrated users.

In my opinion unless your specifically attempting to hide connection issues from the user, it is by far better to report back that a connection attempt has failed and ask the user if they wish to retry. Then having a count on the number of times that you'll allow a reconnection attempt before informing the user that they can't continue or putting the application into an "out of service" state.

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

Try to surround connection operation with using statement to capture connection related problems .

using( sqlcon = new SqlConnection(constr))
{}

Use the Try/Catch Statement for capturing the exception:

try
        {
            con.Open();
            try
            {
                //Execute Queries 
                // ....
            }
            catch
            {
                // command related or  other  exception
            }
            finally
            {
                con.Close();
            }
        } 
        catch
        {
            // connection error
        }

To prevent Exception of such type check these:

Troubleshooting Timeout SqlExceptions

you can set the CommandTimeout to some value on a SqlCommand:

objCmd.CommandTimeout = 600

You can catch the SqlException.

• SqlException.Class

  • Gets the severity level of the error returned from the .NET Framework Data Provider for SQL Server.

• SqlException.Errors

  • Gets a collection of one or more SqlError objects that give detailed information about exceptions generated by the .NET Framework Data Provider for SQL Server.

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = new SqlConnection("CONNECTION_STRING");
    cmd.CommandText = "SELECT * FROM ....";
    // cmd.CommandType = System.Data.CommandType.Text;
    try
    {
        cmd.Connection.Open();
        try
        {
            SqlDataReader reader = cmd.ExecuteReader();
            // ....
        }
        finally
        {
            cmd.Connection.Close();
        }
    } 
    catch (SqlException ex)
    {
        // ex.Class contains the ErrorCode, depends on your dataprovider
    
        foreach (SqlError error in ex.Errors)
        {
            // error.LineNumber
            // error.Message
        }
    }  
    

The best way would be to putt it in a try catch statement and display the error in a better format, If it fails for 1 time, trying it continue sly 3 times will not change anything untill and unless you dc and send request again, In a separate in separate packed as a new request.

use try this.

try
{
       Executing code.
}
        catch (Exception err)
        {
            Display["ErrorMsg"] = err.Message.ToString() + "|" + err.GetBaseException() + "|" + Request.Url.ToString();
        }

Good Luck.