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.

Answer 2

You can NOT throw a sql exception. I think he meant to throw a new Exception that contains the command.CommandText.

Answer 3

As a simple hack you can also add the sql as part of the Data of the exception. This will preserve the original exception but also give the additional sql message.

using (var command = new SqlCommand(connection, "dbo.MyProc"))
{
   try
   {
      command.Execute();
   }
   catch (SqlException ex)
   {
      ex.Data.Add("Sql",command.Text);
      throw ex
   }
}