Custom ELMAH diagnostics

For some time we have been using the excellent ELMAH project (Error Logging Modules and Handlers for ASP.NET) to provide invaluable diagnostics when our ASP.NET web applications generate unhandled exceptions. However I came across a situation today where the stack trace and server variables were just not enough to chase down the issues.

Dynamic SQL is sometimes a necessary evil and has a nasty bite when you don't get it just right. Without going into all the gory detail a feature request on one of our internal applications necessitated a disproportionate amount of SQL refactoring and the SQL being refactored was dynamic. Nobody likes to discover the code they have released after testing is flawed, but at least ELMAH had our back promptly notifying us that not was all well.

System.InvalidCastException: Object cannot be cast from DBNull to other types.

Generated: Wed, 02 May 2012 09:44:28 GMT

System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. System.InvalidCastException: Object cannot be cast from DBNull to other types.
   at System.DBNull.System.IConvertible.ToDateTime(IFormatProvider provider)
   at QuoteManagement.Quotes.GetQuotesFromDB(SqlCommand SqlDBCommand) in E:\projects\QuoteManagement\Classes\clsQuotes.vb:line 193

Looking at the code the problem was clear, an object property was being passed a DBNull value, however there was no valid circumstance when the SQL should be returning DBNull for that column in the recordset. It was clear that the re-factoring was causing the error condition as this column was now being derived from a sub-query. But how was I going to catch the dynamically built SQL string? I would be great if we could get it into the ELMAH error notification.

It turns out that this is relatively easy:

• use a try...catch block to handle the original exception
• build a message string containing the custom diagnostics that you want to pass to ELMAH
• throw a new exception using the message string and the original exception as the inner exception

Here is the updated application code. The dynamic SQL is built as a parametrised SQL string so this and the parameters collection are included in the message text.

Private Shared Function GetQuotesFromDB(ByVal SqlDBCommand As SqlCommand) As Quotes

 Dim MyReader As SqlClient.SqlDataReader

 Dim MyConnection As New SqlClient.SqlConnection
 MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("AppDB").ConnectionString

 SqlDBCommand.Connection = MyConnection

 Try

  MyConnection.Open()
  MyReader = SqlDBCommand.ExecuteReader()

  If MyReader.HasRows Then

   '[....]
   
   '# this is where the exception occurred 
   MyQuote.Entered = Convert.ToDateTime(MyReader("audit_date"))
   
   '[....]

  End If

 Catch e As Exception

  Dim ErrorText As New StringBuilder

  ErrorText.AppendLine("SQL Parameters:")

  For Each Param As System.Data.SqlClient.SqlParameter In SqlDBCommand.Parameters

   ErrorText.AppendLine(String.Format("{0} : {1}", Param.ParameterName, Param.Value.ToString))

  Next

  ErrorText.AppendLine("CommandText:")
  ErrorText.AppendLine(SqlDBCommand.CommandText)

  Throw New Exception(ErrorText.ToString, e)

 Finally

  If Not MyConnection Is Nothing Then
   MyConnection.Close()
  End If

 End Try

End Function

On this occasion there was no reasonable way to handle the exception so we still allowed the user to be passed to the web application's custom error page. If the error is non-catastrophic and can be handled then it is still possible to capture the original exception condition to ELMAH using ELMAH's signalling feature:

Try
 
 '# operation that generates an exception
 '# that we can recover from
 
Catch e As Exception

 '# exception handling code
 
 Dim ErrorText As New StringBuilder
 
 '# build error text then send text and orignal error to ELMAH

 Elmah.ErrorSignal.FromCurrentContext().Raise(New Exception(ErrorText.ToString, e))

End Try