Null values for int

[Howard Weisberg <hl...@UNIVISION.NET>]

I'm writing some code using AdoNetAppender_SqlServer. Some of the info I
want to log is best expressed in SQL columns with the int datatype. Also
some of the information only exists during certain events.

Ideally, information that does not exist should be expressed as SQL null
values. However so far I've only been able to figure out how to log
using a special numerical value (in my case zero) to flag a non-existent
values. Null values do seem to be supported for string type data.

The following doesn't work for me. It causes the sql exception "data is
not in the correct format."

int spid = MyGetSPID (transaction);
log4net.GlobalContext.Properties["spid"] = spid;
EventLog.Info ("Database Update");
transaction.Commit();
log4net.GlobalContext.Properties["spid"] = null;
//log4net.GlobalContext.Properties.Remove("spid") also doesn't work.

Instead I have to use the following:

const int NONEXISTENT_VALUE = 0;
int spid = MyGetSPID (transaction);
log4net.GlobalContext.Properties["spid"] = spid;
EventLog.Info ("Database Update");
transaction.Commit();
log4net.GlobalContext.Properties["spid"] = NONEXISTENT_VALUE ;

My config file includes the following:

<commandText value="INSERT INTO EventLog ([Date], ..., [SPID]) 
VALUES (@date, ..., @spid)" />
<parameter>
	<parameterName value="@date" />
	<dbType value="DateTime" />
	<layout type="log4net.Layout.PatternLayout"
value="%date{yyyy'-'MM'-'dd HH':'mm':'ss'.'fff}" />
</parameter>

...

<parameter>
	<parameterName value="@spid" />
	<dbType value="Int32" />
	<layout type="log4net.Layout.PatternLayout"
value="%property{spid}" />
</parameter>

My table includes the following:

CREATE TABLE EventLog
(
  [ID] [int] IDENTITY (1, 1) NOT NULL ,
  [Date] [datetime] NOT NULL ,

...

  [SPID] [int]
) ON [PRIMARY]

How do I get log4net to handle null integer values? 

The information contained in this e-mail and any attached documents 
may be privileged, confidential and protected from disclosure.  If you 
are not the intended recipient you may not read, copy, distribute or 
use this information.  If you have received this communication in 
error, please notify the sender immediately by replying to this 
message and then delete it from your system.