Store Log Messages in SQL Server For Your ASP.NET Core 6.0 App by Using NLog
Tags: dotnet, aspnetcore, nlog
Published: 2022-03-16 21:38:00

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rdb6t0lg0bt27drefelz.PNG)

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)

```C#

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();
}

``` 
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!

```sql
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]
``` 

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:

```C#
{
  "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": "*"
}

```

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:


```C#
  {
        "logger": "*",
        "minLevel": "Debug",
        "writeTo": "database"
      }
```
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  

```C#
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();
        }
    }
}
```
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g7jw22rtwozr25pl3hun.PNG)

Thanks for reading...