How do I use a stored procedure in log4net ADONetAppender?

Question

Hello, I am using the ADONetAppender to (try) to log data via a stored procedure (so that I may inject logic into the logging routine).

My configuration settings are listed below. Can anybody tell what I'm doing wrong?

Thanks!

<appender name="ADONetAppender_SqlServer" type="log4net.Appender.ADONetAppender">
  <bufferSize value="1" />
  <threshold value="ALL"/>
  <param name="ConnectionType" value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  <param name="ConnectionString" value="<MyConnectionString>" />
  <param name="UseTransactions" value="False" />
  <commandText value="dbo.LogDetail_via_Log4Net" />
  <commandType value="StoredProcedure" />
      <parameter>
         <parameterName value="@AppLogID"/>
         <dbType value="String"/>
         <size value="50" />
         <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%property{LoggingSessionId}" />
         </layout>
      </parameter>
      <parameter>
         <parameterName value="@CreateUser"/>
         <dbType value="String"/>
         <size value="50" />
         <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%property{HttpUser}" />
         </layout>
      </parameter>
      <parameter>
         <parameterName value="@Message"/>
         <dbType value="String"/>
         <size value="8000" />
         <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%message" />
         </layout>
      </parameter>
      <parameter>
         <parameterName value="@LogLevel"/>
         <dbType value="String"/>
         <size value="50"/>
         <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%level" />
         </layout>
      </parameter>
</appender>

Answer 1

Thanks to a vigilant DBA, we have solved the problem.

Note the size of the "@Message" parameter. log4net is taking a guess at how to convert the type and (I think) converting it to nvarchar even though the column is a varchar. This is a big deal because nvarchar has a max size of 4000 while varchar has a max size of 8000.

The DBA saw errors as described in this KB article: http://support.microsoft.com/kb/827366

I changed the size to 4000 and everything works swimingly.

Hopefully this will help somebody else avoid the same problem.

Cheers!

Answer 2

</configSections>
<log4net>

 <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">

  <bufferSize value="1"/>

  <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.5000.0,Culture=neutral, PublicKeyToken=b77a5c561934e089"/>

  <connectionString value="Data Source=yourservername;initial Catalog=Databasename;User ID=sa;Password=xyz;"/>



  <commandText value="INSERT INTO Log4Net ([Date], [Thread], [Level], [Logger], [Message], 
               [Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)"/>

  <parameter>

   <parameterName value="@log_date"/>

   <dbType value="DateTime"/>

   <layout type="log4net.Layout.RawTimeStampLayout"/>

  </parameter>

  <parameter>

   <parameterName value="@thread"/>

   <dbType value="String"/>

   <size value="255"/>

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

    <conversionPattern value="%thread ip=%property{ip}"/>

   </layout>

  </parameter>

  <parameter>

   <parameterName value="@log_level"/>

   <dbType value="String"/>

   <size value="50"/>

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

    <conversionPattern value="%level"/>

   </layout>

  </parameter>

  <parameter>

   <parameterName value="@logger"/>

   <dbType value="String"/>

   <size value="255"/>

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

    <conversionPattern value="%logger"/>

   </layout>

  </parameter>

  <parameter>

   <parameterName value="@message"/>

   <dbType value="String"/>

   <size value="4000"/>

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

    <conversionPattern value="%message"/>

   </layout>

Answer 3

Use "AnsiString" as dbType for varchar. "String" for nvarchar.

http://msdn.microsoft.com/en-us/library/system.data.dbtype%28v=VS.90%29.aspx