Connection.Execute doesn't return valid integer

Question

I'm trying to return number to my variable so I can figure if there is a record in my sql database with same nick name as user inserted, but connection.Execute keeps returning me -1. I am doing Windows Forms App.

This is my stored procedure:

ALTER PROCEDURE [dbo].[spPerson_GetPerson]
@NickName nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    select COUNT(*) from dbo.Person where NickName = @NickName
END

This is my method:

public static bool IsUserUnique(PersonModel model)
    {
        using (IDbConnection connection = new SqlConnection(CnnString("MathemaKids")))
        {
            bool recordAlreadyExists = false;
            var p = new DynamicParameters();
            p.Add("@NickName", model.NickName);

            int numberOfUsersWithSelectedPassword = connection.Execute("dbo.spPerson_GetPerson",p, commandType: CommandType.StoredProcedure);

            if (numberOfUsersWithSelectedPassword > 0)
            {
                return true;
            }
            return recordAlreadyExists;
        }
    }

Anyone know why?

Answer 1

You are returning only one value from the stored procedure.

If you use execute method then you need to set a dynamic parameter with direction as returnvalue and get using the below code.

p.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
connection.Execute("dbo.spPerson_GetPerson", p, commandType: CommandType.StoredProcedure);
int numberOfUsersWithSelectedPassword = p.Get<int>("@Result");

Please read the blog for detailed information https://csharp.hotexamples.com/examples/-/IDbConnection/Execute/php-idbconnection-execute-method-examples.html

Another solution

Use ExecuteScalar instead of Execute.

SqlCommand cmd = new SqlCommand("dbo.spPerson_GetPerson", con);
cmd.Parameters.AddWithValue("@NickName", model.NickName);
int numberOfUsersWithSelectedPassword = Convert.ToInt32(cmd.ExecuteScalar());