Obtain the Query/CommandText that caused a SQLException

I've got a logger that records exception info开发者_高级运维rmation 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?

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.

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

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
   }
}

The DRYest way to do this is to write a helper method that takes a delegate, the sql command text, and optionally a sql parameter array if you're using parameterized queries. Wrap the delegate in a try catch block and call the LogError method when there is an exception:

protected virtual TResult ExecuteAndLogError<TResult>(Func<TResult> code, string sql, SqlParameterCollection parameters = null)
{
    try {
        if ((System.Diagnostics.Debugger.IsAttached))
            PrintSqlToDebug(sql, parameters);
        return code();
    } catch (Exception ex) {
        LogError(sql, parameters, ex);
        throw;
    }
} 

In my SQL code I call ExecuteAndLogError from data layer helper methods. All of the data layer methods call ExecuteAndLogError, so there is only one section of code to log SQL errors.

public virtual DataTable ExecuteDataTable(SqlCommand command, params SqlParameter[] parameters)
{
    command.Parameters.AddRange(parameters);
    DataTable table = new DataTable();

    using (SqlDataAdapter adapter = new SqlDataAdapter(command)) {
        using (command) {
            ExecuteAndLogError(() => adapter.Fill(table), command.CommandText, command.Parameters);
        }
    }

    return table;

}

You can use it like this: repo.ExecuteDataTable("SELECT * FROM Users"); If there is an exception you can implement the LogError method to perform additional logging.

Some of this code was taken from the Subtext Blog data layer classes.