I am trying to use EF Core (latest release) against a Local 2016 DB and I am getting -1
back every time. I don't know what I have done wrong?
I know that it is reaching the database. I checked that.
int returnCode = _dbContext.Database.ExecuteSqlCommand("CheckReceivedNotificationDuplicate @p0, @p1, @p2",
parameters: new[] { sendMessage.MESSAGE_TEMPLATE_NAME, sendMessage.MESSAGE_SUBJECT, sendMessage.MESSAGE_TEXT_SUMMARY });
Stored Procedure:
ALTER PROCEDURE [dbo].[CheckReceivedNotificationDuplicate]
@MESSAGE_TEMPLATE_NAME nvarchar(100),
@MESSAGE_SUBJECT nvarchar(255),
@MESSAGE_TEXT_SUMMARY nvarchar(4000)
AS DECLARE @NMID Int
BEGIN
IF EXISTS (SELECT 1 FROM dbo.Notification WHERE MESSAGE_TEMPLATE_NAME = @MESSAGE_TEMPLATE_NAME
and MESSAGE_SUBJECT = @MESSAGE_SUBJECT AND ReceiveTimeEST > dateadd(minute,-5, dbo.GetGMTtoEST(getutcdate())))
BEGIN
IF EXISTS (SELECT 1 FROM dbo.Notification WHERE MESSAGE_TEMPLATE_NAME = @MESSAGE_TEMPLATE_NAME
and MESSAGE_SUBJECT = @MESSAGE_SUBJECT AND MESSAGE_TEXT_SUMMARY = @MESSAGE_TEXT_SUMMARY
AND ReceiveTimeEST > dateadd(minute,-5, dbo.GetGMTtoEST(getutcdate())))
RETURN -99
ELSE
RETURN 0
END
ELSE
RETURN 0
END
ExecuteSqlCommand
calls IRelationalCommand.ExecuteNonQuery
internally. It returns the number of rows affected.
To return an arbitrary value from stored procedure, add output parameter to its declaration and to ExecuteSqlCommand
call:
var outParameter = new SqlParameter("@outParameter", DbType.Int32)
{
Direction = ParameterDirection.Output
};
context.Database.ExecuteSqlCommand("exec GetFoo @outParameter OUT", outParameter);