Setting the NLog database connection string in the ASP.NET Core appsettings.json

This article shows how the NLog connection string for the DatabaseTarget can be configured in the appsettings.json in an ASP.NET Core project and not the XML nlog.config file. All the NLog target properties can be configured in code if required and not just in the NLog XML configuration file.

Code: VS2017 RC3 csproj | VS2015 project.json

2017.02.08 Updated to NLog.Web.AspNetCore 4.3.0 and VS2017 RC3
17.12.2016 Updated to ASP.NET Core 1.1

NLog posts in this series:

  1. ASP.NET Core logging with NLog and Microsoft SQL Server
  2. ASP.NET Core logging with NLog and Elasticsearch
  3. Settings the NLog database connection string in the ASP.NET Core appsettings.json
  4. .NET Core logging to MySQL using NLog
  5. .NET Core logging with NLog and PostgreSQL

The XML nlog.config file is the same as in the previous post, with no database connection string configured.

<?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"
      autoReload="true"
      internalLogLevel="Warn"
      internalLogFile="C:\git\damienbod\AspNetCoreNlog\Logs\internal-nlog.txt">

           
  <targets>

    <target name="database" xsi:type="Database" >

<!-- THIS is not required, read from the appsettings.json

<connectionString>${var:connectionString}</connectionString>
-->

<!--
  Remarks:
    The appsetting layouts require the NLog.Extended assembly.
    The aspnet-* layouts require the NLog.Web assembly.
    The Application value is determined by an AppName appSetting in Web.config.
    The "NLogDb" connection string determines the database that NLog write to.
    The create dbo.Log script in the comment below must be manually executed.

  Script for creating the dbo.Log table.

  SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
  CREATE TABLE [dbo].[Log] (
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Application] [nvarchar](50) NOT NULL,
      [Logged] [datetime] NOT NULL,
      [Level] [nvarchar](50) NOT NULL,
      [Message] [nvarchar](max) NOT NULL,
      [Logger] [nvarchar](250) NULL,
      [Callsite] [nvarchar](max) NULL,
      [Exception] [nvarchar](max) NULL,
    CONSTRAINT [PK_dbo.Log] PRIMARY KEY CLUSTERED ([Id] ASC)
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
-->

          <commandText>
              insert into dbo.Log (
              Application, Logged, Level, Message,
              Logger, CallSite, Exception
              ) values (
              @Application, @Logged, @Level, @Message,
              @Logger, @Callsite, @Exception
              );
          </commandText>

          <parameter name="@application" layout="AspNetCoreNlog" />
          <parameter name="@logged" layout="${date}" />
          <parameter name="@level" layout="${level}" />
          <parameter name="@message" layout="${message}" />

          <parameter name="@logger" layout="${logger}" />
          <parameter name="@callSite" layout="${callsite:filename=true}" />
          <parameter name="@exception" layout="${exception:tostring}" />
      </target>
      
  </targets>

  <rules>
    <logger name="*" minlevel="Trace" writeTo="database" />
      
  </rules>
</nlog>

The NLog DatabaseTarget connectionstring is configured in the appsettings.json as described in the ASP.NET Core configuration docs.

{
    "Logging": {
        "IncludeScopes": false,
        "LogLevel": {
            "Default": "Debug",
            "System": "Information",
            "Microsoft": "Information"
        }
    },
    "ElasticsearchUrl": "http://localhost:9200",
    "ConnectionStrings": {
        "NLogDb": "Data Source=N275\\MSSQLSERVER2014;Initial Catalog=Nlogs;Integrated Security=True;"
    }
}

The configuration is then read in the Startup constructor.

public Startup(IHostingEnvironment env)
{
	var builder = new ConfigurationBuilder()
		.SetBasePath(env.ContentRootPath)
		.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
		.AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
		.AddEnvironmentVariables();
	Configuration = builder.Build();
}

The Nlog DatabaseTagert is then configured to use the connection string from the app settings and sets all the DatabaseTarget instances for NLog to use this. All target properties can be configured in this way if required.

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
	loggerFactory.AddNLog();

	//add NLog.Web
	app.AddNLogWeb();

	////foreach (DatabaseTarget target in LogManager.Configuration.AllTargets.Where(t => t is DatabaseTarget))
	////{
	////	target.ConnectionString = Configuration.GetConnectionString("NLogDb");
	////}
	
	////LogManager.ReconfigExistingLoggers();

	LogManager.Configuration.Variables["connectionString"] = Configuration.GetConnectionString("NLogDb");
	LogManager.Configuration.Variables["configDir"] = "C:\\git\\damienbod\\AspNetCoreNlog\\Logs";

	app.UseMvc();
}

Links

https://github.com/NLog/NLog.Extensions.Logging

https://github.com/NLog

https://github.com/NLog/NLog/blob/38aef000f916bd5ffd8b80a5576afa2423192e84/examples/targets/Configuration%20API/Database/MSSQL/Example.cs

https://docs.asp.net/en/latest/fundamentals/logging.html

https://msdn.microsoft.com/en-us/magazine/mt694089.aspx

https://github.com/nlog/NLog/wiki/Database-target

https://docs.asp.net/en/latest/fundamentals/configuration.html

Advertisements

6 comments

  1. Ristogod · · Reply

    I tried this in my application and simply could not get it to work. If you put the connection string directly in the nlog.config database target, it works fine. But if I try and set it using the above code, it fails every time.

    Error when writing to database. Exception: System.Data.SqlClient.SqlException: Invalid object name ‘dbo.LogEntries’.

    1. Bill Moniz · · Reply

      I realise that your question is from a long time ago, but I just ran into the same problem, so I thought I’d post the solution here for future travellers…

      The problem is this commented-out line:

      <!– THIS is not required, read from the appsettings.json

      ${var:connectionString}
      –>

      It actually IS required. We still need to give NLog the parameter, even though the value will only be provided in appsettings.json.

      If you un-comment line 15 of the nlog.config, it will work as described. 🙂

  2. Hi Ristogod

    If you set a breakpoint at the the target.ConnectionString, is the connection string correct in the debugger?

    Greetings Damien

  3. Ali Raza · · Reply

    Hi Can you tell me how to log for MySql database ? Hopefully you will reply soon.

  4. Valeriy · · Reply

    Hi!


    If you comment this line, then NLog silently crashes with error:

    “Error when writing to database. Exception: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) —> System.ComponentModel.Win32Exception”

    May be, this error was raised for application hosted on IIS… If you uncomment this line, then NLog works correctly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: