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

Published: Thursday 31 March 2022

One of the great things about .NET is being able to create a custom logging provider.

Creating a logging provider is relatively easy in ASP.NET Core as you can build the functionality around methods that are contained in the ILogger and ILoggerProvider interfaces.

We will have a look at creating and implementing a logging provider so it we can log to a table in a SQL Server database. The logging provider will have the functionality to choose which exception and log properties are written to the database.

How logging works in ASP.NET Core

Before we go ahead and create the logging provider, we need to understand how ASP.NET Core logging works with ILogger and how we can use it to create a custom logging provider.

To get an understanding of ASP.NET Core logging, read "How ASP.NET Core logging works with ILogger and LogLevel", which details the basics of how logging works and how to implement it into an ASP.NET Core app.

Creating the logging provider

Now that we have an understanding of how ASP.NET Core logging works, lets go ahead and create a custom logging provider to log to a SQL Server database.

Creating options

The first thing we want to do is to create a class to store our settings. These settings will be read for the application using our appsettings.json file. The settings we wish to store are:

Field Description
ConnectionString Stores the connection string to where we want to output our log files to.
LogFields An array of fields that we wish to log in our database. This will include log and exception properties.
LogTable The name of the table that we wish to insert our logs into the database.

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:

Creating logs to a database using an ILogger custom provider

Creating logs to a database using an ILogger custom provider

Looking at the values, we are storing them all as a JSON string. If we wish to query that, we can use the JSON functions that come with SQL Server. These have been available since SQL Server 2016 was launched.

Here is an example of how to query it:

select JSON_VALUE([Error].[Values], '$.LogLevel') AS LogLevel, JSON_VALUE([Error].[Values], '$.ThreadId') AS ThreadId, Created from [dbo].[Error]

When using this SQL query, the results will be displayed like this:

Use JSON_VALUE to query a JSON varchar type in SQL Server

Use JSON_VALUE to query a JSON varchar type in SQL Server

More resources

Check out our video where we demonstrate how the logger provider works in an ASP.NET Core application, and how the data looks in the database.

In-addition, download the code sample which includes the logger provider example and the database schema that is used for this sample.

Finally, read our article where we created a custom logging provider to log to text files.