Calling SQL Procedure for another DB in the C# Catch Exception clause

Question

Along with logging the SqlException, I'm also trying to call another DB which is storing the content from where the error initially occured. However, I'm getting the warning 'Unreachable code detected' for the SqlConnection part and it definitely isn't executing the Procedure that I'm attempting to run.

catch (SqlException ex)
{
     throw new DataException(ex.Message, ex);

     //Call Error Management DB Connection and add content to the table
     using (SqlConnection connection = new SqlConnection(_errorManagementConnectionString))
     {
          SqlCommand cmd = new SqlCommand("[dbo].[InsertDataErrors]", connection);
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.AddWithValue("@InputParam", InputParam);
          cmd.Parameters.AddWithValue("@Content", Content);
          cmd.ExecuteNonQuery();
          connection.Open();
     }
}

How can I tackle this error and ensure that the SqlException is logged along with the procedure that I'm trying to run?

You're throwing a `new DataException` before the `using`-part so the using is unreachable. — MatSnow, Oct 11 '17 at 13:37
I have tried the other way, it then doesn't throw the new DataException if I put the using beforehand. — Dev, Oct 11 '17 at 13:40
Also you should use [`Add`](https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue) instead of [`AddWithValue`](https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue). — MatSnow, Oct 11 '17 at 13:46

Chris Mack · Accepted Answer · 2017-12-08T22:11:31.603

You need to throw the exception at the end of the catch block.

You could also wrap the using statement in another try/catch if you're concerned about that failing.

Also, you need to open the connection before you execute the SqlCommand. This is why your DataException wasn't being thrown, another unhandled exception was being thrown before your code could reach that line.

E.g.:

catch (SqlException ex)
{
    try
    {
        //Call Error Management DB Connection and add content to the table
        using (SqlConnection connection = new SqlConnection(_errorManagementConnectionString))
        {
            SqlCommand cmd = new SqlCommand("[dbo].[InsertDataErrors]", connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@InputParam", InputParam);
            cmd.Parameters.AddWithValue("@Content", Content);
            connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
    catch
    {
        // Add any desired handling.
    }

    throw new DataException(ex.Message, ex);
}

Thank you! Adding the try-catch clause and displaying the Exception in Event Viewer pointed out the error in that new SQL Connection. — Dev, Oct 12 '17 at 10:34

score 0 · Answer 2 · answered Oct 11 '17 at 13:51

You need to put the throw at the end of the block.

Your error handling code is failing, which is why you think it should be at the top - probably because you are not opening the connection as elsewhere.

You also need to catch possible exceptions that occur when you attempt to log the error in the DB, because there are quite alot of cases where that would fail if the first database error occured.

Then you end up with a really messy exception handler, so you probably then should move it to another method.

           // somehting with database ....
      }
      catch (SqlException ex)
      {
           AttemptToLogDbError(ex)
           throw new DataException(ex.Message, ex);
      }

      // ...

 }

 void AttemptToLogDbError(SqlException ex)
 {
      try
      {
           //Call Error Management DB Connection and add content to the table
           using (SqlConnection connection = new SqlConnection(_errorManagementConnectionString))
           {
                connection.open();
                SqlCommand cmd = new SqlCommand("[dbo].[InsertDataErrors]", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@InputParam", InputParam);
                cmd.Parameters.AddWithValue("@Content", Content);
                cmd.ExecuteNonQuery();
           }
      }
      catch (Exception err)
      {
           // OMG nothing is working at all! log / report this somehow, but do not throw
      }
 }

Calling SQL Procedure for another DB in the C# Catch Exception clause

2 Answers2