Quickly implement nLogging to a MS SQL Database

The following blog post will give you a short toturial on implementing nLog into a system logging logs to the database.

Right click on a project/solution that you want to implement nLogging into, I just have a seperate assembly that I add to my projects.

select NuGet Packages

Search for nLog in the search bar in the top right hand corner, when the search result come back you should see something like below

Log NuGet Options

Install NLog, when finished close the dialog and open the app/web.config file in you exected project (website or client application, not class libraries). Add the configSections and nlog nodes to your config file as below. You will note that I have exception throwing and internal logging  turned on for nLog, this will help you easily resolve any issues that are encountered with the use of nLog.


	
mssql SQL SERVER HOST NAME YOUR DATABASE NAME USERNAME TO ACCESS THE DATABASE PASSWORD TO ACCESS THE DATABASE INSERT INTO tb_EventLogs([Source],[SubSource], [DateTimeStamp],[LogLevel], [Logger],[LogMessage]) VALUES('TFSSourceSync','TFS Application Tier', @time_stamp,@level,@logger,@message)

As you can see above we are specifying the SQL statement to use to insert the log data into our database, you can configure this as you want. You will if using my exact example be required to run the below SQL script againsr your database

CREATE TABLE [dbo].[tb_EventLogs](
	[EventLogID] [bigint] IDENTITY(1,1) NOT NULL,
	[Source] [varchar](20) NOT NULL,
	[SubSource] [varchar](20) NOT NULL,
	[DateTimeStamp] [datetime] NOT NULL,
	[LogLevel] [varchar](20) NOT NULL,
	[Logger] [varchar](100) NOT NULL,
	[LogMessage] [varchar](max) NOT NULL,
 CONSTRAINT [PK_EventLogs] PRIMARY KEY CLUSTERED 
(
	[EventLogID] 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]

You are now ready to log using nLog to a MS SQL Database. As a little bonus below is a extensions class that I use to make the logging a bit simplier. The code makes use of reflection to try help get a more detailed log with less effort.

using System;
using System.Reflection;
using NLog;

public static class MyNLogExtensions
{
    static MyNLogExtensions()
    {
        logger = LogManager.GetLogger("Database");
    }

    public static Logger logger;

    public static void LogInfo(this string message)
    {
        // message will not be computed if not necessary, 
        //thus helping improve performance when logs are disabled
        logger.Info(() => ReturnMessage(message));
    }

    public static void LogMessage(this string message, LogLevel logLevel)
    {
        // message will not be computed if not necessary, 
        //thus helping improve performance when logs are disabled
        logger.Log(logLevel, () => ReturnMessage(message));
    }

    public static void LogTrace(this string message, LogLevel logLevel)
    {
        // message will not be computed if not necessary, 
        //thus helping improve performance when logs are disabled
        logger.Trace(() => ReturnMessage(message));
    }

    public static void LogFatal(this string message, LogLevel logLevel)
    {
        // message will not be computed if not necessary, 
        //thus helping improve performance when logs are disabled
        logger.Fatal(() => ReturnMessage(message));
    }

    public static void LogWarning(this string message, bool showMessageBox = false)
    {
        // message will not be computed if not necessary, 
        //thus helping improve performance when logs are disabled
        logger.Warn(() => ReturnMessage(message));
    }

    public static void LogError(this string message, bool showMessageBox = false)
    {
        // message will not be computed if not necessary, 
        //thus helping improve performance when logs are disabled
        logger.Error(() => ReturnMessage(message));
    }

    public static void LogDebug(this string message, LogLevel logLevel)
    {
        // message will not be computed if not necessary, 
        //thus helping improve performance when logs are disabled
        logger.Debug(() => ReturnMessage(message));
    }

    public static void LogError(this Exception ex, bool showMessageBox = false)
    {
        // message will not be computed if not necessary, 
        //thus helping improve performance when logs are disabled
        logger.Error(() => GetExceptionMessage(ex, 0));
    }

    public static void LogWarning(this Exception ex, bool showMessageBox = false)
    {
        // message will not be computed if not necessary, 
        //thus helping improve performance when logs are disabled
        logger.Warn(() => GetExceptionMessage(ex, 0));
    }

    private static string ReturnMessage(string message)
    {
        return message;
    }

    private static string GetExceptionMessage(Exception ex, int exceptionInnerCount)
    {
        string result = string.Empty;

        result += "Exception --:-- " + exceptionInnerCount + Environment.NewLine;
        result += GetAllValueTypeAndStringValues(ex);
        if (ex.InnerException != null)
        {
            result += GetExceptionMessage(ex.InnerException, exceptionInnerCount+1);
        }
        return result;
    } 
    private static string GetAllValueTypeAndStringValues(object obj)
    {
        string result = string.Empty;

        if (obj != null)
        {
            result += "Object Value -:- " + obj.ToString() + Environment.NewLine;

            foreach (PropertyInfo pi in obj.GetType().GetProperties())
            {
                if (pi.PropertyType.IsValueType || pi.PropertyType == typeof(string))
                {
                    object val = pi.GetValue(obj);
                    result += pi.Name + " -:- " + 
                        (val == null ? "NULL" : val.ToString()) + 
                        Environment.NewLine;
                }
            }
        }

        return result;
    }
}

More information on nLog can be found on their site http://nlog-project.org/. If you have any questions about this, feel free to ask.

  • Piotr Trychta

    The configuration section is badly pasted.

Buffer