Extending the ASP.NET error page (show me the SQL edition)

ASP.NET has the infamous yellow screen of death. Rumor is that it was written by “The Gu” himself. It is an incredibly useful page that allows you to quickly and easily determine why stuff went wrong, while debugging your code. You get a stack trace, including the line of code and context for your error.
There is a slight snag though. When diagnosing SQL errors this page is lacking. Even though you are provided with the exception SQL Server returned, you do not get the SQL that executed nor do you get the parameters. Often I found myself setting break points and attaching, just to get at the SQL. This is very wasteful.
So I thought … why not extend the error page. Turns out this is fairly tricky.
There are two very big problems. Firstly, DbException and derived classes do not have any idea what the CommandText is. Secondly, extending the yellow screen of death is totally undocumented.
Nonetheless, I care not for such limitations. I wanted this:

Step 1 – storing and formatting the SQL statement

The custom DbConnection we use with MiniProfiler makes tracking the failed SQL simple.
First, we extend the ProfiledDbConnection:

public sealed class RichErrorDbConnection : ProfiledDbConnection
{
#if DEBUG
        DbConnection connection;
        MiniProfiler profiler;
#endif

        public RichErrorDbConnection(DbConnection connection, MiniProfiler profiler)
            : base(connection, profiler)
        {
#if DEBUG
            this.connection = connection;
            this.profiler = profiler;
#endif
        }


#if DEBUG
        protected override DbCommand CreateDbCommand()
        {
            return new RichErrorCommand(connection.CreateCommand(), connection, profiler);
        }
#endif
} 

Next we implement a class that intercepts the exceptions and logs the SQL.

public class RichErrorCommand : ProfiledDbCommand
{ 
    public RichErrorCommand(DbCommand cmd, DbConnection conn, IDbProfiler profiler) : base(cmd, conn, profiler)
    {
    }

    void LogCommandAsError(Exception e, ExecuteType type)
    {
        var formatter = new MvcMiniProfiler.SqlFormatters.SqlServerFormatter();
        SqlTiming timing = new SqlTiming(this, type, null);
        e.Data["SQL"] = formatter.FormatSql(timing);
    }

    public override int ExecuteNonQuery()
    {
        try
        {
            return base.ExecuteNonQuery();
        }
        catch (DbException e)
        {
            LogCommandAsError(e, ExecuteType.NonQuery);
            throw;
        }
    }

    protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
    {
        try
        {
            return base.ExecuteDbDataReader(behavior);
        }
        catch (DbException e)
        {
            LogCommandAsError(e, ExecuteType.Reader);
            throw;
        }
    }

    public override object ExecuteScalar()
    {
        try
        {
            return base.ExecuteScalar();
        }
        catch (DbException e)
        {
            LogCommandAsError(e, ExecuteType.Scalar);
            throw;
        }
    }
}

During debug we now make sure we use RichErrorDbConnection as our db connection.

Step 2 – extending the error page

This was fairly tricky to discover, it lives in global.asax.cs:

protected void Application_Error(object sender, EventArgs e)
{
#if DEBUG 
    var lastError = Server.GetLastError();
    string sql = null;

    try
    {
        sql = lastError.Data["SQL"] as string;
    }
    catch
    { 
        // skip it
    }

    if (sql == null) return;

    var ex = new HttpUnhandledException("An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.", lastError);

    Server.ClearError();

    var html = ex.GetHtmlErrorMessage();
    var traceNode = "<b>Stack Trace:</b>";
    html = html.Replace(traceNode, @"<b>Sql:</b><br><br>
    <table width='100%' bgcolor='#ffffccc'>
    <tbody><tr><td><code><pre>" + sql + @"</pre></code></td></tr></tbody>
    </table><br>" + traceNode);

    HttpContext.Current.Response.Write(html);
    HttpContext.Current.Response.StatusCode = 500;
    HttpContext.Current.Response.Status = "Internal Server Error";
    HttpContext.Current.Response.End();

#endif
}

The trick here is that we use a string replace to yank in the new chunk of html.

---

I hope that future releases of the platform make both of the hacks easier to implement. It would be awesome if MVC4 shipped a template error page you can just edit. It would be awesome if the base ado.net interfaces provided a means of interception without needing a full re-implementation.

---

Reference : http://samsaffron.com/archive/2011/09/08/Extending+the+ASP.NET+error+page+show+me+the+SQL+edition