Log4Net,如何获取存储过程的结果
本文关键字:存储过程 结果 获取 何获取 Log4Net | 更新日期: 2023-09-27 17:58:16
我正在C#上进行开发,我一直在寻找一种在日志文件和数据库上高效登录的方法。我找到了Log4Net。我的附加程序运行良好,我可以在数据库中进行编写。我想知道是否可以通过Log4Net获得存储过程的结果。
我解释道:我有两个appender("Add"answers"Update"),它们在SQLserver上调用两个不同的存储过程("sp_AjouterEntree"answers"sp_UpdateEntree")。第一个过程向表中添加一行,第二个过程用错误消息(如果有)和任务的"EndDate"更新此行。
表的主键是ID。该ID由第一个过程("添加")自动选择。我需要的是接收第一个过程的返回@@identite,以知道之后我需要在第二个追加器"update"中更新哪一行。
我希望我的问题足够清楚。我已经在其他讨论中找到了答案,但显然以前没有人遇到过这个问题。
谢谢你的帮助。
存储过程(添加行):
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>
这是我的存储过程代码
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