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:

rich error

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.


EDIT Just updated the code sample following the comments by Keith Henry and Nigel, thanks!

Comments

Adam about 3 years ago
Adam

Could you not have caught the exception and rethrown it with a new Exception appending the sql to the message and passing the original exception in as the inner exception???

Sam Saffron about 3 years ago
Sam Saffron

yeah I did that originally ... very risky, strongly recommend against. We have internal code that handles stuff like SqlException deadlocks etc, wrapping it up means you now have to check for another exception type. Also, it is much less readable than my screenshot.

Matthew_Abbott about 3 years ago
Matthew_Abbott

Good call on leaving it with #IF DEBUG, otherwise there would be some crying sysadmins out there ;–)

Petr about 3 years ago
Petr

Hi, it looks useful,
is it possible to use it with EF CodeFirst?
Thanx

Sam Saffron about 3 years ago
Sam Saffron

Probably, but you would need to extend the provider wrapper or hack some MiniProfiler internals.

Zac about 3 years ago
Zac

Great stuff Sam.

Ian about 3 years ago
Ian

Thre seems to be broken link to MiniProfiler – or am I missing something?

Sam Saffron about 3 years ago
Sam Saffron

yeah ... oops, fixed

Darryl about 3 years ago
Darryl

Surely you test your SQL queries in SQL Server Manager before copying them to your stored procedure? Makes this suggestion redundant.

Doga about 3 years ago
Doga

@Darryl: of course you test your sql queries but the value of this suggestion isn't that it will show you broken sql statements, you should have caught those before release but data issues. when you can see the values of the parameters causing the issue, you can take that offline and inspect why it becomes an issue.

An excellent post in my opinion, now to find out more about this miniProfiler

Aharon about 3 years ago
Aharon

You might want to use the RaiseError within the SQL.
This will produce the exact same messeges without all the changes you have provided.
You will have to surround your SP in this case with a try and catch.

This works in sql server
e.g

BEGIN TRAN ACTIONS


begin try
SET @sql = ''
EXEC(@sql)
SET @sql = ''
EXEC (@sql)
COMMIT TRANSACTION ACTIONS
end try
begin catch
ROLLBACK TRANSACTION ACTIONS
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity INT
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
* RAISERROR*(@ErrMsg, @ErrSeverity, 1)
return;
end catch
Sam Saffron about 3 years ago
Sam Saffron

I am failing to see how this would help, for one it requires extensive changes throughout your codebase that would probably effect production.

Jalpesh_Vadgama about 3 years ago
Jalpesh_Vadgama

Hello Sam,

Great Work once Again!!!

Best Regards,
Jalpesh

Keith about 3 years ago
Keith

Excellent idea, but couldn't the SQL detail be a property of the exception rather than in the global HTTP cache?

If you do:

exception.Data.Add(“SQL”, formatter.FormatSql(timing));

Then in Application_Error you'd read lastError.Data instead of the HTTP page cache.

This would be more extensible too, as content other than SQL could be added for other calls.

Sam Saffron about 3 years ago
Sam Saffron

nice ...will try it out and amend this sample

Keith about 3 years ago
Keith

One other small thing – you've called Server.ClearError(), so now the page can continue processing but without the error set.

This results in the page having a 200 HTTP status code, instead of a 500 one, and other page processes still continuing.

I'd add this to the end of the Application_Error method:

   HttpContext.Current.Response.StatusCode = 500;


HttpContext.Current.Response.Status = "Internal Server Error";
HttpContext.Current.Response.End();
Sam Saffron about 3 years ago
Sam Saffron

true very good point ... will amend as well

Matthew_Moody about 3 years ago
Matthew_Moody

Not knocking MVC but, what would you instead of ProfileDbConnection for Web Forms

Sam Saffron about 3 years ago
Sam Saffron

same pattern exactly will work for webforms, ProfiledDbConnection live in MiniProfiler

Behtash_Moradi about 3 years ago
Behtash_Moradi

Good Idea & implementation
Thanks

Nigel about 3 years ago
Nigel

In the ExecuteScalar method shouldn't this bit:

LogCommandAsError(ExecuteType.Scalar);

be in the catch not the try block?

Sam Saffron about 3 years ago
Sam Saffron

good catch, fixed


comments powered by Discourse