For many reasons it’s quite useful to output the SQL commands into a log file. Especially for that case the ObjectMapper .NET offers the interface ISqlTracer which can be implemented by your application in order to log the sql output.

For logging purpose Log4Net is most time the first choice. Would’nt it be nice to marriage both technologies, the ObjectMapper .NET and Log4Net? That’s in fact possible and this tutorial will show how to implement it.

First thing we have to do is implementing the ISqlTracer interface. Have a look at the implementation below. I cuttet some basic stuff in order to save a little desktop space.

      /// <summary>

      /// This class traces the SQL Commands using log4net

      /// </summary>

      public class SqlTracer : ISqlTracer

      {

            private static readonly ILog log = LogManager.GetLogger(typeof (SqlTracer));

 

            …

            …

            …

 

            /// <summary>

            /// The persister uses this method to publish the sql command

            /// </summary>

            /// <param name="original">The original string without the parameter extension.</param>

            /// <param name="extended">The extended string including the parameter content</param>

            /// <param name="affactedRows">The rows which are touched by the sql statement.</param>

            /// <param name="duration">Duration of the sql statement</param>

            public void SqlCommand(string original, string extended, int affactedRows, TimeSpan duration)

            {

                  if (extended.StartsWith("SELECT"))

                        log.Debug(extended);

                  else

                        log.Info(extended);

            }

 

            /// <summary>

            /// The persister uses this method to publish info messages, like warnings and so on.

            /// </summary>

            /// <param name="message">Message text</param>

            /// <param name="source">Source</param>

            public void ErrorMessage(string message, string source)

            {

                  log.Error(string.Concat(message, "\nSource: ", source));

            }

 

            /// <summary>

            /// Must return true, if an error logging shall be enabled.

            /// </summary>

            /// <value>

            ///   <c>true</c> if traceing of errors shall be enabled; otherwise, <c>false</c>.

            /// </value>

            public bool TraceErrorEnabled

            {

                  get { return log.IsErrorEnabled; }

            }

 

            /// <summary>

            /// Must return true, if the SQL tracing shall be enabled

            /// </summary>

            /// <value>

            ///   <c>true</c> if tracing of sql shall be enabled; otherwise, <c>false</c>.

            /// </value>

            public bool TraceSqlEnabled

            {

                  get { return log.IsInfoEnabled; }

            }

      }

 

Second thing we need is the Log4Net config file that configures the Log4Net Settings.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

 

      <configSections>

            <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />

      </configSections>

 

      <log4net>

     

            <appender name="SqlFileAppender" type="log4net.Appender.FileAppender">

                  <file value="sql_logging.txt" />

                  <appendToFile value="true" />

                  <layout type="log4net.Layout.PatternLayout">

                        <conversionPattern value="%date [%thread] %-5level: %message%newline" />

                  </layout>

            </appender>

           

            <root>

                  <level value="WARN" />

            </root>

           

            <logger name="Utils.SqlTracer">

                  <level value="DEBUG" />

                  <appender-ref ref="SqlFileAppender" />

            </logger>

 

      </log4net>

 

</configuration>

 

To complete the Log4Net framework must be initialized once when starting the application. That’s only 2 lines of code.

            string log4NetConfig = Path.GetDirectoryName("..\\..\\")+"\\logging.config";

            log4net.Config.XmlConfigurator.Configure(new FileInfo(log4NetConfig));

 

And sure: Our new SqlTracer must be initialized too when creating the Persister.

            protected AccessPersister GetAccessPersister ()

            {

                  return new AccessPersister("accessDb.mdb", new SqlTracer());

            }

That’s all. After starting the application and running the NUnit Test Cases a sql_logging.txt file must appear in your output directory (bin\debug).

You can download the example application using the following link:

Tracing SQL commands using Log4Net