A single method throws same type of exception for different reasons in C#-CodePudding

I have a method UpdateSQL() which may throw exception of same type (SqlException) for two or more different unrelated reasons. Reason 1 = "Invalid connection string" while executing sqlConn.open() . Reason 2 = "Some error while executing Stored Procedure" while executing sqlCommand.ExecuteNonQuery(). How do I identify the reason for which the SqlException was thrown at the caller method so that I could log a custom reason?

Caller Method

    try
    {
      UpdateSQL();
    }
    catch(SqlException e)
    {
      // How do i know the reason for which exception was thrown so I could log
      log.LogError(e, "Reason");
    }

Update method -

    UpdateSQL()
    {
      using (var sqlConn = new SqlConnection("myConnString"))
           {
             sqlConn.Open(); // May throw exception for reason 1
             SqlCommand sqlCommand = new SqlCommand("myStoredProcedure", sqlConn);
             sqlCommand.CommandType = CommandType.StoredProcedure;
        
             // Some random parameter
             SqlParameter myParam = sqlCommand.Parameters.AddWithValue("@Time", "3/10/2015 2:15:10 AM");
             myParam.SqlDbType = SqlDbType.NVarChar;
          
             sqlCommand.ExecuteNonQuery(); // May throw exception for reason 2
           }
    }

One possible method that I can see is that wrapping the two Sql commands itself in separate try-catch blocks at the Update method. But is there a way to avoid this?

CodePudding user response：

When it comes to logging especially I would avoid doing any custom logic around SqlException and just personally log the Error Message Error number for lookup. The reason for not doing this is because logging should be relatively simple and you should not write logic you have to further test.

Typically just log something similar to what the docs share. Here is a more condensed version I typically would use in my logs.

Logger.Log(String.Join(Environment.NewLine,
   exception.Errors.Select( (error, i) => $"Error [{i}]: {Error.Message} : Error Number {error.Number}" ));

If you need to perform actual business logic there is not really a great way to handle this other than be pretty explicit and use the error numbers to branch to different logic based on error numbers directly or error number ranges. Something like this should get that job done reasonably well.

try
{
   // do Sql stuff here
}
catch(SqlException sqlException) when (e.Errors.Select(e => e.Number).Contains(new []{25})
{
   // Log Something about bad connection string.. 
   // it happens to be 25 from what I've seen 
   // which tbh I cant find the docs for :(
}
catch(SqlException sqlException) when (e.Errors.Select(e => e.Number).Contains(new []{1,2,3})
{
   // Log Some other kind of Error
}