Fail to retrieve the User Id from Database using Dapper

Question

public int UserAuthentication(UsersViewModel users)
{
        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("@UserName", users.UserName);
        parameters.Add("@Password", users.Password);
        parameters.Add("@UserID", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

        connection();
        sqlConn.Open();

        var result =  sqlConn.Execute("usp_UserLoginValidation", parameters, commandType: CommandType.StoredProcedure);            
        // int userid = SqlMapper.Query<UsersViewModel>(sqlConn, "usp_UserLoginValidation", parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();
        int userid = parameters.Get<int>("@UserID");

        sqlConn.Close();            

        return userid;
}

Stored procedure:

ALTER PROCEDURE usp_UserLoginValidation
    (@UserName varchar(50),
     @Password varchar(20),
     @UserID int = 100 OUT)
AS
BEGIN
    SET @UserID = (SELECT UserId FROM Users 
                   WHERE UserName = @UserName AND Password = @Password AND IsActive = 1)
    SELECT @UserID
END

I'm trying to get the UserId from the database table, when the user logs into the application. Executing the stored procedure in the database returns the UserId but it fails to fetch that info into a class using Dapper.

I tried the following approaches:

1. Using Execute method, userid value is 0.
2. Using Query method, expect value but it returns the model (commented code)

Any help will be appreciated.

Answer 1

Your stored procedure uses an OUT parameter but your @UserID is declared as ParameterDirection.ReturnValue.

If you use ParameterDirection.Output it should work as expected.