How to save logs into db

Question

I m running Microsoft ILogger in my asp.net core webapi project. The logs are running perfect and also generating log files but log files are really hard to scan and read.
I want to save the logs into db and then run some query and find out the issues and warning.

So how do I save into db when Log call on exceptions ?

is there any project or example on it? I check ILogger on docs. microsoft .com but didn't get any helpful on this.

Project is in .net 6

Answer 1

Hi @Prathamesh Shende ,

Here is an article about Create a custom database logging provider with ASP.NET Core ILogger, it will use the ADO.Net methods to insert the log into database. I also try to modify the code to use EF core and Dbcontext, but it keeps showing the Cannot consume scoped servicea from singleton service error and stackoverflow issue.

So, I suggest you could refer the article or the following steps and use ADO.Net method to insert the log to the database.

1.Create a new Asp.net core web application and add the following custom error model:

public class CustomError  
{  
    public int Id { get; set; }  
    public string Values { get; set; }  
    public DateTime Created { get; set; }  
}  

public class ApplicationDbContext : IdentityDbContext  
{  
    public DbSet<CustomError> Errors { get; set; }  
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)  
        : base(options)  
    {  
    }  
}  

Then, enable migration and generate the database and the Errors table.

2.Add a DbLoggerOptions class to store the logger settings:

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

    public string[] LogFields { get; init; }  

    public string LogTable { get; init; }  

    public DbLoggerOptions()  
    {  
    }  
}  

The appsettings.json file like this:

{  
  "ConnectionStrings": {  
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-MVCLogToDb-D3A8C17C-FEB5-4F32-B4A8-3449605330CF;Trusted_Connection=True;MultipleActiveResultSets=true"  
  },  
  "Logging": {  
    "Database": {  
      "Options": {  
        "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database=aspnet-MVCLogToDb-D3A8C17C-FEB5-4F32-B4A8-3449605330CF;Trusted_Connection=True;MultipleActiveResultSets=true",  
        "LogFields": [  
          "LogLevel",  
          "ThreadId",  
          "EventId",  
          "EventName",  
          "ExceptionMessage",  
          "ExceptionStackTrace",  
          "ExceptionSource"  
        ],  
        "LogTable": "dbo.Errors"  
      }  
    },  
    "LogLevel": {  
      "Default": "Information",  
      "Microsoft.AspNetCore": "Warning",  
      "RoundTheCode.LoggerDb": "Error"  
    }  
  },  
  "AllowedHosts": "*"  
}  

3.Creating the custom logging provider

//DbLoggerProvider.cs  
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()  
    {  
    }  
}  

Add the logger instance:

//DbLogger.cs  
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();  
        }  
    }  
}  

Then, configuration extension method:

//DbLoggerExtensions.cs  
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;  
    }  
}  

4.Adding the logger provider to the ASP.NET Core application

// 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();  

After running the application, the result like this:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Answer 2

Write a custom logger.

Implement a custom logging provider in .NET

Rather than writing to the console save the exception data to a database table using your (unknown) data access provider.

Answer 3

I have created the files this.
public class CustomLoggingProvider : ILoggerProvider
{
BooksServerContext _context;

public CustomLoggingProvider(BooksServerContext context)
{
    _context = context;
}

public ILogger CreateLogger(string categoryName)
{
    return new DatabaseLogger(_context);
}

public void Dispose()
{
    throw new NotImplementedException();
}

}

public class DatabaseLogger : ILogger
{
BooksServerContext _context;

public DatabaseLogger(BooksServerContext context)
{
    _context = context;
}

public IDisposable BeginScope<TState>(TState state)
{
    throw new NotImplementedException();
}

public bool IsEnabled(LogLevel logLevel)
{
    throw new NotImplementedException();
}

public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
{
    _context.Add(new Logs()
    {
        CreatedOn = DateTime.Now,
        Level = ((int)logLevel),
        //EventId = eventId.ToString(),
        Message = exception.ToString(),
        Source = 2,
        StackTrace = state.ToString(),
    });
    _context.SaveChanges();
}

program.cs

builder.Logging.ClearProviders();
builder.Logging.AddProvider(new CustomLoggingProvider()); //problem : I cannot provide the dbContext as parameter to CustomLoggingProvider
builder.Logging.AddSimpleConsole(options =>
{
options.IncludeScopes = true;
options.TimestampFormat = "\n[dd-MMM-yyyy hh:mm:ss]\n";
});

After I will set
If env.Production() then save into db.

Answer 4

SeriLog provides the ability to write logs to SQL-Server. I have a console project which demonstrates writing to SQL-Server using .NET Core 7 (will work with .NET Core 6) using EF Core. What is provided can be used in an ASP.NET Core project. See also

• SeriLog
• SeriLog providers