Logging Exceptions and the Commands that Caused Them.

Logging Exceptions and the Commands that Caused Them.

by jbrooks 14. December 2010 12:12

For a long time I’ve been logging all of my exceptions to a table that looks like:

ExceptionTable

Now for exceptions that were generated by the database I want to also log the exact database call that caused the exception.  It will make debugging go a lot faster.  For this I have a new table:

ExceptionCmdTable

This will get the exceptionId from the first table and the a string that caused the exception.  Something like:

saveSomething @id=100, @UserName='Jakey',  @IPAddress='100.100.100.100',  @editBy='JBrooks'

So the first part of logging the Exception is two methods on my base class.

 
public void LogException(Exception ex)
{
    LogException(ex, null);
}
 
 
public void LogExceptionx(Exception ex, SqlCommand objBadCmd)
{
    int id = 0;
 
    if (this._WritingErrorLog == true)
    {
        // don't want to get in an infinite loop.
        return;
    }
    else
    {
        this._WritingErrorLog = true;
    }
 
 
    try
    {
 
        SqlCommand objCmd = this.GetNewCmd("dbo.insertException");
 
        string message = "";
 
        // Get the userId of the user that got the error.
        if (System.Web.HttpContext.Current.Request.Cookies["UID"] != null)
            message = System.Web.HttpContext.Current.Request.Cookies["UID"].Value + "|";
 
        // Add the command that gave the error.
        if (objBadCmd != null && !string.IsNullOrEmpty(objBadCmd.CommandText))
            message += objBadCmd.CommandText + "|";
 
 
        message += ex.ToString();
 
        if (message.Length > 8000)
            message = message.Substring(0, 7999);
 
 
        objCmd.Parameters.Add("@Message", SqlDbType.VarChar, 8000).Value = message;
 
        objCmd.Parameters.Add("@StackTrace", SqlDbType.VarChar, 8000).Value =
            ((ex.StackTrace == null) ? "no stack trace." : ex.StackTrace);
 
        //insert the exception and get the new ID.
        id = this.ExecuteScalarInt(objCmd);
 
        if (id > 0 && objBadCmd != null)
            insertExceptionCmd(id, objBadCmd);
 
    }
    catch (Exception ExLogException)
    {
        this.LogExceptionToFile(ex);
        this.LogExceptionToFile(ExLogException);
    }
 
    this._WritingErrorLog = false;
 
}

Now the part that generates the command string is another method that gets called be the method above:

 
private string getCmdString(SqlCommand objBadCmd)
{
 
    if (objBadCmd == null)
        return string.Empty;
 
    if (objBadCmd.Parameters == null || objBadCmd.Parameters.Count == 0)
        return objBadCmd.CommandText;
 
    StringBuilder sb = new StringBuilder();
 
    try
    {
        sb.Append(objBadCmd.CommandText+" ");
 
        for (int i = 0; i < objBadCmd.Parameters.Count; i++)
        {
            SqlParameter p = objBadCmd.Parameters[i];
 
            if (i > 0)
                sb.Append(", ");
 
            sb.Append(p.ParameterName + "=");
 
            if (p.Value == null)
                sb.Append("null");
            else
            {
                switch (objBadCmd.Parameters[i].SqlDbType)
                {
                    case SqlDbType.BigInt:
                    case SqlDbType.Int:
                    case SqlDbType.Float:
                    case SqlDbType.Decimal:
                    case SqlDbType.SmallInt:
                    case SqlDbType.Money:
                    case SqlDbType.Image:
                    case SqlDbType.Real:
                    case SqlDbType.SmallMoney:
                    case SqlDbType.TinyInt:
                    case SqlDbType.DateTimeOffset:
                        sb.Append(p.Value.ToString());
                        break;
 
                    default:
                        sb.Append("'" + p.Value.ToString() + "'");
                        break;
 
                }
            }
        }
    }
    catch (Exception ex)
    {
        return "getCmdString created its own error processing:  " + 
            sb.ToString() + "      +++++" + ex.ToString().Substring(0, 8000);
    }
 
    return sb.ToString();
}

So now I have a complete logging of exception in my applications and it is a simple matter to make a page where I can view them, or make an RSS feed where they are sent to me.

Tags:

Development | SQL | ASP.Net

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

One Script

OneScript Continuous Integration for you database Scripts in version control are automatically combined into a release script.

RecentPosts