SQL Server Runner - Part 2: log4Net

Introduction

The purpose of this article is to demonstrate some of the log4Net capabilities.

Background

A ticket was logged at the SQL Runner website regarding the possibility of creating a consolidated file with all the scripts that were executed by the application. I thought log4Net could provide what this user was asking for with very few changes in the application. This article describes what changes were done to achieve so.

The source code for this article can be found at the SQL Runner Source Code repository, these modifications are available since version 2.0.1.3 RC1.

log4Net configuration

For a brief overview of the SQL Server Runner capabilities, you may want to look at my previous article about this application.

The log4Net library is used in the SQL Runner GUI application as the instrumentation mechanism. The command line version uses it to display the progress of the scripts' execution. The logger is setup at the application's configuration file: SQLRunner.exe.config.

Firstly, we need to define a new configuration section:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
      <section name="log4net" 
               type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
      ...
    </configSections>
...
<configuration>

The next thing you need to decide is what type of appenders you want to have. log4net allows logging requests to print to multiple destinations. In log4net speak, an output destination is called an appender. In the SQL Runner case, two appenders are found:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  ...
  <log4net>
    <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender, log4net" >
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date [%thread] %-5level - %message%newline" />
      </layout>
    </appender>
    <appender name="LogFileAppender" type="log4net.Appender.FileAppender,log4net">
      <param name="File" value="logs\\sqlrunner.log" />
      <param name="AppendToFile" value="true" />
      <layout type="log4net.Layout.PatternLayout,log4net">
        <param name="ConversionPattern" 
               value="%date [%thread] %-5level - %message%newline" />
      </layout>
    </appender>
    ...
  </log4net> 
  ...
</configuration>

The ConsoleAppender prints directly to the console, and the LogFileAppender prints to a file named sqlrunner.log.

The next component to define is the logger. In log4Net, loggers are created in a hierarchy structure by the name given to each of them. This naming convention is very similar to namespaces in your classes. A good idea is to use the fully qualified name of your classes when loggers are instantiated in your code.

The root logger is the parent of all loggers, and it is required that a level is assigned in the configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  ...
  <log4net>
    ...
    <root>
      <level value="INFO" />
        <appender-ref ref="ConsoleAppender" />
        <appender-ref ref="LogFileAppender" />      
    </root>
  </log4net>
  ...
</configuration>

The root's level is set to INFO, and both appenders are used. So, any code that calls the logger using the INFO level or above will print to both loggers.

How to modify the application to consolidate the executed scripts

The idea here is to create a new appender that prints to a new file: allscripts.sql, every time a script is executed. A new logger needs to be created as well. And finally, the code needs to be modified; only two new lines need to be added to get everything working.

Configuration file changes

The configuration file needs to be changed to:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  ...
  <log4net>
    <appender name="ScriptConsolidator" 
         type="log4net.Appender.FileAppender,log4net">
      <param name="File" value="logs\\allscripts.sql" />
      <param name="AppendToFile" value="true" />
      <layout type="log4net.Layout.PatternLayout,log4net">
        <param name="ConversionPattern" value="%message%newline" />
      </layout>
    </appender>
    ...
    <logger name="SQLRunnerLib.Runners.Runner">
      <level value="DEBUG" />
      <appender-ref ref="ScriptConsolidator" />
    </logger>
    ...
  </log4net>
  ...
</configuration>

Few aspects to notice are:

• The file appender pattern is very simple, it prints the message and a carriage return.
• The logger name matches the fully qualified class name of the class that will use the logger.
• The logger level was set to DEBUG and the logger uses the previously defined appender.

Code changes

Only two lines are required to create the consolidated script file. In SQL Runner, the Runner class executes the scripts when the ExecuteSQL method is called.

I need to create a local instance of an ILog that calls the factory method GetLogger in the LogManager. I pass the fully qualified class name of the Runner class to get the logger:

public sealed class Runner : IRunner
{
  #region Private Instances
    ...
    private readonly ILog _logger = LogManager.GetLogger(typeof (Runner));
    ...
  #endregion

  ...
}

Then, the only thing I need to do is to call the logger just before the script is executed:

public sealed class Runner : IRunner
{
  ...

  private void ExecuteSQL(FileInfo aFile){
  
    if ( IsCancelled ) return;
    try
    {
      OnProgressMsgCreated("Executing {0} script", aFile.Name);
      using (StreamReader aStream = new StreamReader(aFile.OpenRead()))
      {
        try
        {
          string strSQL = aStream.ReadToEnd();
          aStream.Close();

          strSQL = ReplacePlaceholders(strSQL, aFile.Name);
          _logger.Debug(strSQL);

          ...
        }
        catch (Exception e)
        {
          ...
        }
      }
    }
    catch (Exception e)
    {
      ...
    }
  }
  ...
}

Please note that the Debug method is used to print the script content.

Points of interest

Although most times logging is used for instrumentation purposes, you may find that using log4Net is also a flexible way to create application outputs of all kinds.

Relevant links

• log4net manual introduction
• SQL Server Runner website
• First CodeProject article regarding SQL Server Runner