受欢迎的博客标签

How to implement logging to sql database in ASP.NET Core

Published

This sample demonstrates how to implement logging to sql database in ASP.NET Core.

 

Sample prerequisites

• Install Visual Studio and .NET Core

• Restore all the NuGet packages in the project

• Create a database with sql scripts below:

SQL
CREATE DATABASE CustomLoggerDB  
GO  
USE CustomLoggerDB 
GO  
CREATE TABLE EventLog(  
    [IDint identity primary key,  
    [EventIDint,  
    [LogLevelnvarchar(50), 
    [Messagenvarchar(4000), 
    [CreatedTimedatetime2 
) 
GO 
CREATE TABLE Student(  
    ID int identity primary key,  
    Name nvarchar(50),  
    Age int  
)  
GO 
INSERT INTO Student VALUES('Bear',18)  
INSERT INTO Student VALUES('Frank',20GO

Running the sample

Before you run the sample, please update the connection string in the appsettings.json first.

JavaScript
"ConnectionStrings"{ 
  "LoggerDatabase""Server=.;Database=CustomLoggerDB;Trusted_Connection=True;" 
} 

 Do one of the following to start debugging:

• Click the Start Debugging button on the toolbar.

• Click Start Debugging in the Debug menu.

• Press F5.

 

Click "Frank", then update the name to "Frank1".

 

Go to sql server client, and run script:

SQL
SELECT * FROM EventLog

You will see that the log data has been inserted into the table.

 

Using the code

Add logger provider in the configure method:

C#
loggerFactory.AddContext(LogLevel.Information);

Add DBContext in the services:

C#
CustomLoggerDBContext.ConnectionString = Configuration.GetConnectionString("LoggerDatabase"); 
services.AddDbContext<CustomLoggerDBContext>(); 

LoggerProvider

C#
public class DBLoggerProvider : ILoggerProvider 
{ 
    private readonly Func<string, LogLevel, bool> _filter; 
    private string _connectionString; 
    public DBLoggerProvider(Func<string, LogLevel, bool> filter,string connectionStr) 
    { 
        _filter = filter; 
        _connectionString = connectionStr; 
    } 
    public ILogger CreateLogger(string categoryName) 
    { 
        return new DBLogger(categoryName, _filter, _connectionString); 
    } 
    public void Dispose() 
    { 
    } 
}

DBLogger:

C#
public class DBLogger : ILogger 
{ 
    private string _categoryName; 
    private Func<string, LogLevel, bool> _filter; 
    private SqlHelper _helper; 
    private int MessageMaxLength = 4000; 
    public DBLogger(string categoryName, Func<string, LogLevel, bool> filter,string connectionString) 
    { 
        _categoryName = categoryName; 
        _filter = filter; 
        _helper = new SqlHelper(connectionString); 
    } 
    public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter) 
    { 
        if (!IsEnabled(logLevel)) 
        { 
            return; 
        } 
        if (formatter == null) 
        { 
            throw new ArgumentNullException(nameof(formatter)); 
        } 
        var message = formatter(state, exception); 
        if (string.IsNullOrEmpty(message)) 
        { 
            return; 
        } 
        if (exception != null) 
        { 
            message += "\n" + exception.ToString(); 
        } 
        message = message.Length > MessageMaxLength ? message.Substring(0, MessageMaxLength) : message; 
        EventLog eventLog = new EventLog 
        { 
            Message = message, 
            EventId = eventId.Id, 
            LogLevel = logLevel.ToString(), 
            CreatedTime = DateTime.UtcNow 
        }; 
        _helper.InsertLog(eventLog); 
    } 
    public bool IsEnabled(LogLevel logLevel) 
    { 
        return (_filter == null || _filter(_categoryName, logLevel)); 
    } 
    public IDisposable BeginScope<TState>(TState state) 
    { 
        return null; 
    } 
}

Student Controller:

C#
private CustomLoggerDBContext _context; 
private readonly ILogger<StudentController> _logger; 
 
public StudentController( ILogger<StudentController> logger, CustomLoggerDBContext context) 
{ 
    _context = context; 
    _logger = logger; 
} 
 
public IActionResult Index() 
{ 
    var StudentList = _context.Student.ToList(); 
    _logger.LogInformation((int)LoggingEvents.GENERATE_ITEMS, "Show student list."); 
    return View(StudentList); 
}