DEV Community

Cover image for Store Log Messages in SQL Server For Your ASP.NET Core 6.0 App by Using NLog
Mohammed Ahmed Hussien
Mohammed Ahmed Hussien

Posted on

Store Log Messages in SQL Server For Your ASP.NET Core 6.0 App by Using NLog

NLog is a third-party library to store message that shown the state of the application, and there is more than one way to store that messages:
1- File
2- Console
3- Database (I will describe how to use this option in this text)
I will guide you how to setup NLog in ASP.NET Core App that target .NET 6.

I assume you have an ready ASP.NET Core project.
First you have to install four packages by using Nuget package:

Image description

The System.Data.SqlClient it helps us to store the log message to SQL Server database, and in the same context, if you're working with different database you have to install the properly package that fit what the NLog needs.

So, to prepare the ASP.NET Core Application to work with NLog open the Program.cs class and paste the below code ( I will explain it later)


using NLog;
using NLog.Web;

var logger = NLog.LogManager
    .Setup()
    .LoadConfigurationFromAppSettings()
    .GetCurrentClassLogger();
try
{
    var builder = WebApplication.CreateBuilder(args);
    builder.Services.AddControllersWithViews();

       // Cleaer the build in provider
    builder.Logging.ClearProviders();

    // log youe application at trace level 
    builder.Logging.SetMinimumLevel(Microsoft.Extensions.Logging.LogLevel.Trace);

    // Register the NLog service
    builder.Host.UseNLog();

    var app = builder.Build();
    app.UseStaticFiles();
    app.UseRouting();
   app.MapDefaultControllerRoute();
   app.Run();
}

catch(Exception ex)
{
    logger.Error(ex);
    throw;
}
finally
{
// Ensure to shout downon the NLog ( Disposing )
    NLog.LogManager.Shutdown();
}

Enter fullscreen mode Exit fullscreen mode

Here I create an instance of Logger class by calling the LogManager. The LogManager class will let me to work with all available functions and methods that I need when I working with NLog, therefore, NLog need to know from where can render all the settings and configurations it needs, so I Setup and add all the configurations at appsettings.json file( I will show you next what this file include...) by adding LoadConfigurationFromAppSettings() method, and chaining it with GetCurrentClassLogger() method to get the full name of the class ( including namespace ) that I want to log and fetch all the information and errors and warning from it.
Now let us jump into the appsettings.json file, but before that we need to setup our Database and the Table that will take care of our all information we would like to log.

So Run this script to create the table, Be sure to create the database first with any name you liked!

CREATE TABLE [dbo].[AppLogs](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Added_Date] [datetime] NOT NULL,
    [Level] [nvarchar](10) NULL,
    [Message] [nvarchar](max) NULL,
    [StackTrace] [nvarchar](max) NULL,
    [Exception] [nvarchar](max) NULL,
    [Logger] [nvarchar](255) NULL,
    [RequestUrl] [nvarchar](255) NULL,
    [RequestType] [nvarchar](10) NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)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]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Enter fullscreen mode Exit fullscreen mode

Now we need to map this column names to NLog Layout, and that what we have to do in the appsettings.josn file, like below:

{
  "NLog": {
    "throwConfigExceptions": true,
    "targets": {
      "async": true,
      "database": {
        "type": "Database",
        "dbProvider": "System.Data.SqlClient",
        "connectionString": "Data Source=Youe Server Name;Initial Catalog=Your Database Name;Trusted_Connection=True;",
        "keepConnection": "true",
        "commandText": "insert into dbo.AppLogs (Added_Date,Level,Message,StackTrace,Exception,Logger,RequestUrl,RequestType) values (@addeddate, @level, @message, @stackTrace, @logger, @exception, @requestUrl, @requestType);",
        "parameters": [
          {
            "name": "@addeddate",
            "layout": "${date}"
            //"dbType": "DbType.DateTime"
          },
          {
            "name": "level",
            "layout": "${level}"
          },
          {
            "name": "message",
            "layout": "${message}"
          },
          {
            "name": "stackTrace",
            "layout": "${stacktrace}"
          },
          {
            "name": "logger",
            "layout": "${logger}"
          },
          {
            "name": "exception",
            "layout": "${exception:tostring}"
          },
          {
            "name": "requestUrl",
            "layout": "${aspnet-request-url}"
          },
          {
            "name": "requestType",
            "layout": "${aspnet-request-method}"
          }
        ]
      },
      "logconsole": {
        "type": "Console"
      }
    },
    "rules": [
      {
        "logger": "*",
        "minLevel": "Info",
        "writeTo": "logconsole"
      },
      {
        "logger": "*",
        "minLevel": "Debug",
        "writeTo": "database"
      }
    ]
  },
  "AllowedHosts": "*"
}

Enter fullscreen mode Exit fullscreen mode

Ooooooops, there is a lot of configuration data here. but it's easy to understand.
In this configuration file I have two target:
1- Database
2- Console

At target element I introduce more than one attribute, the important one is commandText the mission of this attribute is to run SQL Insert Statement against the table that we created when we setup our database.
The connectionString attribute it's very clear, and it give us the capability to connect between the app and database.
The type attribute indicate where I would like to store the log messages. In our case at ( Back Store ).
You have to be careful with name of your target element, in our case the name is database, therefore if you look at the rules element at the end of appsettings.json file you will notice that, I append this name ( database ) to the second rule like so:

  {
        "logger": "*",
        "minLevel": "Debug",
        "writeTo": "database"
      }
Enter fullscreen mode Exit fullscreen mode

The parameters attribute ensure that all the column names in the AppLogs table are mapped to NLog Layout correctly.

Now here is my HomeController.cs class

using Microsoft.AspNetCore.Mvc;

namespace NLogExample.Controllers
{
    public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger;   
        public HomeController(ILogger<HomeController> logger)
        {
            _logger = logger;
        }
        public IActionResult Index()
        {
            _logger.LogInformation("My Name is Mohammed Ahmed Hussien");
            _logger.LogWarning("Please, can you check your app's performance");
            _logger.LogError(new Exception(), "Booom, there is an exception");

            ViewBag.Name = "Mohammed Ahmed Hussien";
            return View();
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

If you run the application, and navigate to you SQL Server to see what the information are logged you will see the messages that I typed it in the Index action method. see the next image:

Image description

Thanks for reading...

Discussion (0)