Subscribe to Youtube channel

Implement Logging in Sql Server Database using Nlog in .net core MVC application - part 3

In previous article "Implement Logging in CSV file using Nlog in .net core MVC application- part 2"  I have demonstrated that how we can implement Logging in CSV file using Nlog in .core mvc application. In previous article I have logged the exceptions in CSV file, in this article I will show you how you can store the exception and custom logs in SQL server database using Nlog in .net core MVC application. Storing logs in SQL server database can help us to query the exception data efficiently and we can show it to UI if needed to check exception upfront.



 Lets start the implementation of it, I going to use the same example project used in previous article/demonstration.

The following nuget package needs to be installed before implement the sql server logging using NLog.


  • "Microsoft.Data.SqlClient" Version="3.0.1"
  • "NLog" Version="4.7.13" 
  • "NLog.Config" Version="4.7.13"
  • "NLog.Extensions.Logging" Version="1.7.4"
  • "NLog.Web.AspNetCore" Version="4.14.0"

NLog sql server logging package dependency


Firstly we will create the table (AppException) for storing the exception in SQL server database.

 CREATE TABLE [dbo].[AppException](  
      [id] [int] IDENTITY(1,1) NOT NULL,  
      [Date] [datetime] NULL,  
      [level] [varchar](50) NULL,  
      [ExceptionMessage] [varchar](max) NULL,  
      [exception] [varchar](max) NULL,  
      [stacktrace] [varchar](max) NULL,  
      [CustomProperty] [varchar](150) NULL,  
      [id] ASC  

Then modify the Nlog.config file as follows, added new target and rule for logging logs in SQL Server database.


   <target xsi:type="File" name="logError" fileName="${basedir}/logs/${shortdate}.log"  
       layout="${longdate} ${uppercase:${level}} ${message}" />  
   <target xsi:type="File" name="logErrorWithCSV" fileName="${basedir}/logs/${shortdate}.csv">  
    <layout xsi:type="CsvLayout" delimiter="Pipe" withHeader="true">  
     <column name="Date" layout="${longdate}" />  
     <column name="level" layout="${level:upperCase=true}"/>  
     <column name="message" layout="${message}" />  
     <column name="exception" layout="${exception:format=ToString}"/>  
     <column name="stacktrace" layout="${stacktrace:topFrames=10}" />  
     <column name="CustomProperty" layout="${event-properties:CustomProperty}"/>  
   <target xsi:type="Database"  
   dbProvider="Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient"  
   connectionString="Data Source=DESKTOP-IA0LSSV\SQLEXPRESS;Initial Catalog=Emp;User Id=sa;Password=12345"  
   commandText="INSERT INTO AppException(Date,ExceptionMessage,level,exception,stacktrace,CustomProperty) VALUES (@Date,@ExceptionMessage,@level,@exception,@stacktrace,@CustomProperty)">  
    <parameter name="@Date" layout="${date}" />  
    <parameter name="@level" layout="${level:upperCase=true}"/>  
    <parameter name="@ExceptionMessage" layout="${message}" />  
    <parameter name="@exception" layout="${exception:format=ToString}"/>  
    <parameter name="@stacktrace" layout="${stacktrace:topFrames=10}" />  
    <parameter name="@CustomProperty" layout="${event-properties:CustomProperty}"/>  
   <logger name="ErrorLogFile" minlevel="Debug" writeTo="logError" />  
   <logger name="logErrorWithCSV" minlevel="Debug" writeTo="logErrorWithCSV" />  
   <logger name="logErrorWithSqlServer" minlevel="Trace" writeTo="logErrorWithSqlServer" />  


Modify the IloggerManager Interface as per below code sample.

 using System;   
 namespace NlogExample.NlogHelper  
   public interface ILoggerManager  
     void LogError(string ErrorMessage);  
     void LogError(Exception ex, string ErrorMessage);  
     void LogTrace(string ErrorMessage);  
     void LogDebug(string ErrorMessage);  
     void LogInfo(string ErrorMessage);  
     void LogErrorCsv(Exception ex, string ErrorMessage);  
     void LogErrorDB(Exception ex, string ErrorMessage);  


Now next we need to implement new method added for Sql Server db logging in loggerManager class added in interface as per below sample.


 using NLog;  
 using System;  
 namespace NlogExample.NlogHelper  
   public class LoggerManager: ILoggerManager  
     Logger log = NLog.LogManager.GetLogger("ErrorLogFile");  
     Logger logErrorWithCSV = NLog.LogManager.GetLogger("logErrorWithCSV");  
     Logger logErrorWithSqlService = NLog.LogManager.GetLogger("logErrorWithSqlServer");  
     public void LogError(string ErrorMessage)  
     public void LogError(Exception ex,string ErrorMessage)  
       log.Error(ex, ErrorMessage);  
     public void LogInfo(string ErrorMessage)  
     public void LogDebug(string ErrorMessage)  
     public void LogTrace(string ErrorMessage)  
     public void LogErrorCsv(Exception ex, string ErrorMessage)  
       logErrorWithCSV.SetProperty("CustomProperty", "Value of custom property");  
     public void LogErrorDB(Exception ex, string ErrorMessage)  
       logErrorWithSqlService.SetProperty("CustomProperty", "Value of custom property for sql server");  
       logErrorWithSqlService.Error(ex, ErrorMessage);  


Now we will call the LogErrorDb method in our HomeController as below.

 using Microsoft.AspNetCore.Mvc;   
 using NlogExample.Models;  
 using NlogExample.NlogHelper;  
 using System;   
 using System.Diagnostics;  
 namespace NlogExample.Controllers  
   public class HomeController : Controller  
     private readonly ILoggerManager _logger;  
     public HomeController(ILoggerManager logger)  
       _logger = logger;  
     public IActionResult Index()  
       _logger.LogInfo("In Index");  
         _logger.LogDebug("In Index");  
         int i = 0;  
         _logger.LogTrace("value of i="+i);  
         var output = 75 / i;  
         _logger.LogDebug("out Index");  
       catch (Exception ex)  
         _logger.LogError("this is sample error without exception");  
         _logger.LogError(ex, "this is sample error with exception");  
         _logger.LogErrorCsv(ex, "this is sample error with exception");  
         _logger.LogErrorDB(ex, "this is sample error with exception");  
       return View();  
     public IActionResult Privacy()  
       return View();  
     [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]  
     public IActionResult Error()  
       return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });  


Now we can run the project and we will get the below output.


Nlog sql logging sample


That's it!!!, This way you can easily implement the logging in SQL server database using Nlog.









Popular posts from this blog

Implement Logging in CSV file using Nlog in .net core MVC application- part 2

Implement Nlog in .Net core MVC application part 1

Disable backspace key using Jquery

Devexpress Datebox date formatting in angular 6 with example

Angular User Session Timeout example step by step

Restore the lost focus of Auto post back controls in update Panel control

How to Import CSV File and bind csv file data to gridview in using c#

Remove Owin from MVC 5 Application and use custom forms authentication

Configure archival and purging of log file in Nlog