[EF][SQL]透過EF執行預存程序並改變傳入的參數值
c#:
using (KENODBEntities db = new KENODBEntities())
{
var outputParameter = new ObjectParameter("OutString", typeof(string));
db.s_Insert_GrayLog_NormalCsvRequest(eventCode, outputParameter);
result = Convert.ToString(outputParameter.Value);
}
sp tsql:
USE [KENODB]
GO
/****** Object: StoredProcedure [Betgenius].[s_Insert_GrayLog_NormalCsvRequest] Script Date: 2017/6/6 下午 01:48:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Betgenius].[s_Insert_GrayLog_NormalCsvRequest]
@EventCode bigint,
@OutString varchar(200) out
AS
BEGIN
--回傳結果
SET @OutString = '';
BEGIN TRY
--先檢查是否存在
if not exists(select * from Betgenius.GrayLog_NormalCsvRequest where EventCode = @EventCode )
begin
--不存在的話就直接insert
insert into Betgenius.GrayLog_NormalCsvRequest
(EventCode, Status, CreateDateTime)
values
(@EventCode, 'queue', GETDATE())
END
else
BEGIN
--已存在此賽事的話,將狀態更新為queue,便可已重新請求csv檔案
update Betgenius.GrayLog_NormalCsvRequest
SET EventFilePath = NULL, Status = 'queue', CreateDateTime = Getdate(), UpdatedDateTime = NULL
where EventCode = @EventCode
end
END TRY
BEGIN CATCH
SET @OutString = N'exec [Betgenius].[s_Insert_GrayLog_NormalCsvRequest]發生錯誤:' + CONVERT(VARCHAR(1000),ERROR_MESSAGE())
END CATCH
END
記錄下來以免以後忘記