Log4net, ADONetAppender和存储过程:设置精度&规模

有两个参数是AdoNetAppenderParameter类的一部分，您可以在xml文件中添加ExecutionTime参数，Precision和Scale:

<parameter name="ExecutionTime">
    <parameterName value="@ExecutionTime"/>
    <dbType value="Decimal"/>
    <precision value="14"/>
    <scale value="4"/>
    <layout type="log4net.Layout.PatternLayout" value="%property{execution_time}" />
</parameter>

• 打开调试并找到关于Precision和Scale的投诉
• 接下来是NULL值。它们被转换为文字(null)，并且在将其转换为小数时阻塞。
• Log4net转换。配置为STRING，并添加了一些转换逻辑到存储过程
• 转换的ExecutionTime Int，因为我使用的代码是毫秒。

    DROP TABLE [LOG];
    GO
    CREATE TABLE [dbo].[LOG] (
         [Id] [int] IDENTITY(1,1) NOT NULL
        ,[LogDate] [datetime] NOT NULL
        ,[Thread] [varchar](32) NOT NULL
        ,[Level] [varchar](10) NOT NULL
        ,[Logger] [varchar](255) NOT NULL
        ,[Message] [varchar](4000) NOT NULL
        ,[Exception] [varchar](4000) NULL
        ,[Context] [varchar](10) NULL
        ,[MethodName] [varchar](200) NULL
        ,[Parameters] [varchar](4000) NULL
        ,[ExecutionTime] int NULL
        --,[Origin] [varchar](55) NOT NULL
        --,[ExecutionRaw] [varchar](4000) NULL
    )
    GO
    DROP PROCEDURE InsertLog
    GO
    CREATE PROCEDURE [dbo].[InsertLog]
         @LogDate DateTime
        ,@Thread varchar(32)
        ,@Level varchar(10)
        ,@Logger varchar(255)
        ,@Message varchar(4000)
        ,@Exception varchar(4000) = null
        ,@Context varchar(10) = null
        ,@MethodName varchar(200) = null
        ,@Parameters varchar(4000) = null
        ,@ExecutionTime varchar(32) = null
    AS
    BEGIN
        SET NOCOUNT ON;
        --DECLARE @ETChar varchar(32); set @ETChar = @ExecutionTime;
        if @Exception     = '' set @Exception = null;
        if @Context       = '(null)' set @Context = null;
        if @MethodName    = '(null)' set @MethodName = null;
        if @Parameters    = '(null)' set @Parameters = null;        
        if @ExecutionTime = '(null)' set @ExecutionTime = null;
        DECLARE @ETInt int;          set @ETInt  = convert(int, @ExecutionTime);
        INSERT INTO [dbo].[LOG] (
             [LogDate]
            ,[Thread]
            ,[Level]
            ,[Logger]
            ,[Message]
            ,[Exception]
            ,[Context]
            ,[MethodName]
            ,[Parameters]
            ,[ExecutionTime]
            --,[Origin]
            --,[ExecutionRaw]
            )
        VALUES (
             @LogDate
            ,@Thread
            ,@Level
            ,@Logger
            ,@Message
            ,@Exception
            ,@Context
            ,@MethodName
            ,@Parameters
            ,@ETInt
            --,'InsertLog'
            --,@ETChar
            )
        SET NOCOUNT OFF;
    END
    GO
    DROP INDEX [IX_LEVEL] ON [LOG]
    GO
    CREATE NONCLUSTERED INDEX [IX_Level] ON [dbo].[LOG] ( [Level] ASC ); 
    GO

Log4Net.config:

<?xml version="1.0" encoding="utf-8"?>
<log4net>
  <root>
    <level value="DEBUG" />
    <appender-ref ref="ADONetAppender" />
  </root>
  <appender name="ADONetAppender" type="log4net.Appender.ADONetAppender">
    <threshold value="ALL" />
    <bufferSize value="1" />
    <lossy value="false" />
    <param name="UseTransactions" value="False" />
    <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <connectionString value="..." />
    <commandType value="StoredProcedure" />
    <commandText value="InsertLog" />
    <parameter name="LogDate">
      <parameterName value="@LogDate" />
      <dbType value="DateTime" />
      <layout type="log4net.Layout.RawTimeStampLayout" />
    </parameter>
    <parameter name="Thread">
      <parameterName value="@Thread" />
      <dbType value="String" />
      <size value="32" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%thread" />
      </layout>
    </parameter>
    <parameter name="Level">
      <parameterName value="@Level" />
      <dbType value="String" />
      <size value="10" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%level" />
      </layout>
    </parameter>
    <parameter name="Logger">
      <parameterName value="@Logger" />
      <dbType value="String" />
      <size value="255" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%logger" />
      </layout>
    </parameter>
    <parameter name="Message">
      <parameterName value="@Message" />
      <dbType value="String" />
      <size value="4000" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%message" />
      </layout>
    </parameter>
    <parameter name="Exception">
      <parameterName value="@Exception" />
      <dbType value="String" />
      <size value="4000" />
      <layout type="log4net.Layout.ExceptionLayout" />
    </parameter>
    <parameter name="Context">
      <parameterName value="@Context" />
      <dbType value="String" />
      <size value="10" />
      <layout type="log4net.Layout.PatternLayout" value="%x" />
    </parameter>
    <parameter name="MethodName">
      <parameterName value="@MethodName" />
      <dbType value="String" />
      <size value="200" />
      <layout type="log4net.Layout.PatternLayout" value="%property{method_name}" />
    </parameter>
    <parameter name="Parameters">
      <parameterName value="@Parameters" />
      <dbType value="String" />
      <size value="4000" />
      <layout type="log4net.Layout.PatternLayout" value="%property{properties}" />
    </parameter>
    <parameter name="ExecutionTime">
      <parameterName value="@ExecutionTime" />
      <dbType value="String" />
      <size value="32" />
      <layout type="log4net.Layout.PatternLayout" value="%property{execution_time}" />
    </parameter>
  </appender>
</log4net>