How to save logs into db

Prathamesh Shende 376 Reputation points
2022-04-08T11:27:03.93+00:00

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

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,360 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Zhi Lv - MSFT 32,076 Reputation points Microsoft Vendor
    2022-04-13T08:04:26.123+00:00

    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:

    192548-image.png


    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

    2 people found this answer helpful.

  2. AgaveJoe 27,576 Reputation points
    2022-04-08T12:20:10.867+00:00

    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.

    0 comments No comments

  3. Prathamesh Shende 376 Reputation points
    2022-04-08T15:56:13.593+00:00

    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.


  4. Karen Payne MVP 35,376 Reputation points
    2023-04-26T15:42:57.8966667+00:00

    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

    0 comments No comments