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:

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

Any help will be appreciated.

score 2 · Accepted Answer · answered Mar 06 '20 at 09:26

2

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.

answered Mar 06 '20 at 09:26

Alex

7,901
1
41
56

Thanks..It worked. just a follow up question, when a query return a model instead value. – Krish Mar 06 '20 at 09:35
See https://stackoverflow.com/questions/3815502/stored-procedure-when-to-use-output-parameter-vs-return-variable/42125448 it should answer your question on when to use a return value vs. output parameters – Alex Mar 06 '20 at 09:37

score 1 · Answer 2 · answered Mar 06 '20 at 09:28

1

You just modify SELECT @UserID => RETURN @UserID. Then it will work as expected. Hope to help, my friend :))

answered Mar 06 '20 at 09:28

Tomato32

2,145
1
10
10

Thanks. It worked for Query method not when using Execute method. – Krish Mar 06 '20 at 09:40

Fail to retrieve the User Id from Database using Dapper

2 Answers2