Create a custom database logging provider with ASP.NET Core ILogger

We will store these settings in a DbLoggerOptions class. Here is how the code will look:

// DbLoggerOptions.cs
public class DbLoggerOptions
{
	public string ConnectionString { get; init; }

	public string[] LogFields { get; init; }

	public string LogTable { get; init; }

	public DbLoggerOptions()
	{
	}
}

As discussed, the DbLoggerOptions properties will be read from the appsettings.json file. We will need to set these values in there which we can do like this:

{
	"Logging": {
	...
		"Database": {
			"Options": {
				"ConnectionString": "Server=localhost; Database=RoundTheCode_DbLogger; Trusted_Connection=true; MultipleActiveResultSets=true; Integrated Security=true;",
				"LogFields": [
					"LogLevel",
					"ThreadId",
					"EventId",
					"EventName",
					"ExceptionMessage",
					"ExceptionStackTrace",
					"ExceptionSource"
				],
				"LogTable": "dbo.Error"
		},
		"LogLevel": {
			"Default": "Error",
			"Microsoft.AspNetCore": "Error",
			"RoundTheCode.LoggerDb": "Error"
		}
	}
	...
}

Inside the appsettings.json file, we've created a new Database JSON object. This will represent our values inside the custom logging provider.

We have overridden the LogLevel object so it only logs where the level is Error or higher.

Creating the custom logging provider

The logging provider helps us define what we want to do when writing a log. In this instance, we want to write it to a SQL Server database. We are going to go ahead and create the logger provider class named DbLoggerProvider, which will inherit the ILoggerProvider interface.

In this class, we need to pass in the DbLoggerOptions instance which will store the logging settings that we get from appsettings.json.

We need to pass in the ProviderAlias attribute to the class. This is so the logger provider knows which object to read from the appsettings.json file.

A method that must be implemented in the ILoggerProvider interface is the CreateLogger method. This will create a new logger instance which will specify what we do when writing the log. The logger type is the next thing we will create.

[ProviderAlias("Database")]
public class DbLoggerProvider : ILoggerProvider
{
	public readonly DbLoggerOptions Options;

	public DbLoggerProvider(IOptions<DbLoggerOptions> _options)
	{
		Options = _options.Value; // Stores all the options.
	}

	/// <summary>
	/// Creates a new instance of the db logger.
	/// </summary>
	/// <param name="categoryName"></param>
	/// <returns></returns>
	public ILogger CreateLogger(string categoryName)
	{
		return new DbLogger(this);
	}

	public void Dispose()
	{
	}
}

The logger instance

The next thing to do is to create the logger. This will set the functionality for writing the log.

We will name it DbLogger, and the class must inherit the ILogger interface. There are a number of methods we need to implement from the ILogger interface. One of those is the Log method. The Log method is where we set the functionality for writing the log.

For this, we could of used an ORM tool like Entity Framework, or Dapper. But, as there is always a debate between which one to use, we've decided to use the methods from System.Data.SqlClient.

We create a new database connection with SqlConnection, and read our options to see which fields we want to output. For the fields we wish to output, we create a new JObject instance from the Newtonsoft.Json assembly, and store each one as a JToken.

Then we go ahead and create these values in the database. For the fields we wish to write, we create them as a JSON type string in a column, alongside the date for when the log was created.

Then it's a case of closing the database connection.

public class DbLogger : ILogger
{
	/// <summary>
	/// Instance of <see cref="DbLoggerProvider" />.
	/// </summary>
	private readonly DbLoggerProvider _dbLoggerProvider;

	/// <summary>
	/// Creates a new instance of <see cref="FileLogger" />.
	/// </summary>
	/// <param name="fileLoggerProvider">Instance of <see cref="FileLoggerProvider" />.</param>
	public DbLogger([NotNull] DbLoggerProvider dbLoggerProvider)
	{
		_dbLoggerProvider = dbLoggerProvider;
	}

	public IDisposable BeginScope<TState>(TState state)
	{
		return null;
	}

	/// <summary>
	/// Whether to log the entry.
	/// </summary>
	/// <param name="logLevel"></param>
	/// <returns></returns>
	public bool IsEnabled(LogLevel logLevel)
	{
		return logLevel != LogLevel.None;
	}


	/// <summary>
	/// Used to log the entry.
	/// </summary>
	/// <typeparam name="TState"></typeparam>
	/// <param name="logLevel">An instance of <see cref="LogLevel"/>.</param>
	/// <param name="eventId">The event's ID. An instance of <see cref="EventId"/>.</param>
	/// <param name="state">The event's state.</param>
	/// <param name="exception">The event's exception. An instance of <see cref="Exception" /></param>
	/// <param name="formatter">A delegate that formats </param>
	public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
	{
		if (!IsEnabled(logLevel))
		{
			// Don't log the entry if it's not enabled.
			return;
		}

		var threadId = Thread.CurrentThread.ManagedThreadId; // Get the current thread ID to use in the log file. 

		// Store record.
		using (var connection = new SqlConnection(_dbLoggerProvider.Options.ConnectionString))
		{
			connection.Open();

			// Add to database.

			// LogLevel
			// ThreadId
			// EventId
			// Exception Message (use formatter)
			// Exception Stack Trace
			// Exception Source

			var values = new JObject();

			if (_dbLoggerProvider?.Options?.LogFields?.Any() ?? false)
			{
				foreach (var logField in _dbLoggerProvider.Options.LogFields)
				{
					switch (logField)
					{
						case "LogLevel":
							if (!string.IsNullOrWhiteSpace(logLevel.ToString()))
							{
								values["LogLevel"] = logLevel.ToString();
							}
							break;
						case "ThreadId":
							values["ThreadId"] = threadId;
							break;
						case "EventId":
							values["EventId"] = eventId.Id;
							break;
						case "EventName":
							if (!string.IsNullOrWhiteSpace(eventId.Name))
							{
								values["EventName"] = eventId.Name;
							}
							break;
						case "Message":
							if (!string.IsNullOrWhiteSpace(formatter(state, exception)))
							{
								values["Message"] = formatter(state, exception);
							}
							break;
						case "ExceptionMessage":
							if (exception != null && !string.IsNullOrWhiteSpace(exception.Message))
							{
								values["ExceptionMessage"] = exception?.Message;
							}
							break;
						case "ExceptionStackTrace":
							if (exception != null && !string.IsNullOrWhiteSpace(exception.StackTrace))
							{
								values["ExceptionStackTrace"] = exception?.StackTrace;
							}
							break;
						case "ExceptionSource":
							if (exception != null && !string.IsNullOrWhiteSpace(exception.Source))
							{
								values["ExceptionSource"] = exception?.Source;
							}
							break;
					}
				}
			}


			using (var command = new SqlCommand())
			{
				command.Connection = connection;
				command.CommandType = System.Data.CommandType.Text;
				command.CommandText = string.Format("INSERT INTO {0} ([Values], [Created]) VALUES (@Values, @Created)", _dbLoggerProvider.Options.LogTable);

				command.Parameters.Add(new SqlParameter("@Values", JsonConvert.SerializeObject(values, new JsonSerializerSettings
				{
					NullValueHandling = NullValueHandling.Ignore,
					DefaultValueHandling = DefaultValueHandling.Ignore,
					Formatting = Formatting.None
				}).ToString()));
				command.Parameters.Add(new SqlParameter("@Created", DateTimeOffset.Now));

				command.ExecuteNonQuery();
			}

			connection.Close();
		}
	}
}

Configuration extension method

Our final method is an extension method that allows us to add the DbLogger to the ILoggerBuilder. This extension method will be called in the Program.cs file.

This uses dependency injection to use the DbLoggerProvider as a singleton instance. It also allows us to configure the options for the provider.

public static class DbLoggerExtensions
{
	public static ILoggingBuilder AddDbLogger(this ILoggingBuilder builder, Action<DbLoggerOptions> configure)
	{          
		builder.Services.AddSingleton<ILoggerProvider, DbLoggerProvider>();
		builder.Services.Configure(configure);
		return builder;
	}
}

Adding the logger provider to the ASP.NET Core application

Now that we have our logger provider created, we need to add it to an ASP.NET Core application. In .NET 6 applications that don't contain a namespace, class or method, we can do that by going into our Program.cs file and adding the configuration extension method like this:

// Program.cs
var builder = WebApplication.CreateBuilder(args);
 
...
builder.Logging.AddDbLogger(options =>
{
    builder.Configuration.GetSection("Logging").GetSection("Database").GetSection("Options").Bind(options);
});
 
var app = builder.Build();
 
...
 
app.Run();

Inside the AddDbLoggerProvider, we need to ensure that we are reading the options from the correct place in the appsettings.json file.

If the ASP.NET Core application does contain a namespace, class and method, the logging provider can be added inside a ConfigureLogging extension method:

// Program.cs
public class Program
{
	...
      
	public static IHostBuilder CreateHostBuilder(string[] args) =>
		Host.CreateDefaultBuilder(args)
			.ConfigureWebHostDefaults(webBuilder =>
			{
				webBuilder.UseStartup<Startup>();
			})
			.ConfigureLogging((hostBuilderContext, logging) =>
			{
				logging.AddDbLogger(options =>
				{
					hostBuilderContext.Configuration.GetSection("Logging").GetSection("Database").GetSection("Options").Bind(options);
				});
			});
}

How the logs are written in the database

Now that we have set this up, we can throw an exception in our ASP.NET Core application to check that it works. The logs in our database will be written like this: