Reply to Reddit Post titled, "How do you log Error in Database?"

This blog post is a reply to a Reddit thread I found here: https://www.reddit.com/r/dotnet/comments/1fndiqp/how_do_you_log_error_in_database/

Here's the original post:

As the title suggest, I was storing error log in PostgreSQL database using Serilog. But I am not quite satisfied with the result. And Serilog.PostgreSQL NuGet packages are not getting updated.

I want to know how .NET community stores error logs in database. Do you use with EF Core or Serilog. Any suggestion and advice regarding this topic is welcome.

I intended to paste this markdown as a reply to the post, but I guess it was too long because I got a generic "Unable to create comment" error whn trying to post. Here's my original reply:

I usually just store error data in my centralized ActivityLog table in my database. In order to conserve resources in my DB, I store the error details + stack trace in a text file as JSON. I also make sure all error reports get emailed to an administrator so that we know when errors are happening before they become more widespread. Here’s my code if it helps:

Here's my ActivityLog entity:

using System.ComponentModel.DataAnnotations.Schema;

namespace LymeStackApi.Models;

/// <summary>
/// The ActivityLog class is intended to be a centralized logging system
/// for the Application.
/// </summary>
[Table("ActivityLog")]
public class ActivityLog
{
    /// <summary>
    /// The primary key. It's very conceivable that this field may need
    /// to be changed to a long or bigint data type.
    /// </summary>
    public int Id { get; set; }

    /// <summary>
    /// The activity being performed. Make sure this doesn't 
    /// value doesn't change for similar activities. Put specifics
    /// in the Notes or Data properties.
    /// 
    /// FUTURE: Will probably refactor this into an ActivityType
    /// table so that it's more efficient and more meaningful.
    /// </summary>
    public string? Activity { get; set; }

    /// <summary>
    /// Specific details about the activity that area human-readable.
    /// </summary>
    public string? Notes { get; set; }

    /// <summary>
    /// Sometimes activities have technical details that are only 
    /// interesting to a developer. (e.g. SQL Statements)
    /// </summary>
    public string? DeveloperNotes { get; set; }

    /// <summary>
    /// The date of occurrence.
    /// </summary>
    public DateTime DateEntered { get; set; }

    /// <summary>
    /// This column really can contain any data, but is usually 
    /// used to store JSON data and is meant to be machine-readable.
    /// </summary>
    public string? Data { get; set; }

    /// <summary>
    /// If the activity is related to any particular table or entity 
    /// it can be noted in this column. This makes querying activities 
    /// based on entity much easier since the Data column is not easily
    /// queryable or indexable.
    /// </summary>
    public string? ReferenceTable { get; set; }

    /// <summary>
    /// If the activity is related to any particular table or entity
    /// the ID of that entity can be stored in this column. Most keys 
    /// in the system are int primary keys, this field is of that type.
    /// In the rare occurence that it might not be, use the Data 
    /// property.
    /// </summary>
    public int? ReferenceId { get; set; }

    /// <summary>
    /// The ID of the user (in the UserInfo table) that is associated 
    /// with this activity.
    /// </summary>
    public int? UserInfoId { get; set; }

    /// <summary>
    /// EF Navigation Property
    /// </summary>
    public UserInfo UserInfo { get; set; }

    /// <summary>
    /// Foreign key to the EmailMessage table.
    /// </summary>
    public int? EmailMessageId { get; set; }

    #region Constructors

    public ActivityLog(string activity)
    {
        DateEntered = DateTime.UtcNow;
        Activity = activity;
    }

    public ActivityLog(string activity, int? userInfoId)
    {
        DateEntered = DateTime.UtcNow;
        Activity = activity;
        UserInfoId = userInfoId;
    }

    public ActivityLog(string activity, int? userInfoId, string referenceTable, int referenceId)
    {
        DateEntered = DateTime.UtcNow;
        Activity = activity;
        UserInfoId = userInfoId;
        ReferenceTable = referenceTable;
        ReferenceId = referenceId;
    }

    #endregion
}

In my appsettings.json:

"ApiConfig": {
  "environment": "Production",
  "dataDirectory": "C:/AppData/YourAppDataDirectory/",
  "errorHandling": {
    "emailErrorLogs": true,
    "emailRecipients": "youradmin@yourdomain.net, anotherguy@yourdomain.com",
    "emailSubject": "Automated Error Notification",
    "emailFromAddress": "it@yourdomain.com"
  },
},

Program.cs:

...

var app = builder.Build();

app.UseExceptionHandler(exceptionHandlerApp =>
{
    exceptionHandlerApp.Run(async context =>
    {
        context.Response.StatusCode = StatusCodes.Status500InternalServerError;
        context.Response.ContentType = MediaTypeNames.Text.Plain;

        var db = new DataContext(builder.Configuration);
        var apiConfig = builder.Configuration.GetSection("ApiConfig").Get<ApiConfig>();
        var errorLogging = new ErrorLogging(db, apiConfig, context);
        var exceptionHandlerFeature =
                    context.Features.Get<IExceptionHandlerFeature>();

        var ex = exceptionHandlerFeature?.Error;

        var errorData = errorLogging.GetErrorData(ex, context.User.Identity?.Name);
        var errorEmail = errorLogging.SendErrorEmail(errorData);
        errorLogging.CreateErrorActivityLog("Server Error", errorData, context.User.Identity?.Name, errorEmail);

        context.Response.StatusCode = StatusCodes.Status500InternalServerError;
        context.Response.ContentType = MediaTypeNames.Text.Html;
        string html = errorLogging.GetMessageBody(errorData).ToString();
        await context.Response.WriteAsync(html);
    });
});

My ErrorLogging.cs class:

using LymeStackApi.Data;
using LymeStackApi.Models;
using LymeStackApi.Models.AppConfig;
using System.Net.Mail;
using System.Text;

namespace LymeStackApi.Code;

public class ErrorData
{
    public string User { get; set; }
    public string ErrorMessage { get; set; }
    public string IpAddress { get; set; }
    public string UserAgent { get; set; }
    public string Url { get; set; }
    public Exception Exception { get; set; }
}

/// <summary>
/// Provides methods to be used for error reporting including the automated error reporting configured in our API.
/// </summary>
public class ErrorLogging(DataContext db, ApiConfig apiConfig, HttpContext context)
{
    /// <summary>
    /// Creates an error log for an Exception
    /// </summary>
    /// <param name="ex"></param>
    /// <param name="username"></param>
    public dynamic GetErrorData(Exception ex, string username)
    {
        var remoteIpAddress = context.Request.HttpContext.Connection.RemoteIpAddress;
        var userAgent = context.Request.Headers["UserInfo-Agent"].ToString();

        var sb = new StringBuilder();
        sb.Append(context.Request.IsHttps ? "https://" : "http://");
        sb.Append(context.Request.Host);
        sb.Append(context.Request.Path);
        if (!string.IsNullOrEmpty(context.Request.QueryString.Value))
            sb.Append(context.Request.QueryString.Value);

        var errorData = new ErrorData
        {
            ErrorMessage = ex.Message,
            User = username,
            IpAddress = remoteIpAddress?.ToString(),
            UserAgent = userAgent,
            Exception = ex,
            Url = sb.ToString()
        };

        return errorData;
    }

    public void CreateErrorActivityLog(string activity, ErrorData errorData, string username, EmailMessage emailMessage)
    {
        var userInfoId = new int?();
        if (!string.IsNullOrEmpty(username))
        {
            var userInfo = db.UserInfos.Single(x => x.Username == username);
            userInfoId = userInfo.Id;
        }

        var activityLog = new ActivityLog(activity, userInfoId)
        {
            EmailMessageId = emailMessage.Id
        };

        db.ActivityLogs.Add(activityLog);

        try
        {
            db.SaveChanges();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }

        // ZOMBIE: We save this to an email message body file, so this is redundant. Commenting for now.
        //var saveFolder = _apiConfig.ErrorHandling.DataDirectory;
        //if (!Directory.Exists(saveFolder)) Directory.CreateDirectory(saveFolder);
        //var savePath = Path.Combine(saveFolder, $"{activityLog.Id}.json");
        //File.WriteAllText(savePath, JsonConvert.SerializeObject(errorData));
    }

    public EmailMessage SendErrorEmail(ErrorData errorData)
    {
        if (string.IsNullOrEmpty(apiConfig.ErrorHandling.EmailRecipients) || !apiConfig.ErrorHandling.EmailErrorLogs) return null;
        var msg = new MailMessage();
        msg.To.Add(apiConfig.ErrorHandling.EmailRecipients);
        msg.From = new MailAddress(apiConfig.ErrorHandling.EmailFromAddress);
        msg.Subject = $"{apiConfig.ErrorHandling.EmailSubject} - {apiConfig.Environment}";
        msg.IsBodyHtml = true;
        msg.Body = GetMessageBody(errorData);

		// Sends an email message and logs to an EmailMessage entity table.
		// NOTE: Since email bodies can be quite large, my email helper stores the body of the email to a text file.
        var emailHelper = new EmailHelper(db, apiConfig.EmailSettings);
        return emailHelper.SendMessage(msg);
    }

    public string GetMessageBody(ErrorData errorData)
    {
        var sb = new StringBuilder();
        sb.AppendFormat("<div>Error Message: {0}</div>", errorData.Exception.Message).AppendLine();
        sb.AppendFormat("<div>Remote IP Address: {0}</div>", errorData.IpAddress).AppendLine();

        sb.AppendFormat("<div>UserInfo: {0}</div>",
            string.IsNullOrEmpty(errorData.User)
                ? "n/a"
                : errorData.User).AppendLine();

        sb.AppendFormat("<div>URL: {0}</div>", errorData.Url);
        sb.AppendLine();
        sb.AppendLine("<div>EXCEPTION INFO</div>");
        sb.AppendLine("<hr /><pre>");
        sb.Append(GetExceptionString(errorData.Exception));
        sb.Append("</pre><br />");

        var innerEx = errorData.Exception.InnerException;
        while (innerEx != null)
        {
            sb.AppendLine("INNER EXCEPTION");
            sb.AppendLine("<hr /><pre>");
            sb.AppendLine(GetExceptionString(innerEx));
            sb.Append("</pre>");
            innerEx = innerEx.InnerException;
        }

        return sb.ToString();
    }

    private static string GetExceptionString(Exception ex)
    {
        var sb = new StringBuilder();
        sb.AppendFormat("Source: {0}", ex.Source).AppendLine();
        sb.AppendFormat("Message: {0}", ex.Message).AppendLine();
        sb.AppendFormat("Stack Trace: {0}", ex.StackTrace.Replace("\r", "")).AppendLine();
        return sb.ToString();
    }
}

There are a few gaps to fill in regards the the EmailHelper mechanism, but hopefully you get the gist of it.

Hope this helps you or anyone else!