Create a custom database logging provider with ASP.NET Core ILogger
source link: https://www.roundthecode.com/dotnet/create-custom-database-logging-provider-asp-net-core-ilogger
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Create a custom database logging provider with ASP.NET Core ILogger
31st March 2022One 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:
FieldDescriptionConnectionStringStores the connection string to where we want to output our log files to.LogFieldsAn array of fields that we wish to log in our database. This will include log and exception properties.LogTableThe 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
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
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK