How do I log a custom field in NLog to database?

Question

I currently use NLog on a lot of projects. On some, I log to a database.

Here is what I would like to do:

CREATE TABLE [dbo].[NLogEntries](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [Origin] [nvarchar](100) NOT NULL,
  [LogLevel] [nvarchar](20) NOT NULL,
  [Message] [nvarchar](3600) NOT NULL,
  [CreatedOn] [datetime] NOT NULL,
  [OrderId] [int] NULL --Custom field!
)

And NLog.config with this target:

<target type="Database" name="database" connectionstring="Server=localhost;Database=NLog;Trusted_Connection=True;">
  <commandText>
    INSERT INTO NLogEntries ([Origin], [Message], [LogLevel],[CreatedOn],[OrderId]) VALUES (@Origin,@Message,@LogLevel,@Date, @OrderId);
  </commandText>
  <parameter name="@Date" layout="${date}"/>
  <parameter name="@Origin" layout="${callsite}"/>
  <parameter name="@LogLevel" layout="${level}"/>
  <parameter name="@message" layout="${message}"/>
  <parameter name="@OrderId" layout="${orderId}"/> <!-- custom field! -->
</target>

And then log something like this:

var logger = LogManager.GetCurrentClassLogger();
var orderId = 123;
logger.Debug("What is going on here", orderId);

Is there a good way to do this and keep using NLog? Or do I have to roll my own logger and skip NLog when these are the requirements?

maybe this can be of some help : http://nlog-forum.1685105.n2.nabble.com/Custom-Parameters-in-Database-Insert-td5557794.html — jbl, Sep 14 '12 at 12:50
Rich>> You mean other than querying in the logfile later will be difficult (require splitting etc)? There is a reason why we have relational databases with separate data in separate fields. — Kjensen, Sep 24 '12 at 08:54

Michael Freidgeim · Answer 1 · 2022-02-06T03:57:44.600

91

UPDATE 11 Feb 2022: newer versions of NLOG have other solutions- see Julian’s answer.

Rather than using GDC, which is for global static data and fails on concurrent logging, it is better to use EventProperties-Layout-Renderer that allows to pass custom properties specific for the event

LogEventInfo theEvent = new LogEventInfo(logLevel, "", message);
theEvent.Properties["OrderId"] =orderId;`

log.Log(theEvent);

... and in your NLog.config file: 
${event-context:item=OrderId}  -- obsolete
${event-properties:item=OrderId} -- renders OrderId

edited Feb 06 '22 at 03:57

answered Jul 20 '13 at 23:40

Michael Freidgeim

26,542
16
152
170

4

Could you add a source stating that GDC and MDC are now obsolete? I tried your solution first, but moved to the marked answer's solution since it's much cleaner - I don't have to define a new variable and manually set the log level and logger name. The only thing I could find about their 'deprecation' is their names have changed from GDC/MDC to what the marked answer uses (GlobalDiagnosticsContext) - http://nlog-project.org/2009/10/19/nlog-2-backwards-compatibility-and-breaking-change-policy.html – Brett Oct 17 '13 at 04:42
5

According to https://github.com/nlog/nlog/wiki/Layout-Renderers MDC and NDC provided for compatibility with log4net. – Michael Freidgeim Oct 18 '13 at 00:07
2

With a custom LogEventInfo instance, you also have to set Timestamp / LoggerName manually which is not as convenient as using GDC. – Dresel Jan 29 '15 at 21:11
7

`${event-context}` is deprecated now, you should use [`${event-properties}`](https://github.com/nlog/NLog/wiki/EventProperties-Layout-Renderer) instead. – Sergey Kolodiy Oct 01 '15 at 12:03
This sounds more reliable solution then accepted answer – Mihir May 17 '16 at 08:40
makes it more complicated. wageoghe's answer is better. – Doruk Aug 13 '18 at 12:10
1

@Doruk, “Use the Global Diagnostics Context when you want to make certain information available to **every** logger in the current process.” from https://github.com/nlog/nlog/wiki/Gdc-Layout-Renderer – Michael Freidgeim Aug 13 '18 at 12:47

score 37 · Answer 2 · edited May 23 '17 at 12:26

37

Here is one approach using the GlobalContext.

Configuration:

<target type="Database" name="database" connectionstring="Server=localhost;Database=NLog;Trusted_Connection=True;">
  <commandText>
    INSERT INTO NLogEntries ([Origin], [Message], [LogLevel],[CreatedOn],[OrderId]) VALUES (@Origin,@Message,@LogLevel,@Date, @OrderId);
  </commandText>
  <parameter name="@Date" layout="${date}"/>
  <parameter name="@Origin" layout="${callsite}"/>
  <parameter name="@LogLevel" layout="${level}"/>
  <parameter name="@message" layout="${message}"/>
  <parameter name="@OrderId" layout="${gdc:OrderId}"/> <!-- custom field! -->
</target>

Call site:

var logger = LogManager.GetCurrentClassLogger();
GlobalDiagnosticsContext.Set("OrderId",123);
logger.Debug("What is going on here"); //If you use the logging configuration above, 123 will be logged to the OrderId column in your database

With a little more effort, you could wrap the NLog logger using one of the techniques illustrated here.

Or, you could create your own "context" object and write a custom LayoutRenderer to pull the values from it and write them to the log. Custom LayourRenderers are easy to write. You can see one example in my first answer to this question. There, I show how to write your own LayoutRenderer that appends the current value of System.Diagnostics.Trace.CorrelationManager.ActivityId to the log message.

edited May 23 '17 at 12:26

Community

1
1

answered Sep 14 '12 at 15:23

wageoghe

27,390
13
88
116

GlobalDiagnosticsContext has some static methods but it is not a dictionary, something changes or? – Nuri YILMAZ Dec 07 '12 at 12:16
You're right, it's GlobalContetxt not GlobalDiagnosticContext and it's not a dictionary, but it does contain a dictionary which it exposes via the Properties property. – wageoghe Dec 07 '12 at 14:59
Ignore that last comment. I've updated the sample code so that it is correct (or more correct, anyway). – wageoghe Dec 07 '12 at 15:07
GlobalDiagnosticsContext.Set() takes 2 string variables as parameters so it GlobalDiagnosticsContext.Set("OrderId","123"); should work – Unix von Bash Oct 10 '14 at 12:31
Hello, I have already write this code in Controller - MappedDiagnosticsContext.Set("UserName", LoggedUser.Name); and add this in config - , but this way can not to write username in Db, do you have some recommendation ? – Avtandil Kavrelishvili Nov 17 '15 at 10:17
If you are putting your value in the MappedDiagnosticsContext, then you must use the mdc layout renderer. You are using gdc. – wageoghe Nov 17 '15 at 18:07
7

Doesn't it cause problem with concurrent writes? - I mean one thread sets the value and another thread overwrites the value which will be then written by the first thread – Mihir May 17 '16 at 08:40
1

See answer below by Michael Freidgeim, this solution fails for concurrent writes. – Fran Hoey Sep 29 '16 at 16:44
Great... Its great help for me. setting in just constructor and work for all methods – Mansinh Mar 19 '18 at 12:41

Julian · Accepted Answer · 2023-03-09T22:23:18.593

NLog 4.5 introduces structured logging, so you can do this:

logger.Debug("What is going on here. OrderId={MyOrderId}", orderId);

With NLog 4.6.3 it possible to use WithProperty:

Call

int orderId = 123; 
logger.WithProperty("MyOrderId", orderId).Info("This is my message!");

Config:

<target type="Database" name="database" connectionstring="Server=localhost;Database=NLog;Trusted_Connection=True;">
  <commandText>
    INSERT INTO NLogEntries ([Origin], [Message], [LogLevel],[CreatedOn],[OrderId]) VALUES (@Origin,@Message,@LogLevel,@Date, @OrderId);
  </commandText>
  <parameter name="@Date" layout="${date}" dbType="DbType.Date"/>
  <parameter name="@Origin" layout="${callsite}"/>
  <parameter name="@LogLevel" layout="${level}"/>
  <parameter name="@message" layout="${message}"/>
  <parameter name="@OrderId" layout="${event-properties:MyOrderId}" dbType="DbType.Int32"/> <!-- custom field! Note also the DB Type -->
</target>

Note, NLog 4.6 has also support for DbType - See https://nlog-project.org/2019/03/20/nlog-4-6-is-live.html

I think this is the best solution for newer versions of NLog. +1. — Alexei - check Codidact, Sep 26 '19 at 13:36

score 8 · Answer 4 · edited Nov 17 '16 at 14:43

If that's all one needs, as of NLog version 4.3.3 there's an easier way to declare and access custom variables. Beware: none of these solutions are thread-safe.

Add the following to the NLog.config

<nlog ...
    <!-- optional, add some variables -->  
    ...
    <variable name="myvarone" value="myvalue"/>
    <variable name="myvartwo" value=2/>
     ...
</nlog>

Variables can be changed/accessed in the code by:

LogManager.Configuration.Variables["myvarone"] = "New Value"
LogManager.Configuration.Variables["myvartwo"] = 2

The values can be referenced in NLog.config:

"${var:myvarone}"  -- renders "New Value"
"${var:myvartwo}"  -- renders 2

As I mentioned above var and LogEventInfo objects are global. So if multiple instances are defined, changing a value would change the value for all instances. I'm very interested if anyone knows a way to declare per instance custom variables for NLog.

Michael's answer above is thread safe. it's not using a Global LogEventInfo, he's creating a new instance. — Fran Hoey, Sep 29 '16 at 16:46

score 2 · Answer 5 · edited Sep 25 '16 at 00:10

2

You can use the MDC code:

var logger = LogManager.GetCurrentClassLogger();

MDC.Set("OrderId", 123);
MDC.Set("user", HttpContext.Current.User.Identity.Name);
// ... and so on

also check this http://weblogs.asp.net/drnetjes/archive/2005/02/16/374780.aspx

edited Sep 25 '16 at 00:10

Julian

33,915
22
119
174

answered Sep 23 '12 at 14:42

Eldar

862
9
22

How do I log a custom field in NLog to database?

5 Answers5

Linked