Pass value to ExceptionFilter

Question

I have a WebAPI project and am moving my exception catching from try/catch to exception filters. Previously when I used try/catch blocks I was able to access the SQL command object to get the SQL command text to log to the database. Now that I moving to exception filters I am no longer able to access the SQL command object. What I am after is a way to pass the SQL command which was last executed to the exception filter so that I can still log it.

Previous code (try/catch blocks)

public HttpResponseMessage API([FromBody] int roomID)
{
    HttpResponseMessage msg = null;
    SqlCommand comm = Common.Shared.ConnectDB();

    try
    {
        List<Models.room> room = room(comm, roomID);
        msg = Request.CreateResponse(HttpStatusCode.OK, room);
    }
    catch (CustomException ex)
    {
        msg = Request.CreateResponse(HttpStatusCode.BadRequest, ex.Message);
    }
    catch (Exception ex)
    {
        comm.Parameters.Clear();
        var sourceFunction = "rooms";
        var returnException = Common.Logging.CreateLog(ex, comm, sourceFunction);
        msg = Request.CreateResponse(HttpStatusCode.InternalServerError, returnException);
    }
    finally
    {
        comm.Connection.Close();
    }
    return msg;
}

Exception Filter code

public class InternalServerErrorFilter : ExceptionFilterAttribute
{
    public override void OnException(HttpActionExecutedContext context)
    {
        Log.Error(context.Exception, "Exception Information");

        context.Response = new HttpResponseMessage(HttpStatusCode.InternalServerError);
    }
}

Controller Exception Filter

[InternalServerErrorFilter]
[HttpPost]
public HttpResponseMessage API([FromBody] int roomID)
{
    HttpResponseMessage msg = null;
    SqlCommand comm = Common.Shared.ConnectDB();

    try
    {
        List<Models.room> room = room(comm, roomID);
        msg = Request.CreateResponse(HttpStatusCode.OK, room);
    }
    finally
    {
        comm.Connection.Close();
    }
    return msg;
}

Answer 1

There are few ways you could handle that scenario:

Check and cast Exception type into more specific exception, like SqlException(Check documentation). Inside SqlException you'll be able to find reason for that exception.

public class InternalServerErrorFilter : ExceptionFilterAttribute
{
    public override void OnException(HttpActionExecutedContext context)
    {
        if (context.Exception is SqlException)
        {
           var sqlException = (SqlException)context.Exception;
           //Do your logging...
        }
        context.Response = new HttpResponseMessage(HttpStatusCode.InternalServerError);
    }
}

Or you could create your custom sql exception, to which you'll pass your SqlCommand for further logging. However, you'll need to check type (like above) in order to get strongly-typed access.

public class CustomSqlException : Exception
{
    public SqlCommand ExecutedCommand { get; }

    public CustomException(SqlCommand executedCommand) 
        : base("Custom Message")
    {
        ExecutedCommand = executedCommand;
    }
}

public class InternalServerErrorFilter : ExceptionFilterAttribute
{
    public override void OnException(HttpActionExecutedContext context)
    {
        if (context.Exception is CustomSqlException)
        {
            var sqlException = (CustomSqlException)context.Exception;
            var command = sqlException.ExecutedCommand;
            //Do your logging...
        }
        context.Response = new HttpResponseMessage(HttpStatusCode.InternalServerError);
    }
}

usage:

[InternalServerErrorFilter]
    [HttpPost]
    public HttpResponseMessage API([FromBody] int roomID)
    {
        HttpResponseMessage msg = null;
        SqlCommand comm = Common.Shared.ConnectDB();

        try
        {
            List<Models.room> room = room(comm, roomID);
            msg = Request.CreateResponse(HttpStatusCode.OK, room);
        }
        catch (SqlException ex)
        {
           throw new CustomSqlException(comm);
        }
        finally
        {
            comm.Connection.Close();
        }
        return msg;
    }

I would suggest you use first approach, because SqlException should have all necessary information about error which ocurred while executing SQL command on SQL server. In addition to that, you will not lose your stack trace, due to throwing new exception.

If you decide to go with the second solution, you could circumvent issue of losing stack trace by passing exception as inner exception of your CustomSqlException.