Log4Net can be configured to log the SQL statements generated by NHibernate. A couple of configuration approaches as below can be followed.
I. Use one single config file eg. App.config (or web.config) can be used to configure both NHibernate as well as log4net.
<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" /> <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" /> </configSections> <!-- This template was written to work with NHibernate.Test. Copy the template to your NHibernate.Test project folder and rename it in hibernate.cfg.xml and change it for your own use before compile tests in VisualStudio. --> <!-- This is the ByteFX.Data.dll provider for MySql --> <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" > <session-factory name="NHibernate.Test"> <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property> <property name="connection.connection_string"> server=127.0.0.1;uid=root;pwd=********;database=test;Allow Zero Datetime=true; </property> <property name="dialect">NHibernate.Dialect.MySQLDialect</property> <property name="show_sql">true</property> </session-factory> </hibernate-configuration> <!-- This section contains the log4net configuration settings --> <log4net debug="true"> <!-- Define some output appenders --> <!--appender name="trace" type="log4net.Appender.TraceAppender, log4net"> <layout type="log4net.Layout.PatternLayout,log4net"> <param name="ConversionPattern" value="%d{ABSOLUTE} %-5p %c{1}:%L - %m%n" /> </layout> </appender--> <appender name="console" type="log4net.Appender.ConsoleAppender, log4net"> <layout type="log4net.Layout.PatternLayout,log4net"> <param name="ConversionPattern" value="%d{ABSOLUTE} %-5p %c{1}:%L - %m%n" /> </layout> </appender> <appender name="SQLLogFile" type="log4net.Appender.RollingFileAppender,log4net" > <param name="File" value="logs/log-sql.txt" /> <param name="AppendToFile" value="false" /> <param name="RollingStyle" value="Date" /> <param name="DatePattern" value="yyyy.MM.dd" /> <param name="StaticLogFileName" value="true" /> <layout type="log4net.Layout.PatternLayout,log4net"> <param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" /> </layout> </appender> <!-- Setup the root category, add the appenders and set the default priority --> <!--root> <priority value="DEBUG" /> <appender-ref ref="console" /> <appender-ref ref="rollingFile" /> </root--> <logger name="NHibernate.SQL"> <level value="ALL" /> <appender-ref ref="SQLLogFile" /> <appender-ref ref="console" /> </logger> </log4net> </configuration>
After configuring as above, make sure to invoke below code from somewhere in your startup code.
log4net.Config.XmlConfigurator.Configure();
II. Alternatively, you can use separate config files for Nhibernate as well as Log4Net. (preferred way).
-Add config file (hibernate.cfg.xml) for NHibernate
<?xml version="1.0" encoding="utf-8"?>
<!--
This template was written to work with NHibernate.Test.
Copy the template to your NHibernate.Test project folder and rename it in hibernate.cfg.xml and change it
for your own use before compile tests in VisualStudio.
-->
<!-- This is the ByteFX.Data.dll provider for MySql -->
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
<session-factory name="NHibernate.Test">
<property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
<property name="connection.connection_string">
server=127.0.0.1;uid=root;pwd=********;database=test;Allow Zero Datetime=true;
</property>
<property name="dialect">NHibernate.Dialect.MySQLDialect</property>
<property name="show_sql">true</property>
</session-factory>
</hibernate-configuration>
-Add config file (log4net.cfg.xml) for Log4Net
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
</configSections>
<!-- This section contains the log4net configuration settings -->
<log4net debug="true">
<!-- Define some output appenders -->
<!--appender name="trace" type="log4net.Appender.TraceAppender, log4net">
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d{ABSOLUTE} %-5p %c{1}:%L - %m%n" />
</layout>
</appender-->
<appender name="console" type="log4net.Appender.ConsoleAppender, log4net">
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d{ABSOLUTE} %-5p %c{1}:%L - %m%n" />
</layout>
</appender>
<appender name="SQLLogFile" type="log4net.Appender.RollingFileAppender,log4net" >
<param name="File" value="logs/log-sql.txt" />
<param name="AppendToFile" value="false" />
<param name="RollingStyle" value="Date" />
<param name="DatePattern" value="yyyy.MM.dd" />
<param name="StaticLogFileName" value="true" />
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
</layout>
</appender>
<!-- Setup the root category, add the appenders and set the default priority -->
<!--root>
<priority value="DEBUG" />
<appender-ref ref="console" />
<appender-ref ref="rollingFile" />
</root-->
<logger name="NHibernate.SQL">
<level value="ALL" />
<appender-ref ref="SQLLogFile" />
<appender-ref ref="console" />
</logger>
</log4net>
</configuration>
// Configure log4net using the config file
[assembly: log4net.Config.XmlConfigurator(ConfigFile = "log4net.cfg.xml", Watch = true)]
-After configuring as above, make sure to invoke below code from somewhere in your startup code.
log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
And log will be as in the below:
2014-08-31 20:05:33,725 [4] DEBUG NHibernate.SQL - INSERT INTO User (FName, LName, PlaceOfBirth, Id) VALUES (?p0, ?p1, ?p2, ?p3);?p0 = 'John' [Type: String (6)], ?p1 = 'Smith' [Type: String (7)], ?p2 = 'London' [Type: String (5)], ?p3 = 9929769d-a95a-4f71-9cf8-4e93fdff54ce [Type: Guid (0)]
