c# - Obtain the Query/CommandText that caused a SQLException

Question

I've got a logger that records exception information for our in house applications.

When we log SQL exceptions it'd be super useful if we could see the actual query that caused the exception.

Is there a way we can achieve this?

Answer 1

The SqlException does not hold a reference to the SqlCommand that caused the exception. In your logger there is no way to do this. What you could do is catch the SqlException in the method that executes the SqlCommand and wrap it in a more descriptive exception. Example:

using (var command = new SqlCommand(connection, "dbo.MyProc"))
{
    try
    {
        command.Execute();
    }
    catch (DbException ex)
    {
        throw new InvalidOperationException(ex.Message + " - " + command.Text, ex);
    }
}

This way you can log this more expressive exception.