NLog with SQL Server and MVC 4

NLog is a great open-source logging tool that allows developers to easily and efficiently implement custom logging. Nlog can be configured to log to a number of targets, but on this tutorial we will be looking at logging to the database.

First you need to add NLog to your MVC website. Bring up the Nuget package manager and search for NLog. Add the following packages:

• NLog
• NLog Schema for Intellisense
• NLog configuration
• NLog for Extended Profile

This will add the necessary dlls and config files for use later. Once the installation is complete, you should see the following file at the root of your application : NLog.config. Double-click to edit it in Visual Studio. Copy and paste the following xml:

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      throwExceptions="true"
      internalLogLevel="Trace"
      internalLogFile="..\..\..\Logs\nlog-app.log">
  <!-- 
  See http://nlog-project.org/wiki/Configuration_file 
  for information on customizing logging rules and outputs.
   -->
  <targets>
    <!-- file targets -->
    <target name="asyncFile" xsi:type="AsyncWrapper">
      <target xsi:type="File" name="f" fileName="${basedir}/Logs/${shortdate}.log"
            layout="${longdate} ${uppercase:${level}} ${message} ${aspnet-user-identity}"/>
    </target>

    <!-- database targets -->
    <target name="database" xsi:type="Database" keepConnection="true" useTransactions="true"
             dbProvider="System.Data.SqlClient" 
             connectionString="data source=localhost;initial catalog=NLogTest;integrated security=false;persist security info=True;User ID=sa;Password=Password1" 
              commandText="INSERT INTO Logs(EventDateTime, EventLevel, UserName, MachineName, EventMessage, ErrorSource, ErrorClass, ErrorMethod, ErrorMessage, InnerErrorMessage) VALUES (@EventDateTime, @EventLevel, @UserName, @MachineName, @EventMessage, @ErrorSource, @ErrorClass, @ErrorMethod, @ErrorMessage, @InnerErrorMessage)">    
      <!-- parameters for the command -->
      <parameter name="@EventDateTime" layout="${date:s}" />
      <parameter name="@EventLevel" layout="${level}" />
      <parameter name="@UserName" layout="${aspnet-user-identity}" />
      <parameter name="@MachineName" layout="${machinename}" />
      <parameter name="@EventMessage" layout="${message}" />
      <parameter name="@ErrorSource" layout="${event-context:item=error-source}" />
      <parameter name="@ErrorClass" layout="${event-context:item=error-class}" />
      <parameter name="@ErrorMethod" layout="${event-context:item=error-method}" />
      <parameter name="@ErrorMessage" layout="${event-context:item=error-message}" />
      <parameter name="@InnerErrorMessage" layout="${event-context:item=inner-error-message}" />
    </target>
  </targets>
  <rules>
    <!-- add your logging rules here -->
    <logger name="*" minlevel="Debug" writeTo="asyncFile,database" />
  </rules>
</nlog>

So let’s break it down further...

The first part is the main Nlog configuration and we enable custom logging for any errors that may get thrown during the Nlog initialization. It can be handy when chasing down why Nlog is not working. The internal logging is turned on by the throwExceptions property and the internalLogLevel sets the minimum log type to log. Finally the internalLogFile points to the file that the internal errors should be written.

<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      throwExceptions="true"
      internalLogLevel="Trace"
      internalLogFile="..\..\..\Logs\nlog-app.log">

Next we will set up a file logger. This logger will log all logs to a file asynchronously.

<targets>
    <!-- file targets -->
    <target name="asyncFile" xsi:type="AsyncWrapper">
      <target xsi:type="File" name="f" fileName="${basedir}/Logs/${shortdate}.log"
            layout="${longdate} ${uppercase:${level}} ${message} ${aspnet-user-identity}"/>
    </target>

The filename will be the be written to the Logs directory and the name will consist of the current date. This way, a new log file will be generated for each new date keeping the file size manageable and the files responsive, especially if your site performs heavy logging. The logger will write the following information to the file:

• ${longdate} : a datetime stamp
• ${uppercase:${level}} : the log level
• ${message}: the custom message you wish to log
• ${aspnet-user-identity} : the wedsite user. If unauthenticated, then this will be blank.

Note that that this information can only be captured if you have a reference to the NLog for Extended Profile dll.

Next we will create the database logger. Before we continue, we need to make sure that a database exists (sqllite or sqlserver) and that a table called Logs is also present. If you don’t have one, the script to create it is supplied below:

CREATE TABLE [dbo].[Logs](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EventDateTime] [datetime] NOT NULL,
	[EventLevel] [nvarchar](100) NOT NULL,
	[UserName] [nvarchar](100) NOT NULL,
	[MachineName] [nvarchar](100) NOT NULL,
	[EventMessage] [nvarchar](max) NOT NULL,
	[ErrorSource] [nvarchar](100) NULL,
	[ErrorClass] [nvarchar](500) NULL,
	[ErrorMethod] [nvarchar](max) NULL,
	[ErrorMessage] [nvarchar](max) NULL,
	[InnerErrorMessage] [nvarchar](max) NULL,
	CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
)

Make sure you run this script against your database! Also, depending on the queries and reports you may generate in the future, you may want to rethink about your keys. In this example I will create a ‘useless’ key for educational purposes. Feel free to edit it as you see fit.  NLog can take it one step further and manage the database and table creation by adding the appropriate commands in the config file, but in the scenario I decide to simplify the overall process and keep the possibility of an error to a minimum.

Now we are ready to create the NLog configuration for the database. The important properties are the connectionString and the commandText. Finally, the parameters are used to map between the NLog values and the insert command values.

<target name="database" xsi:type="Database" keepConnection="true" useTransactions="true"
             dbProvider="System.Data.SqlClient" 
             connectionString="data source=localhost;initial catalog=NLogTest;integrated security=false;persist security info=True;User ID=sa;Password=Password1" 
              commandText="INSERT INTO Logs(EventDateTime, EventLevel, UserName, MachineName, EventMessage, ErrorSource, ErrorClass, ErrorMethod, ErrorMessage, InnerErrorMessage) VALUES (@EventDateTime, @EventLevel, @UserName, @MachineName, @EventMessage, @ErrorSource, @ErrorClass, @ErrorMethod, @ErrorMessage, @InnerErrorMessage)">    
      <!-- parameters for the command -->
      <parameter name="@EventDateTime" layout="${date:s}" />
      <parameter name="@EventLevel" layout="${level}" />
      <parameter name="@UserName" layout="${aspnet-user-identity}" />
      <parameter name="@MachineName" layout="${machinename}" />
      <parameter name="@EventMessage" layout="${message}" />
      <parameter name="@ErrorSource" layout="${event-context:item=error-source}" />
      <parameter name="@ErrorClass" layout="${event-context:item=error-class}" />
      <parameter name="@ErrorMethod" layout="${event-context:item=error-method}" />
      <parameter name="@ErrorMessage" layout="${event-context:item=error-message}" />
      <parameter name="@InnerErrorMessage" layout="${event-context:item=inner-error-message}" />
    </target>

Before any logging takes place, we need to tell NLog which log levels to capture. You could either create a rule for each target or you could roll all of them into one as I will demonstrate below:

<rules>
    <!-- add your logging rules here -->
    <logger name="*" minlevel="Debug" writeTo="asyncFile,database" />
</rules>

This rule simple instructs NLog to log all the entries at Debug level and above to both targets. NLog supports the following Log Levels:

• Trace - very detailed logs, which may include high-volume information such as protocol payloads. This log level is typically only enabled during development
• Debug - debugging information, less detailed than trace, typically not enabled in production environment.
• Info - information messages, which are normally enabled in production environment
• Warn - warning messages, typically for non-critical issues, which can be recovered or which are temporary failures
• Error - error messages
• Fatal - very serious errors

This are all the configuration changes you need to do to setup NLog. The final step is to create a logger in our application and start logging.

For the internal logging to work you need to open the Global.asax and add the following two lines to the Application_Start() to indicate where the InternalLogger should log the exceptions

string nlogPath = Server.MapPath("nlog-web.log");
InternalLogger.LogFile = nlogPath;
InternalLogger.LogLevel = NLog.LogLevel.Trace;

Finally, you can instantiate a logger and start logging in your controllers. I have added some basic log statements in my Home controller:

private static Logger logger = LogManager.GetCurrentClassLogger();

        public ActionResult Index()
        {
            ViewBag.Message = "Modify this template to jump-start your ASP.NET MVC application.";
            logger.Info("You have visited the Index view");
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your app description page.";
            logger.Info("You have visited the About view");
            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";
            logger.Info("You have visited the Contact view");
            return View();
        }
// code below omitted for clarity...

To create an instance of a logger, just call the

LogManager.GetCurrentClassLogger();

To log a message, call the

logger.Info(“you custom message goes here”);

More information and examples how to use NLog can be found at the NLog wiki

Happy coding….