Dapper returns negative result

Question

I'm kinda confused by dapper returning -1 instead of 1 on sp call.

Here is the stored procedure:

CREATE PROCEDURE [dbo].[spIncrementUserAccessFailedCount]
@UserId INT,
@ConcurrencyStamp UNIQUEIDENTIFIER,

@IncrementCount INT OUTPUT,
@ConcurrencyStampNew UNIQUEIDENTIFIER OUTPUT
AS
  BEGIN
    SET NOCOUNT ON;

    BEGIN TRY

    IF NOT EXISTS(SELECT * FROM [dbo].[User] WHERE [UserId] = @UserId AND [ConcurrencyStamp] = @ConcurrencyStamp)
        RAISERROR('ConcurrencyStamp is not valid.',18,1) WITH NOWAIT;

    UPDATE [dbo].[User]
    SET 
        @IncrementCount = [AccessFailedCount] = [AccessFailedCount] + 1, 
        @ConcurrencyStampNew = [ConcurrencyStamp] = NEWID() 
    WHERE [UserId] = @UserId
    RETURN 1;

END TRY

BEGIN CATCH

    THROW

END CATCH
END

And here is the c# code where I call the sp:

    public async Task IncrementAccessFailedCountAsync(User user)
    {
        DynamicParameters parms = new DynamicParameters();
        parms.Add("UserId", user.UserId);
        parms.Add("ConcurrencyStamp", user.ConcurrencyStamp);

        parms.Add("IncrementCount", null, DbType.Int32, direction: ParameterDirection.Output);
        parms.Add("ConcurrencyStampNew", null, DbType.Guid, direction: ParameterDirection.Output, size: 40);

        var result = await _dataAcces.SaveData("dbo.spIncrementUserAccessFailedCount", parms, _connectionString.SqlIdentityConnectionName);

        if (result == 1)
        {
            user.ConcurrencyStamp = parms.Get<Guid>("ConcurrencyStampNew");
            user.AccessFailedCount = parms.Get<int>("IncrementCount");
        }
    }

Heres my wrapper around the dapper:

    public async Task<int> SaveData<U>(string sql, U parameters, string connectionStringName)
    {
        string connectionString = _configuration.GetConnectionString(connectionStringName);

        using (IDbConnection connection = new SqlConnection(connectionString))
        {
            return await connection.ExecuteAsync(sql, parameters, commandType: CommandType.StoredProcedure);
        }
    }

But the result value is -1.

FailedCount and ConcurencyStam are changed in the database but not returned becouse of the if block. I know that dapper returns number of effected rows and with "SET NOCOUNT ON", I thought its gonna return the "RETURN 1" value instead. Can I force it to do so?

Answer 1

The issue is that Dapper is trying to get the number or rows changed, when you call a PROCEDURE the number of rows changed by the command is -1. You want to get the return code from the stored procedure you can use something like this

In your case it would be something like;

    public async Task<int> SaveData<U>(string sql, U parameters, string connectionStringName)
    {
        string connectionString = _configuration.GetConnectionString(connectionStringName);
        using (IDbConnection connection = new SqlConnection(connectionString))
        {
            parameters.Add("RetVal", null, DbType.Int32, direction: ParameterDirection.ReturnValue);
            var returnCode = connection.Execute(sql, parameters, commandType: CommandType.StoredProcedure);
            return parameters.Get<int>("@RetVal");
        }
    }