I have the following stored procedure that I'm trying to call from my asp.net core MVC web application:
CREATE PROCEDURE [dbo].[uspLogin]
(
@pUserName VARCHAR(150),
@pPassword VARCHAR(150),
@responseMessage NVARCHAR(250)='' OUTPUT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @userID INT
IF EXISTS (SELECT TOP 1 ID FROM dbo.[User] WHERE UserName = @pUserName)
BEGIN
SET @userID = (SELECT ID FROM dbo.[User] WHERE UserName = @pUserName AND PasswordHash=HASHBYTES('SHA2_512', @pPassword+CAST(Salt AS NVARCHAR(36))))
IF(@userID IS NULL)
SET @responseMessage='Incorrect password'
ELSE
SET @responseMessage='Success'
END
ELSE
SET @responseMessage = 'Invalid Login'
END
I can call this procedure perfectly fine from within my DB, however I'm trying to call it with this code:
[HttpPost]
public IActionResult Index(User user)
{
var userName = user.UserName;
var password = user.Password;
var message = new SqlParameter
{
ParameterName = "responseMessage",
SqlDbType = SqlDbType.NVarChar,
Size = 250,
Direction = ParameterDirection.InputOutput,
};
_context.Database.ExecuteSqlCommand("dbo.uspLogin @p0, @p1, @responseMessage", userName, password, message);
return RedirectToAction("Success", "Home", new { email = message.SqlValue });
}
When testing out this action, I get the following error:
SqlException: The parameterized query '(@p0 nvarchar(4000),@p1 nvarchar(4000),@responseMessage nvarchar' expects the parameter '@responseMessage', which was not supplied.
What is the right way to get the output
of this procedure from EF Core?
Change param direction,
var message = new SqlParameter
{
ParameterName = "responseMessage",
SqlDbType = SqlDbType.NVarChar,
Size = 250,
Direction = ParameterDirection.Output,
};
And execution line as below and try,
_context.Database.ExecuteSqlCommand("dbo.uspLogin @p0, @p1, @responseMessage OUT", userName, password, message);