In this short blog post I’m going to show you how to log to the database using ASP.NET Core and NLog.
I assume you already have an ASP.NET Core project, so we’re going to skip this step. If you don’t, then you could create a simple API project to test this.
1. Install NLog and SqlClient
Add these lines to your .csproj file, inside an ItemGroup:
<PackageReference Include="Microsoft.Data.SqlClient" Version="2.0.0" />
<PackageReference Include="NLog" Version="4.7.2" />
<PackageReference Include="NLog.Web.AspNetCore" Version="4.9.2" />
<PackageReference Include="NLog.Extensions.Logging" Version="1.6.4" />
Note: NLog.Extensions.Logging isn’t needed per se, but it lets us use configsetting which links directly to the appsettings.json as you’ll see in a bit.
2. Create nlog.config file and add this:
<?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="Info"
internalLogFile="temp\internal-nlog.txt">
<!-- enable asp.net core layout renderers -->
<extensions>
<add assembly="NLog.Web.AspNetCore"/>
</extensions>
<!-- the targets to write to -->
<targets>
<target name="db"
xsi:type="Database"
connectionString="${configsetting:item=Logging.NLog}"
dbProvider="Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient"
commandType="StoredProcedure"
commandText="[dbo].[NLog_AddEntry_p]"
>
<parameter name="@machineName" layout="${machinename}" />
<parameter name="@logged" layout="${date}" />
<parameter name="@level" layout="${level}" />
<parameter name="@message" layout="${message}" />
<parameter name="@logger" layout="${logger}" />
<parameter name="@properties" layout="${all-event-properties:separator=|}" />
<parameter name="@callsite" layout="${callsite}" />
<parameter name="@exception" layout="${exception:tostring}" />
</target>
</targets>
<!-- rules to map from logger name to target -->
<rules>
<!--Skip non-critical Microsoft logs and so log only own logs-->
<logger name="Microsoft.*" maxlevel="Info" final="true" />
<!-- BlackHole without writeTo -->
<logger name="*" minlevel="Trace" writeTo="db" />
</rules>
</nlog>
Next, inside Visual Studio, right click on this file and click Properties. Then, select Copy if newer against Copy to Output Directory.
Note connectionString=”${configsetting:item=Logging.NLog}” – here we use the keyword configsetting that’s available thanks to NLog.Extensions.Logging package.
Also, internalLogFile=”temp\internal-nlog.txt“ will create a temp folder inside your project and will fill this text file with logs that are NLog related. This will help you debug any issues related to NLog in the future.
3. Add connection string inside appsettings.json to match the config file
"Logging": {
"IncludeScopes": false,
"LogLevel": {
"Default": "Trace",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
},
"NLog": "Server=(localdb)\\MSSQLLocalDB;Initial Catalog=YOUR_DATABASE_HERE;Integrated Security=SSPI;"
}
4. Create a new database (if you don’t already have one) and replace YOUR_DATABASE_HERE with its name
5. Execute this procedure inside your database to create a NLog table in which your logs will reside
CREATE TABLE [dbo].[NLog] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[MachineName] [nvarchar](200) NULL,
[Logged] [datetime] NOT NULL,
[Level] [varchar](5) NOT NULL,
[Message] [nvarchar](max) NOT NULL,
[Logger] [nvarchar](300) NULL,
[Properties] [nvarchar](max) NULL,
[Callsite] [nvarchar](300) 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] TEXTIMAGE_ON [PRIMARY];
GO
CREATE PROCEDURE [dbo].[NLog_AddEntry_p] (
@machineName nvarchar(200),
@logged datetime,
@level varchar(5),
@message nvarchar(max),
@logger nvarchar(300),
@properties nvarchar(max),
@callsite nvarchar(300),
@exception nvarchar(max)
) AS
BEGIN
INSERT INTO [dbo].[NLog] (
[MachineName],
[Logged],
[Level],
[Message],
[Logger],
[Properties],
[Callsite],
[Exception]
) VALUES (
@machineName,
@logged,
@level,
@message,
@logger,
@properties,
@callsite,
@exception
);
END
6. Add logging inside Program.cs
public class Program
{
public static void Main(string[] args)
{
var logger = NLogBuilder.ConfigureNLog("nlog.config").GetCurrentClassLogger();
try
{
logger.Debug("Starting host builder");
CreateHostBuilder(args).Build().Run();
}
catch (Exception exception)
{
logger.Error(exception, "Stopped program because of exception");
throw;
}
finally
{
NLog.LogManager.Shutdown();
}
}
public static IHostBuilder CreateHostBuilder(string[] args) =>
Host.CreateDefaultBuilder(args)
.ConfigureWebHostDefaults(webBuilder =>
{
webBuilder.UseStartup<Startup>();
})
.ConfigureLogging(logging =>
{
logging.ClearProviders();
logging.SetMinimumLevel(LogLevel.Trace);
})
.UseNLog();
}
7. Inject ILogger to the controller
Inside your controller, let’s call it ProductsController, inject the ILogger interface.
private readonly ILogger<ProductsController> _logger;
public ProductsController(ILogger<ProductsController> logger)
{
_logger = logger;
}
8. Start Logging
Let’s say you have a Get method that accepts and id
public async Task<IActionResult> Get(int id)
{
var result = await _someFakeService.GetProductById(id)
if (result == null)
{
_logger.LogWarning($"Message with id {id} doesn't exist.");
return NotFound("Product not found");
}
return Ok(result);
}
Now, if you’ve followed along you should receive a new record in your database every time you hit this endpoint. Of course, in this example a new record would be inserted only if the product doesn’t exist, but that’s easily changeable.
Hope you’ve found this useful.
