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:


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:


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='',  @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.
        this._WritingErrorLog = true;
        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._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();
        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)
                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() + "'");
    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.


Development | SQL | ASP.Net

Add comment

  Country flag

  • Comment
  • Preview

One Script

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