log4net AdoNetAppender inserting "Null" string instead of null in database

Question

I have a log4net logging on my .net 3.5 application. Logging is inserting into database. One issue I am having is that for the database which i doesn't set data it inserts "NULL" instead of database null. My config is

 <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
    <immediateFlush value="true" />
    <bufferSize value="0" />
    <connectionType value="MySql.Data.MySqlClient.MySqlConnection, MySql.Data" />
    <connectionString value="data source=localhost;initial catalog=logging_db;User ID=root;Password=" />
    <commandText value="INSERT INTO system_log(appname, action,context_id)
                 VALUES (@appname, @action, @context_id);" />
    <parameter>
      <parameterName value="appname" />
      <dbType value="String" />
      <size value="10" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="My Web Service" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="action" />
      <dbType value="String" />
      <size value="45" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%5c{1}.%M" />
      </layout>
    </parameter>
  <parameter>
      <parameterName value="context_id" />
      <dbType value="String" />
      <size value="48" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%X{context_id}" />
      </layout>
    </parameter>
    <filter type="log4net.Filter.LevelRangeFilter">
      <acceptOnMatch value="true" />
      <levelMin value="DEBUG" />
      <levelMax value="FATAL" />
    </filter>
  </appender>

In some methods I am setting context_id in my method as

log4net.LogicalThreadContext.Properties["context_id"] = "My context";

It is inserting fine where i have provided it but incorrectly where i haven't set it. I have also checked the issue at https://issues.apache.org/jira/browse/LOG4NET-28 but there it is marked as resolved

score 4 · Accepted Answer · edited May 23 '17 at 10:29

4

You could alter the INSERT INTO statement to check for "NULL" strings and replace them with DB nulls, the idea is from here

So it would look like this:

INSERT INTO system_log(appname, action,context_id) VALUES (@appname, @action, CASE WHEN @context_id = 'NULL' THEN NULL ELSE @context_id END );"

UPDATE

To use a stored procedure instead, simply replace your statement like this:

exec YourStoredProcedure @appname, @action, @context_id

edited May 23 '17 at 10:29

Community

1
1

answered Apr 08 '15 at 11:19

Peter Szekeli

2,712
3
30
44

That's the last thing i want if no other solution found. Any idea if i can configure stored procedure instead of insert statement? – Kamran Shahid Apr 08 '15 at 11:39
@KamranShahid you are welcome - I updated the answer to call a stored procedure – Peter Szekeli Apr 08 '15 at 12:16

score 1 · Answer 2 · answered Jun 23 '16 at 09:09

1

For this situation I prefer to use a trigger. Either ON INSERT or INSTEAD OF INSERT (see https://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx)

I would do somthing like:

CREATE TRIGGER dbo.log_onInsert 
   ON  system_log
   INSTEAD OF INSERT
 AS 
BEGIN
SET NOCOUNT ON;

INSERT INTO system_log(appname, action,context_id)
select 
    appname, action,
    case when inserted.context_id='(null)' then null else inserted.context_id end
from inserted

END
GO

answered Jun 23 '16 at 09:09

David aOll

21
1

1

The answer by Szeki was one solution, mine is another. Programming seldom or never has one solution to a issue. It does not motivate a downwote. – David aOll Oct 06 '16 at 13:52
I agreed about your thoughts. But by my 12+ years of experience, Using trigger wouldn't be recommended for any-case. Your solution would be a big performance bottleneck – Kamran Shahid Oct 08 '16 at 10:22

score 0 · Answer 3 · answered Mar 22 '21 at 23:47

Try following:

<layout type="log4net.Layout.RawPropertyLayout">
<key value="yourParameterName" />
</layout>

instead of:

<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="yourParameterName" />
</layout>

At least in my case it helped and log4net inserted NULL value into the column

log4net AdoNetAppender inserting "Null" string instead of null in database

3 Answers3