Logging is one of the most important parts in .NET ecosystem and any developer should interact with it to diagnosing the information for applications healthy purposes.
Logging the errors that are happening when the applications shipped to production environment is very important and for that ASP.NET Core has a built-in logging provider, and there is more than one provider that you can use to collect the information during development or production lifecycle, here is a list of the part of logging provider that are shipped with the .NET SDK:

Console
EventLog
Debuge
EventSource
TraceSource

There are also more than one a third-party provider, that are dependent on the built-in one, and you can use any of them in your applications:

NLog
Serilog

Here, we’re going to build our custom logging provider that is dependent on the built-in one but in our provider, we’re going to collect the information and save it in the Database.
To log and save the data in the database we need a table that can help us to hold and query these data for a later time.
Fire your visual studio and create an empty ASP.NET Core project and give it a name DevDbLogger like the following picture:

In the root of the project that you're created int the above step, add a new folder named Advanced and inside this new folder create a new folder named CustomLogging.

I’m going to use the Opting pattern to help me to read some data from the configuration file and in this case the configuration file is appsettings.json or any other type of the configuration files that are available in the .NET applications, so create a new class inside CustomLogging folder named CustomDataBaseLoggerOptions and this class has the following signature:

 public class CustomDataBaseLoggerOptions
 {
     public string ConnectionString { get; set; } = default!;
     public string LogToTable { get; set; } = default!;
     public string TableSchema { get; set; } = default!;
 }

The CustomDataBaseLoggerOptions object has three properties the first one is the ConnectionString and the purpose of this property is to let our log provider connecting to and existing db. The second property LogToTable is the name of the table that we're going to save our log information inside it. The third and last property is TableSchema and this should be the schema name that our table belong to. (By design none of these properties should be null).

In .NET (generally) to create a custom logging provider you must implement two interfaces:

ILoggerProvider.
ILogger.

The purpose of the ILoggerProvider interface is to create an instance of the concreate object that implement the ILogger interface.
The ILogger interface is used to log the data that we're going to save in the Database.
To implement these two interfaces, we need two classes, but before that let us first create our Database and the Table that we're going to save the logging data inside it.

Here I' using SQL Server, so create a new Database named DevDbLogger_DataBase. After creating the database successfully create a new table inside this database named Logs. The schema of the table like the following:

USE [DevDbLogger_DataBase]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Logs](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LogLevel] [int] NOT NULL,
    [LogLevelText] [nvarchar](max) NOT NULL,
    [Message] [nvarchar](max) NOT NULL,
    [TimeStamp] [datetime2](7) NOT NULL,
    [Exception] [nvarchar](max) NULL,
    [IpAddress] [nvarchar](max) NULL,
 CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, 
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Here I'm using SQL Server Express 2019!

Now our Database and table is ready to save our logs data, let us turn our focus to implementing the ILoggerProvider and the ILogger interfaces, back to our application (DevDbLogger) create a new class inside CustomLogging folder named CustomDataBaseLoggerProvider this class should be inherent from ILoogerProvider interface.

As I said before the purpose of the ILoggerProvider interface is creating an instance of the object that implement the ILogger interface, so create a new class inside CustomLogging folder named CustomeDataBaseLogger.

Before doing anything, we need a package that can help us to interact with the Database that we're creating recently. From the NuGet package manager search for Microsoft.Data.SqlClient package and install it. (see the following pic)

Open the CustomDataBaseLoggerProvider class and let it implement the ILoggerProvider interface like so:

    public class CustomDataBaseLoggingProvider : ILoggerProvider
    {
        public readonly CustomDataBaseLoggerOptions Options;

        public CustomDataBaseLoggingProvider(
              IOptions<CustomDataBaseLoggerOptions> options)
        {
            Options = options.Value;
        }

        public ILogger CreateLogger(string categoryName)
        {

            return new CustomeDataBaseLogger(this);
        }

        public void Dispose()
        {

        }
    }

This is class is pretty easy to grasp the idea behind it, the important member of this class is the CreateLogger method, which comes from the ILoggerProvider interface, and our logger here is the CustomeDataBaseLogger and this method accept one parameter of type CustomeDataBaseLogger (we're going to implement this class shortly), as you can see from the signature of this class, I injected the CustomDataBaseLoggerOptions in the constructor to read the required values such as Database name and the Table name (that we're creating above).

Now we're ready to implement the ILogger interface in CustomeDataBaseLogger class, the ILogger interface has three members like so:

void Log<TState>(LogLevel logLevel, 
EventId eventId, 
TState state, 
Exception? exception, 
Func<TState, Exception?, string> formatter);

bool IsEnabled(LogLevel logLevel);

IDisposable? BeginScope<TState>(TState state) where TState : notnull;

The first method is the Log which is contains the information that we're going to send it to our Logs table.
The second one is IsEnabled and it will check the state of the log level is it enabled or not, and here you have to decide at which level you would like your application should collect the data and save it in the database.
The third and the last method BeginScope is creating a scope of the type that is written in the logger level.

Before implementing the ILogger interface let us review the data that we're going to collect, if you look at the signature of the Logs table you will realize that we have seven (7) columns:

Id: the primary key of the table (Logs).
LogLevel: at which level the application creates this log.
LogLevelText: save the log level as a text (ex: Debug, etc...).
Message: the message of the log.
TimeStamp: at which time we logged this message.
Exception: the exception (if any).
IpAddress: the Ip address of the user that use our application.

From the a above columns, we have one column name LogLevelText, this will save the log level as text and because the type of the LogLevel in .NET is Enum so we need a helper method to convert the level of the log to the string and save that value in the **LogLevelText **column.
Here is the helper method that will accomplish our job:

    public class CustomeDataBaseLogger
    {
      // other implementations goes here...

        private string ConvertLogLevelToTxtString(LogLevel logLevel)
        {
            string result = logLevel switch
            {
                LogLevel.Critical => "CRITICAL",
                LogLevel.Warning => "WARNING",
                LogLevel.Information => "INFORMATION",
                LogLevel.Debug => "DEBUG",
                LogLevel.Trace => "TRACE",
                LogLevel.Error => "ERROR",
                _ => logLevel.ToString().ToUpper(),
            };

            return result;
        }
    }

Now let our CustomeDataBaseLogger class inherit from the ILogger interface, here is the complete signature of this class. (Don't worry I will explain it in detail)

 public class CustomeDataBaseLogger : ILogger
 {
     private readonly CustomDataBaseLoggingProvider _dbLoggerProvider;
     public CustomeDataBaseLogger([NotNull] CustomDataBaseLoggingProvider dataBaseLoggerProvider)
     {
         _dbLoggerProvider = dataBaseLoggerProvider;
     }

     public IDisposable BeginScope<TState>(TState state) where TState : notnull
     {
         return default!;
     }

     // ToDo TO set min level to log
     public bool IsEnabled(LogLevel logLevel)
     {
         return logLevel != LogLevel.None;
     }

     public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception? exception, Func<TState, Exception?, string> formatter)
     {
         if (!IsEnabled(logLevel))
         {
             // Don't log anything to the back store the logLevel it's not enabled.
             return;
         }

         // validation
         if (formatter == null)
         {
             throw new ArgumentNullException(nameof(formatter));
         }

         string message = formatter(state, exception);
         int lg = (int)logLevel;
         string lgTxt = ConvertLogLevelToTxtString(logLevel);
         using (var con = new Microsoft.Data.SqlClient.SqlConnection(_dbLoggerProvider.Options.ConnectionString))
         {
             using (var command = new Microsoft.Data.SqlClient.SqlCommand())
             {
                 con.Open();
                 command.Connection = con;
                 command.CommandType = System.Data.CommandType.Text;
                 command.CommandText = string.Format(
                     @"INSERT INTO {0}.{1} (LogLevel, LogLevelText, Message, TimeStamp, Exception, IpAddress) 
                         VALUES (@LogLevel, @LogLevelText, @Message, @TimeStamp, @Exception, @IpAddress);", _dbLoggerProvider.Options.TableSchema, _dbLoggerProvider.Options.LogToTable
                     );


                 command.Parameters.Add(new Microsoft.Data.SqlClient.SqlParameter
                 {
                     ParameterName = "@LogLevel",
                     SqlDbType = System.Data.SqlDbType.Int,
                     IsNullable = false,
                     Value = lg,
                     Direction = System.Data.ParameterDirection.Input
                 });


                 command.Parameters.Add(new Microsoft.Data.SqlClient.SqlParameter
                 {
                     ParameterName = "@LogLevelText",
                     SqlDbType = System.Data.SqlDbType.NVarChar,
                     IsNullable = false,
                     Value = lgTxt,
                     Direction = System.Data.ParameterDirection.Input
                 });

                 command.Parameters.Add(new Microsoft.Data.SqlClient.SqlParameter
                 {
                     ParameterName = "@Message",
                     SqlDbType = System.Data.SqlDbType.NVarChar,
                     IsNullable = false,
                     Value = message,
                     Direction = System.Data.ParameterDirection.Input
                 });

                 command.Parameters.Add(new Microsoft.Data.SqlClient.SqlParameter
                 {
                     ParameterName = "@TimeStamp",
                     SqlDbType = System.Data.SqlDbType.DateTime2,
                     IsNullable = false,
                     Value = DateTime.UtcNow,
                     Direction = System.Data.ParameterDirection.Input
                 });

                 command.Parameters.Add(new Microsoft.Data.SqlClient.SqlParameter
                 {
                     ParameterName = "@Exception",
                     SqlDbType = System.Data.SqlDbType.NVarChar,
                     IsNullable = true,
                     Value = (object?)exception ?? DBNull.Value,
                     Direction = System.Data.ParameterDirection.Input
                 });

                 command.Parameters.Add(new Microsoft.Data.SqlClient.SqlParameter
                 {
                     ParameterName = "@IpAddress",
                     SqlDbType = System.Data.SqlDbType.NVarChar,
                     IsNullable = true,
                     Value = "IpAddress",
                     Direction = System.Data.ParameterDirection.Input
                 });

                 command.ExecuteNonQuery();
             }
         }
     }

     private string ConvertLogLevelToTxtString(LogLevel logLevel)
     {
         string result = logLevel switch
         {
             LogLevel.Critical => "CRITICAL",
             LogLevel.Warning => "WARNING",
             LogLevel.Information => "INFORMATION",
             LogLevel.Debug => "DEBUG",
             LogLevel.Trace => "TRACE",
             LogLevel.Error => "ERROR",
             _ => logLevel.ToString().ToUpper(),
         };

         return result;
     }
 }

First, I inject the CustomDataBaseLoggingProvider object to read the configuration values from the CustomDataBaseLoggerOptions object that we inject it already in the CustomDataBaseLoggingProvider class.

Second, the IsEnabled method will check the status of the log level if it's None so ignore the log.

Third, the Log method will write the information that we are gathering and send it to the back store and save it inside Logs table. The code in this method is straightforward, I create an instance from the SqlConnection object and apply a simple insert statement that will execute against our database as you see it here (look at the value of the table name and the schema name, I read it from the Options property that is defined in our CustomDataBaseLoggingProvider object)

 command.CommandText = string.Format(
     @"INSERT INTO {0}.{1} (LogLevel, LogLevelText, Message, TimeStamp, Exception, IpAddress) 
         VALUES (@LogLevel, @LogLevelText, @Message, @TimeStamp, @Exception, @IpAddress);", 
     _dbLoggerProvider.Options.TableSchema, _dbLoggerProvider.Options.LogToTable
     );

To be clear, I'm going to improve the implementation of the Log method later after running our application and creating our first log. (See you there)

Also, if you see clearly, I pass a statis value to the *IpAddress * column but also trust me I'm going to improve this right after running our application and creating our first log. (See you there)

The last step that can let us use our provider is to register the provider in the service registration container at the Program.cs class, to end that I'm going to create an extension method of the ILoggingBuilder type and return the ILoggingBuilder also to let you chain multiple logging provider in your application if there is more than one provider, here is the signature of this method

   public static class DataBaseLoggerExtensions
   {
       public static ILoggingBuilder AddDataBaseLogger(this ILoggingBuilder builder, 
            Action<CustomDataBaseLoggerOptions> configure)
       {
           builder.Services.AddSingleton<ILoggerProvider, CustomDataBaseLoggingProvider>();
           builder.Services.Configure(configure);

           return builder;
       }
   }

Now let use add our provider to the service registration container by opining the Program.cs class and add our own provider as shown here:

builder.Logging.ClearProviders();
builder.Logging.AddDataBaseLogger(options =>
{
    options.ConnectionString = "YOUR CONNETION STRING GOES HERE";
    options.LogToTable = "Logs";
    options.TableSchema = "dbo";
});

First, I clear any registered provider and then add my own one DON'T forget to replace the "YOUR CONNETION STRING GOES HERE" text with your real connection string.

To test our own provider let us update the endpoint (/) that return hello world by injecting the ILogger interface and submitting a dummy information as shown here:

using DevDbLogger.Advanced.CustomLogging;

var builder = WebApplication.CreateBuilder(args);

builder.Logging.ClearProviders();
builder.Logging.AddDataBaseLogger(options =>
{
    options.ConnectionString = "YOUR CONNETION STRING GOES HERE";
    options.LogToTable = "Logs";
    options.TableSchema = "dbo";
});

var app = builder.Build();

app.MapGet("/", (ILogger<Program> logger) =>
{
    logger.LogInformation("weeeeeee are here");
    return "hello world";
});

app.Run();

**HEY, PLEASE RUN YOUR APP NOW**.

Open the Database and the Logs table to see the data that is logged! (see the below pic):

At the third row you will find the message that we wrote inside the (/) endpoint.

Make some improvements to our provider

Cool, right after now I'm going to make some improvement to my custom provider and here id the list of the tasks that I will update or add to my custom provider:

Enhance the IsEnabled method in the CustomeDataBaseLogger class by reading the minimum leg level from the configuration file (if any).
Enhance our adding parameters on the SqlCommand object in the Log method.
Get the real IpAddress of the browser when write log to the back store.

The first one is very easy to do.
Open the CustomDataBaseLoggerOptions class and add a new property named MinLevel that will help us to switch the log level at any time:

    public class CustomDataBaseLoggerOptions
    {
        public string ConnectionString { get; set; } = default!;
        public string LogToTable { get; set; } = default!;
        public string TableSchema { get; set; } = default!;


        /// <summary>
        /// A new property that can help us 
        /// switching the log level at any time
        /// </summary>
        public LogLevel MinLevel { get; set; } = LogLevel.Trace;
    }

In the CustomeDataBaseLogger class update the IsEnabled method to read the value from the Options instead of typing it as a hard code:

 public bool IsEnabled(LogLevel logLevel)
 {
     return logLevel != _dbLoggerProvider.Options.MinLevel;
 }

Now in the Program.cs class update the registration of our custom logging provider to be like so:

builder.Logging.ClearProviders();
builder.Logging.AddDataBaseLogger(options =>
{
    options.ConnectionString = "YOUR CONNETION STRING GOES HERE";
    options.LogToTable = "Logs";
    options.TableSchema = "dbo";

    options.MinLevel = LogLevel.Debug;
});

I added a new option value to my custom logging provider to accept the Debug as a minimum level of logging process.

The second improvement (Enhance our adding parameter or the SqlCommand object in the Log method) from our improvement list will change the shape of our code to be more readable than the existing one by reducing the call to command.Parameters.Add **repeatedly, here I'm going to write and extension method that can make my code very clean, create a new folder in the root of the application named **Helpers, inside this folder create a new class named SqlExtensions the full code is shown here:

using Microsoft.Data.SqlClient;
using System.Data;

namespace DevDbLogger.Helpers
{
    public static class SqlExtensions
    {
        public static SqlParameterCollection AddPrameterWithValue(this SqlParameterCollection parameters, 
            string parameterName, 
            SqlDbType sqlDbType, 
            object? value,
            ParameterDirection parameterDirection,
            bool isNullable = false)
        {
            var parameter = new SqlParameter(parameterName, sqlDbType);
            parameter.Value = value;
            parameter.IsNullable = isNullable;
            parameter.Direction = parameterDirection;
            parameters.Add(parameter);
            parameter.ResetSqlDbType();
            return parameters;
        }
    }
}

After that open the CustomeDataBaseLogger class and update the Log methos to be like so:

  public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception? exception, Func<TState, Exception?, string> formatter)
  {
      if (!IsEnabled(logLevel))
      {
          // Don't log anything to the back store the logLevel it's not enabled.
          return;
      }

      // validation
      if (formatter == null)
      {
          throw new ArgumentNullException(nameof(formatter));
      }

      string message = formatter(state, exception);
      int lg = (int)logLevel;
      string lgTxt = ConvertLogLevelToTxtString(logLevel);
      using (var con = new Microsoft.Data.SqlClient.SqlConnection(_dbLoggerProvider.Options.ConnectionString))
      {
          using (var command = new Microsoft.Data.SqlClient.SqlCommand())
          {
              con.Open();
              command.Connection = con;
              command.CommandType = System.Data.CommandType.Text;
              command.CommandText = string.Format(
                  @"INSERT INTO {0}.{1} (LogLevel, LogLevelText, Message, TimeStamp, Exception, IpAddress) 
                      VALUES (@LogLevel, @LogLevelText, @Message, @TimeStamp, @Exception, @IpAddress);", 
                  _dbLoggerProvider.Options.TableSchema, _dbLoggerProvider.Options.LogToTable
                  );

              command.Parameters.AddPrameterWithValue("@LogLevel", System.Data.SqlDbType.Int, lg, System.Data.ParameterDirection.Input, false);

              command.Parameters.AddPrameterWithValue("@LogLevelText", System.Data.SqlDbType.NVarChar, lgTxt, System.Data.ParameterDirection.Input, false);

              command.Parameters.AddPrameterWithValue("@Message", System.Data.SqlDbType.NVarChar, message, System.Data.ParameterDirection.Input, false);

              command.Parameters.AddPrameterWithValue("@TimeStamp", System.Data.SqlDbType.DateTime2, DateTime.UtcNow, System.Data.ParameterDirection.Input, false);

              command.Parameters.AddPrameterWithValue("@Exception", System.Data.SqlDbType.NVarChar, (object?)exception ?? DBNull.Value, System.Data.ParameterDirection.Input, true);

              command.Parameters.AddPrameterWithValue("@IpAddress", System.Data.SqlDbType.NVarChar, "IpAddress", System.Data.ParameterDirection.Input, true);

              command.ExecuteNonQuery();
          }
      }
  }

Now I think the Log method is very clear and easy to read if you compare it to the old version, and you can see that by using the new AddPrameterWithValue extension method.

Are you still here?! glad to be seeing you staying here and reading!

Now I'm going to finishing-up the application by getting the real IpAddress of the browser that I mentioned it in the last point of our improvement list (Get the real IpAddress of the browser when write log to the back store).

Inside Helpers folder I'm going to create a Interface named IWebHelper and has one member named GetCurrentIpAddress like so:

namespace DevDbLogger.Helpers
{
    public interface IWebHelper
    {
        string GetCurrentIpAddress();
    }
}

Now create in the same folder (Helpers) the concrete class that will implement the IWebHelper interface and the name of this class is WebHelper as shown here:

using System.Net;

namespace DevDbLogger.Helpers
{
    public class WebHelper : IWebHelper
    {
        public readonly IHttpContextAccessor _httpContext;
        public WebHelper(IHttpContextAccessor httpContext)
        {
            _httpContext = httpContext;
        }
        public string GetCurrentIpAddress() 
        {
            if (_httpContext?.HttpContext?.Connection?.RemoteIpAddress is not IPAddress remoteIp)
                return "";

            if (remoteIp.Equals(IPAddress.IPv6Loopback))
                return IPAddress.Loopback.ToString();

            return remoteIp.MapToIPv4().ToString();
        }
    }
}

Here I inject the IHttpContextAccessor to get the IpAddress from the HttpContext object, and checking the type of the IpAddress is it v6 or v4.
Last things before running the application I have to register the new object in the container service, open the Program.cs class and add these two lines to the service configure area:

builder.Services.AddHttpContextAccessor();
builder.Services.AddSingleton<IWebHelper, WebHelper>();

Then update the constructor of the CustomeDataBaseLogger object and add the IWebHelper interface to it, the last version of this class it should be like the following code:

private readonly CustomDataBaseLoggingProvider _dbLoggerProvider;
private readonly IWebHelper _webHelper;
public CustomeDataBaseLogger([NotNull] CustomDataBaseLoggingProvider dataBaseLoggerProvider, IWebHelper webHelper)
{
    _dbLoggerProvider = dataBaseLoggerProvider;
    _webHelper = webHelper;
}

Also don't forget to update the value of the IpAddress parameter in the Log method by getting the value from the _webHelper.GetCurrentIpAddress() method:

 command.Parameters.AddPrameterWithValue("@IpAddress", System.Data.SqlDbType.NVarChar, _webHelper.GetCurrentIpAddress(), System.Data.ParameterDirection.Input, true);

Lastly, update the CustomDataBaseLoggingProvider object to accept the IWebHelper interface, because when the ILoggerProvider **interface creates and instance of our logger class (in this case is **CustomeDataBaseLogger object) it should pass the IWebHelper interface as a second parameter, next you will see the last version of the CustomDataBaseLoggingProvider object:

using Microsoft.Extensions.Options;

namespace DevDbLogger.Advanced.CustomLogging
{
    public class CustomDataBaseLoggingProvider : ILoggerProvider
    {
        public readonly CustomDataBaseLoggerOptions Options;
        private readonly IWebHelper _webHelper;

        public CustomDataBaseLoggingProvider(IOptions<CustomDataBaseLoggerOptions> options, IWebHelper webHelper)
        {
            Options = options.Value;
             _webHelper = webHelper;
        }

        public ILogger CreateLogger(string categoryName)
        {

            return new CustomeDataBaseLogger(this, _webHelper);
        }

        public void Dispose()
        {

        }
    }
}

Now, run the application and if you open the Logs table to see the value of the IpAddress you will find it 127.0.0.1 if you run the application in the local environment.

Look at the row number 27 in the above pic.
Thanks for reading.
Mohammed!

Building a Custom Logging Provider in ASP.NET Core

HEY, PLEASE RUN YOUR APP NOW.

Make some improvements to our provider

**HEY, PLEASE RUN YOUR APP NOW**.