How to handle an array of SQL Error codes

Question

I have a try\catch block that handles opening a connection and inserting data into a database.

catch (SqlException ex)
            {
                string sqlError = null;
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    sqlError += "Error Message " + ex.Errors[i].Message + "\n" + "Stored Procedure " +  ex.Errors[i].Procedure + " \n " + "Line Number " + ex.Errors[i].LineNumber;
                }
                LogTools.WriteLog("Sql Server error " + sqlError);
                Variables.InstallStatusDetail = "Database connection failed";

                if (!sqlError.Contains("connection to SQL Server"))
                {
                    if (Variables.WriteToDatabase) 
                    { HostedDataBase.InsertRunStatusIntoInstallStatus('E', sqlError, null); }
                }
            }

I want to log sqlexceptions to the database that wont interfere with connecting and logging to the database. The problem occurs when the database cannot be found, or a login does not have the proper permissions, etc. The exception is raised, and it tries to log to the database, so when it does that, it calls the function that writes to the database and tries to access once again, but once again the same exception is raised, resulting in a loop of failed attempts to write to the database (either because the DSN cannot be found, or the user does not have proper permissions).

How can I handle sql errors that would prevent me from being able to access and write to the database, and at the same time still be able to write sql errors that would not cause this loop?

score 0 · Answer 1 · answered Feb 03 '17 at 22:57

0

I'm slightly confused by your question but I will attempt to answer it. You have to be careful with how you handle exceptions. If you are going to attempt to reconnect to the database even though an exception was raised the first time, you may want to have some conditions checking what sort of exception was raised. This way, you know to only attempt to re-connect if it was an exception which will not be repeated over and over.

IE.

    catch (SqlException ex)
                {
                       Error = ex.ToString()
                       WriteToLog(Error);
                       CheckError();

                }

    Void CheckError()
    {
    //conditions based on error output.
    }

void WriteToLog(string Error)
{
// write your error output to log
}

answered Feb 03 '17 at 22:57

Adam Nunez

46
1
5

Sorry for the vague question. I want to log all errors (including some sql errors) to the database. The problem is when an exception is due to the fact that the program cannot find the dsn, or it does not have permissions. The code executes itself again, and results in a loop of attempted inserts to something that cannot be found or does not have proper permissions. I want a pragmatic way of checking that before we attempt to insert an error, is the sql error something that would not interfere with writing to the database (like a bad connection, or a user with improper permissions). – Mattaceten Feb 03 '17 at 23:03
It sounds like your best route would be to create some sort of counter that is keeping track of the connect attempts and break your loop if your reach 5 for example. If it has failed 5 times, chances are it isn't going to work. So just create an INT and increment it on each connect attempt. – Adam Nunez Feb 03 '17 at 23:05
Your example makes sense, but is there a way in the CheckError method to tell whether the sql error is a bad connection or bad permissions? Or a whole list of unknown errors that could hinder database access? Obviously if the database cant be accessed, there shouldnt be an attempt to insert anything – Mattaceten Feb 03 '17 at 23:05
yes there is: http://stackoverflow.com/questions/24041062/know-when-to-retry-or-fail-when-calling-sql-server-from-c tl;dr: its complicated. – Cee McSharpface Feb 03 '17 at 23:57

score 0 · Answer 2 · answered Feb 03 '17 at 23:04

You should put your logging in it's own try..catch block, like this:

catch (SqlException ex)
{
    string sqlError = null;
    for (int i = 0; i < ex.Errors.Count; i++)
    {
        sqlError += "Error Message " + ex.Errors[i].Message + "\n" + "Stored Procedure " +  ex.Errors[i].Procedure + " \n " + "Line Number " + ex.Errors[i].LineNumber;
    }
    LogTools.WriteLog("Sql Server error " + sqlError);
    Variables.InstallStatusDetail = "Database connection failed";

    if (!sqlError.Contains("connection to SQL Server"))
    {
        if (Variables.WriteToDatabase) 
        { 
            try {
                //I am assuming this is where you are trying to write the error back into the database
                HostedDataBase.InsertRunStatusIntoInstallStatus('E', sqlError, null); 
            } catch {
                //ignore errors here
            }
        }
    }
}

score 0 · Answer 3 · answered Feb 03 '17 at 23:11

Unfortunately if you are writing to the same database that you don't have access to, I'm afraid you cannot do that.

I'd suggest you to use something like Log4net which can have multiple appenders (eventlog, database, file, etc). Also, log4net operates something like a fire and forget. Even if log4net has any issues logging the errors, it won't throw exceptions.

How to handle an array of SQL Error codes

3 Answers3