This sample demonstrates how to implement logging to sql database in ASP.NET Core.
Sample prerequisites
• Install Visual Studio and .NET Core
- .NET Core 1.0 or later version(s). [.NET Core + Visual Studio tooling]
- Microsoft Visual Studio 2015 update3 or above. [Visual Studio 2015 installer]
• Restore all the NuGet packages in the project
• Create a database with sql scripts below:
CREATE DATABASE CustomLoggerDB GO USE CustomLoggerDB GO CREATE TABLE EventLog( [ID] int identity primary key, [EventID] int, [LogLevel] nvarchar(50), [Message] nvarchar(4000), [CreatedTime] datetime2 ) 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',20) GO
Running the sample
Before you run the sample, please update the connection string in the appsettings.json first.
"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:
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:
loggerFactory.AddContext(LogLevel.Information);
Add DBContext in the services:
CustomLoggerDBContext.ConnectionString = Configuration.GetConnectionString("LoggerDatabase");
services.AddDbContext<CustomLoggerDBContext>();
LoggerProvider
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:
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:
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); }