Monthly Archives

Using NLog to Log to a Database with Entity Framework Code First

NLog is a logging framework that is used to add logging capabilities to a .NET application. Today I am going to show how to use NLog to generate logs on a database created by Entity Framework Code First.

The version of NLog used here is NLog 3.2.0.

Summary

Say you have an existing solution that:

  • Already has a database context.
  • Already has NLog installed.
  • Already has the NLog config file added.

Enabling NLog logging to the database consists of the following steps:

  1. Create the entity that represents the log entry and update the database appropriately.
  2. Configure NLog to log the database table that holds the log entries.

Create the entity that represents the log entry and update the database appropriately

This is no different than adding a normal business entity. In this tutorial I will use a LogEntry class that looks like this:


public class LogEntry
{
public string CallSite { get; set; }
public string Date { get; set; }
public string Exception { get; set; }
public int Id { get; set; }
public string Level { get; set; }
public string Logger { get; set; }
public string MachineName { get; set; }
public string Message { get; set; }
public string StackTrace { get; set; }
public string Thread { get; set; }
public string Username { get; set; }
}

This will create a LogEntries table with an Id primary key identity column. The rest of the fields would have types of nullable nvarchar(max). Of course, this can be modified through configuration.

Configure NLog to log the database table that holds the log entries

Now it’s time to tell NLog to log to the database we created. We can use the connection string name to achieve this. Let’s say we have a connection string like this:


<connectionStrings>
<add name="MyContext" connectionString="Server=.SQLEXPRESS;Database=myDatabase;Trusted_Connection=True;Connection Timeout=30;" providerName="System.Data.SqlClient" />
</connectionStrings>

The NLog.config file can look like 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">
<targets async="true">
<target xsi:type="Database"
name="database"
connectionStringName="MyContext"
commandText="INSERT INTO [dbo].[LogEntries] ([CallSite], [Date], [Exception], [Level], [Logger], [MachineName], [Message], [StackTrace], [Thread], [Username]) VALUES (@CallSite, @Date, @Exception, @Level, @Logger, @MachineName, @Message, @StackTrace, @Thread, @Username);">
<parameter name="@CallSite" layout="${callsite:filename=true}" />
<parameter name="@Date" layout="${longdate}" />
<parameter name="@Exception" layout="${exception}" />
<parameter name="@Level" layout="${level}" />
<parameter name="@Logger" layout="${logger}" />
<parameter name="@MachineName" layout="${machinename}" />
<parameter name="@Message" layout="${message}" />
<parameter name="@StackTrace" layout="${stacktrace}" />
<parameter name="@Thread" layout="${threadid}" />
<parameter name="@Username" layout="${windows-identity:domain=true}" />
</target>
</targets>
<rules>
<logger name="*" minlevel="Info" writeTo="database" />
</rules>
</nlog>

Notice how:

  • The async="true" attribute was used in the targets element. This enables async logging.
  • The connectionStringName is used instead of the actual connection string. This means that if we have different connection strings for debug or release modes, NLog will use the appropriate connection string.
  • A hard-coded INSERT statement is used. It’s not likely that the LogEntry entity will change, but if it does, the statement here should be updated as well.

Using this setup, log entries will get persisted to the database.

Update: If pluralizations are turned off, the name of the database table will be LogEntry instead of LogEntries. Make sure that the correct name is reflected in the config file.

Conclusion

In this post I showed how to use NLog to log to a database that was created using Entity Framework Code First. I was actually surprised that the setup process was simple and straightforward. I hope this will help you in your projects.

Read More...

Adding an SQL Azure Linked Server

When adding an SQL Azure Linked Server through SSMS, you will encounter an error if you go through the normal way (Server Objects > Linked Servers > New Linked Server). That is because the New Linked Server wizard does not allow you to specify the catalog name if the chosen Server Type is SQL Server, which should be chosen when adding an SQL Azure Linked Server. To accomplish this, the stored procedure sp_addlinkedserver should be used.


Add the Linked Server

Following is a sample script using sp_addlinkedserver:


EXEC sp_addlinkedserver
@server='serverFriendlyName',
@srvproduct='',
@provider='sqlncli',
@datasrc='tcp:xxxxxxxxxx.database.windows.net,1433',
@location='',
@provstr='',
@catalog='databaseName'

Adding Credentials

You can add SQL Server Authentication credentials to the linked server by using the stored procedure sp_addlinkedsrvlogin:


exec sp_addlinkedsrvlogin 'serverFriendlyName', 'FALSE', NULL, 'username', 'password';

Querying the Linked Server

A sample query would look like:


SELECT * FROM [serverFriendlyName].[databaseName].[schema].[tableName]

Conclusion

In this post I showed how to add an SQL Azure Linked Server with credentials. I also showed a sample statement that queries the linked server.

Read More...