Kamran Shahid Kamran Shahid - 5 months ago 98
MySQL Question

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

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

Answer

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