how to insert into database from stored procedure in log4net?

Question

I have to log thread context properties like this:

string logFilePath = AppDomain.CurrentDomain.BaseDirectory + "log4netconfig.xml";
FileInfo finfo = new FileInfo(logFilePath);
log4net.Config.XmlConfigurator.ConfigureAndWatch(finfo);
ILog logger = LogManager.GetLogger("Exception.Logging");

log4net.ThreadContext.Properties["MESSAGE"] = exception.Message;
log4net.ThreadContext.Properties["MODULE"] = "module1";
log4net.ThreadContext.Properties["COMPONENT"] = "component1";
logger.Debug("test");

and the configuration file is:

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

  <log4net>
    <logger name="Exception.Logging" level="Debug">
      <appender-ref ref="AdoNetAppender"/>
    </logger>
    <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
      <connectionString value="Data Source=xe;User ID=test;Password=test;" />
      <connectionType value="System.Data.OracleClient.OracleConnection, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <bufferSize value="10000"/>
      <commandText value="Log_Exception_Pkg.Insert_Log" />
      <commandType value="StoredProcedure" />

      <parameter>
        <parameterName value="@p_Error_Message" />
        <dbType value="String" />
        <size value="255" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%property{MESSAGE}"/>
        </layout>
      </parameter>

      <parameter>
        <parameterName value="@p_Module" />
        <dbType value="String" />
        <size value="225" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%property{MODULE}"/>
        </layout>
      </parameter>

      <parameter>
        <parameterName value="@p_Component" />
        <dbType value="String" />
        <size value="225" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%property{COMPONENT}"/>
        </layout>
      </parameter>
    </appender>
  </log4net>
</configuration>

But it's not inserting them in the database. How can I get that to work?

Try to turn on internal debugging (http://logging.apache.org/log4net/release/faq.html). Also note: Log4net can deal with exceptions you do not really need to use the thread context properties for that... — Stefan Egli, Aug 11 '10 at 11:59
Thanks a lot Stefan. Its done now. I removed '@' from parameter name. And it worked finally:) — Sam P, Aug 13 '10 at 05:10

score 2 · Answer 1 · edited Nov 04 '13 at 14:59

2

Simple answer: Because this is an Oracle connection remove the @ symbol from the parameter name and it will work.

Sample:

<parameter>
    <parameterName value="@p_Module" />
    <dbType value="String" />
    <size value="225" />
    <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%property{MODULE}"/>
    </layout>
</parameter>

edited Nov 04 '13 at 14:59

Philipp M

1,877
7
27
38

answered Jan 04 '11 at 16:50

Paul Lemke

5,494
3
47
66

score 0 · Answer 2 · answered Dec 11 '13 at 13:51

I had similar problem, when log4net table was in different schema. (it surely applies to stored procedure or package too if they are in different schema)

Don't forget to type your schema name before table or stored procedure then.

Sample:

    <log4net>    
        <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
        <connectionType value="Oracle.DataAccess.Client.OracleConnection, Oracle.DataAccess, Version=4.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
        <connectionString ..../>
        <commandText value="INSERT INTO schemaName.LOG4NET (LOG_DATE, LOG_LEVEL, LOG_IDENTITY, LOG_MESSAGE, LOG_EXCEPTION) VALUES (:log_date, :log_level, :log_identity, :log_message, :log_exception)" />
....

how to insert into database from stored procedure in log4net?

2 Answers2