Post
Ask Question

How To Implement Logging Using Serilog In ASP.NET Core 5.0 Application With Database

Logging is one of the most crucial things in application development. It is fundamental and helps to troubleshoot any application issues. Like other .NET libraries we can implement Serilog in .NET application. Serilog provides diagnostic logging to files, the console, database and elsewhere.
 
In this article we will learn how to implement Serilog in ASP.NET Core 5.0 Web API Project and save the log in database.
 
Prerequisities
  • Visual Studio 2019
  • MSSQL Server
I have assumed that, you have already created ASP.NET Core 5.0 WebAPI project. Firstly, we need to install following packages to implement Serilog and capture Log in Database. Open Command line and run below command.
 
Install-Package Serilog.AspNetCore
Install-Package Serilog.Sinks.MSSqlServer
 
Or
 
You can install it from Nugget Package Manager also.
 
To capture Log in database we have to create Table in our database. Create table in your database using following script. 
  1. CREATE TABLE [dbo].[Log](  
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Message] [nvarchar](max) NULL,  
  4.     [MessageTemplate] [nvarchar](max) NULL,  
  5.     [Level] [nvarchar](128) NULL,  
  6.     [TimeStamp] [datetimeoffset](7) NOT NULL,  
  7.     [Exception] [nvarchar](max) NULL,  
  8.     [Properties] [xml] NULL,  
  9.     [LogEvent] [nvarchar](max) NULL,  
  10.     [UserName] [nvarchar](200) NULL,  
  11.     [IP] [varchar](200) NULL,  
  12.  CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED   
  13. (  
  14.     [Id] ASC  
  15. )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]  
  16. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
We need to add connection string in appsetting.json to save Log in database.
 
Sample appsetting.json code
  1. {  
  2.   "ConnectionStrings": {  
  3.     "DemoSeriLogDB": "Data Source=yourDBServer;Initial Catalog=yourDatabaseName;Persist Security Info=True;User ID=yourDbUserId;Password=yourDBPassword;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;TrustServerCertificate=False"  
  4.   },  
  5.   "Logging": {  
  6.     "LogLevel": {  
  7.       "Default": "Information",  
  8.       "Microsoft": "Warning",  
  9.       "Microsoft.Hosting.Lifetime": "Information"  
  10.     }  
  11.   },    
  12.   "AllowedHosts": "*",   
  13. }  
  14.   
Write the below code Program.cs file. This code helps to read Json files and we can get values from Json such as Connection string.
  1. public static IConfiguration Configuration { get; } = new ConfigurationBuilder()  
  2.         .SetBasePath(Directory.GetCurrentDirectory())  
  3.         .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)  
  4.         .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? "Production"}.json", optional: true)  
  5.         .Build();  
Write the below code in Program.cs file Main function to get Connection string from Json file.
  1. string connectionString = Configuration.GetConnectionString("DemoSeriLogDB");  
Similarly, we need to write the below code in Program.cs file Main function to register or setting of Serilog sink that writes log events to a table in MSSQL Server database.
 
Additionally, through Column Options we can add custom Log data in the table such as User name, IP etc. In this example, I have added username only but we can add multiple columns also.
  1. var columnOptions = new ColumnOptions  
  2.            {  
  3.                AdditionalColumns = new Collection<SqlColumn>  
  4.                {  
  5.                    new SqlColumn("UserName", SqlDbType.VarChar)                                       
  6.                }  
  7.            }; //through this coulmnsOptions we can dynamically  add custom columns which we want to add in database  
  8.            Log.Logger = new LoggerConfiguration()  
  9.                .Enrich.FromLogContext()  
  10.                .WriteTo.MSSqlServer(connectionString, sinkOptions: new MSSqlServerSinkOptions { TableName = "Log" }  
  11.                , null, null, LogEventLevel.Information, null, columnOptions: columnOptions, null, null)  
  12.                .CreateLogger();  
Sample Program.cs file code
  1. public class Program  
  2.    {  
  3.        public static IConfiguration Configuration { get; } = new ConfigurationBuilder()  
  4.        .SetBasePath(Directory.GetCurrentDirectory())  
  5.        .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)  
  6.        .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? "Production"}.json", optional: true)  
  7.        .Build();  
  8.   
  9.   
  10.        public static void Main(string[] args)  
  11.        {           
  12.            string connectionString = Configuration.GetConnectionString("DemoSeriLogDB");            
  13.            var columnOptions = new ColumnOptions  
  14.            {  
  15.                AdditionalColumns = new Collection<SqlColumn>  
  16.                {  
  17.                    new SqlColumn("UserName", SqlDbType.NVarChar)  
  18.                  }  
  19.            };  
  20.            Log.Logger = new LoggerConfiguration()  
  21.                .Enrich.FromLogContext()  
  22.                .WriteTo.MSSqlServer(connectionString, sinkOptions: new MSSqlServerSinkOptions { TableName = "Log" }  
  23.                , null, null, LogEventLevel.Information, null, columnOptions: columnOptions, null, null)  
  24.                .CreateLogger();  
  25.           
  26.            CreateHostBuilder(args).Build().Run();  
  27.        }  
  28.   
  29.        public static IHostBuilder CreateHostBuilder(string[] args) =>  
  30.            Host.CreateDefaultBuilder(args)  
  31.                .ConfigureWebHostDefaults(webBuilder =>  
  32.                {  
  33.                    webBuilder.UseStartup<Startup>();  
  34.                }).UseSerilog();//serilog;  
  35.    }  
If we want to capture User name in our Log table then we have to write code to get user name in Configuration function of Startup.cs class below Authorization. So, write below line of code in Configuration function in Startup.cs class.
  1. public void Configure(IApplicationBuilder app, IWebHostEnvironment env)  
  2.         {  
  3.  app.UseAuthorization();  
  4.           // below code is needed to get User name for Log             
  5.             app.Use(async (httpContext, next) =>  
  6.             {  
  7.                 var userName = httpContext.User.Identity.IsAuthenticated ? httpContext.User.Identity.Name : "Guest"; //Gets user Name from user Identity  
  8.                 LogContext.PushProperty("Username", userName); //Push user in LogContext;  
  9.                 await next.Invoke();  
  10.             }  
  11.             );  
  12. }  
In your WebAPI Control class, you can now call Serilog and Capture the log. You simply call Serilog in your Method/Function. You can write the below line of code to capture the Log.
  1. Serilog.Log.Information("Started --Log Of Control Name-->Get Method");  
To capture Error Log write the below code.
  1. Serilog.Log.Error("Error: Log Of Control Name-->Get Method Error:", ex);  
Sample WebAPI Controller code
  1. public IEnumerable<WeatherForecast> Get()  
  2.         {  
  3.             Serilog.Log.Information("Started --Log Of Weather Forecast-->Get Method");  
  4.             try  
  5.             {  
  6.                 var rng = new Random();  
  7.                 return Enumerable.Range(1, 5).Select(index => new WeatherForecast  
  8.                 {  
  9.                     Date = DateTime.Now.AddDays(index),  
  10.                     TemperatureC = rng.Next(-20, 55),  
  11.                     Summary = Summaries[rng.Next(Summaries.Length)]  
  12.                 })  
  13.                 .ToArray();  
  14.             }  
  15.             catch (Exception ex)  
  16.             {  
  17.                 Serilog.Log.Error("Error: Log Of Weather Forecast-->Get Method Error:", ex);  
  18.                 return null;  
  19.             }             
  20.         }  
If you run the application your Log will be created. Go to database and you can see Log data are already available in Log table.
 
 
It captures all the Logging information. If you want to capture minimal information like, information and error, write the below line of code.
  1. .MinimumLevel.Override("Microsoft", LogEventLevel.Error)//To capture Information and error only  
 
So on we can capture Log in the database.
 
In this way, this article has described how to implement SeriLog implementation in ASP.NET Core 5.0 application. I hope this helps you to implement it in your project.


Similar Articles