c# - Log4Net，如何获取存储过程的结果？

Question

我正在使用 C# 进行开发，我一直在寻找一种有效地记录日志文件和数据库的方法。我找到了 Log4Net。我的 appender 工作正常，我可以在我的数据库中写入。我想知道是否可以通过 Log4Net 获取存储过程的结果。

我解释一下:我有两个附加程序(“添加”和“更新”)，它们在 SQLserver 上调用两个不同的存储过程(“sp_AjouterEntree”和“sp_UpdateEntree”)。第一个过程向表中添加一行，第二个过程使用错误消息(如果有)和任务的“结束日期”更新此行。

表的主键是一个ID。此 ID 由第一个过程(“添加”)自动选择。我需要的是接收第一个程序的返回 @@identite，以了解我需要在第二个附加程序“更新”中更新哪一行。

我希望我的问题足够清楚。我在其他讨论中寻找答案，但显然以前没有人遇到过这个问题。

感谢您的帮助。

存储过程(添加行):

ALTER PROCEDURE [dbo].[sp_AjouteEntree]
@Flux varchar(50), @Etape varchar(200),@Debut datetime2(7),@Fin datetime2(7),@EtapeKO bit, @Erreur varchar(max) , @Annexe varchar(max)
AS
BEGIN



INSERT INTO [dbo].[Journal]
       ([Jour_Flux]
       ,[Jour_Etape_Flux]
       ,[Jour_Date_Heure_Debut]
       ,[Jour_Date_Heure_Fin]
       ,[Jour_Etape_KO]
       ,[Jour_Erreur]
       ,[Jour_Annexe])
 VALUES
       (@Flux
       ,@Etape
       ,@Debut
       ,@Fin
       ,@EtapeKO
       ,@Erreur
       ,@Annexe)



return @@IDENTITY
END

存储过程(更新行):

 ALTER PROCEDURE [dbo].[sp_UpdateEntree]
 @Id integer,@Fin datetime2(7),@EtapeKO bit, @Erreur varchar(max) ,@Annexe varchar(max)
 AS
 BEGIN


UPDATE [dbo].[Journal]
SET [Jour_Date_Heure_Fin] = @Fin
  ,[Jour_Etape_KO] = @EtapeKO
  ,[Jour_Erreur] = @Erreur
  ,[Jour_Annexe] = @Annexe
WHERE [Jour_ID]=@Id


END

第一个附加程序:

<appender name="Add" type="log4net.Appender.AdoNetAppender">
<bufferSize value="1" />
<threshold value="WARN" /> 
<connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> 
<connectionString value="data source=******************;Database=Test_Log4Net;Trusted_Connection=yes" />

<commandText value="sp_AjouteEntree" />
<commandType value="StoredProcedure"/>

<parameter>
  <parameterName value="@Flux" />
  <dbType value="AnsiString" />
  <size value="50" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%property{FluxName}" />
  </layout>
</parameter>

<parameter>
  <parameterName value="@Etape" />
  <dbType value="AnsiString" />
  <size value="500" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%method" />
  </layout>
</parameter>

<parameter>
  <parameterName value="@Debut" />
  <dbType value="DateTime" />
  <layout type="log4net.Layout.RawTimeStampLayout"/>
</parameter>

<parameter>
  <parameterName value="@Fin" />
  <dbType value="DateTime" />
  <layout type="log4net.Layout.RawTimeStampLayout"/>
</parameter>

<parameter>
  <parameterName value="@EtapeKO" />
  <dbType value="Byte" />
  <size value="1" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%property{ErreurKO}" />
  </layout>
</parameter>

<parameter>
  <parameterName value="@Erreur" />
  <dbType value="AnsiString" />
  <size value="2000" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="NULL" />
  </layout>
</parameter>

<parameter>
  <parameterName value="@Annexe" />
  <dbType value="AnsiString" />
  <size value="5000" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%message" />
  </layout>
</parameter>

第二个附加程序:

<appender name="Update" type="log4net.Appender.AdoNetAppender">
<bufferSize value="1" />

<threshold value="WARN" />

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

<connectionString value="data source=*****************;Database=Test_Log4Net;Trusted_Connection=yes" />

<commandText value="sp_UpdateEntree" />
<commandType value="StoredProcedure"/>

<parameter>
  <parameterName value="@Id" />
  <dbType value="Int32" />
  <size value="50" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%property{ID}" />
  </layout>
</parameter>

<parameter>
  <parameterName value="@Fin" />
  <dbType value="DateTime" />
  <layout type="log4net.Layout.RawTimeStampLayout"/>
</parameter>

<parameter>
  <parameterName value="@EtapeKO" />
  <dbType value="Byte" />
  <size value="1" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%property{ErreurKO}" />
  </layout>
</parameter>

<parameter>
  <parameterName value="@Erreur" />
  <dbType value="AnsiString" />
  <size value="2000" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%property{messageErreur}" />
  </layout>
</parameter>

<parameter>
  <parameterName value="@Annexe" />
  <dbType value="AnsiString" />
  <size value="5000" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%message" />
  </layout>
</parameter>

Answer 1

这是我的存储过程代码

USE [JainPort114]
GO
/****** Object:  StoredProcedure [dbo].[GetEventValveONOFF]    Script Date: 17-Apr-17 9:18:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetEventValveONOFF] 
    --@RTUID int, @StartDatetime datetime, @Enddatetime datetime,
    --@networkId int, @Zoneid int, @BlockId int
    @WhereCondition nvarchar(max) , @StartDatetime nvarchar(100), @Enddatetime nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    --If only 1 zone is selected

        begin
            DECLARE @SQL NVARCHAR(MAX)
            print @StartDatetime
            print @Enddatetime

            SELECT @SQL = 'select  be.eventdatetime,''Valve Event'' as eventtype, elest.status as ''status'' ,
                r.rtuname, c.channelname,c.channelid, c.tagname, c.tagname as description,
                (select d.Type from DigitalOutput d where  d.DOId = c.typeid and c.subtypeid = 0 and c.EqpTypeId =4) as SensorType,
                --(select Reason from JainPort114_events.dbo.ElementTypeReason where ReasonId = be.Reason and ElementTypeId = 4) as Reason,
                 --(select EleTypeReasonId from JainPort114_events.dbo.ElementTypeReason where ReasonId = be.Reason and ElementTypeId = 4) as ReasonId
                 etr.Reason, etr.ReasonId,
                 case when be.Reason = 8 then (Select be.Errorcode)
                  when be.Reason = 9 then (Select be.Errorcode)
                 else 0
                 end as moidruleid              
                from JainPort114_events.dbo.BSTEvents b, JainPort114_events.dbo.BSTEventsConfig be, rtu r, network n, channel c , JainPort114_events.dbo.ElementTypeReason etr,JainPort114_events.dbo.elementstatus elest
                where be.bsteventid = b.BSTEventsId
                and c.rtuid = r.rtuid
                and r.networkid = n.networkid
                and elest.EleStatusId = be.elestatus
                and be.EventDateTime between '''+ @StartDatetime + ''' and ''' + @Enddatetime + '''
                and b.NetworkId = (select networkno from network  where NetworkId = r.networkid)
                and b.NetworkId = n.networkno
                and JainPort114_events.dbo.HextoDEC(SUBSTRING (be.elenumberandtype, 1, 2), 16) = r.rtuidinnw 
                and JainPort114_events.dbo.HextoDEC(SUBSTRING (be.elenumberandtype, 3, 2), 16) = c.slotidinrtu
                and c.slotidinrtu = be.number
                and etr.ElementTypeId = 4
                and etr.ReasonId =  be.Reason
                    --and n.networkid = @networkId and r.rtuid = @RTUID
                --and  etr.ReasonId = 13
                and r.active =1'
                    +@WhereCondition
                    +'order by be.eventdatetime desc'
                    PRINT @SQL
            EXEC sys.sp_executesql @SQL

        End         
END