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.
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.
DbLoggerOptions instance which will store the logging settings that we get from appsettings.json.
ProviderAlias attribute to the class. This is so the logger provider knows which object to read from the appsettings.json file.
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.
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.
System.Data.SqlClient.
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.
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.
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.
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:
