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?

Answer 1

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)" />
....

Answer 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>