How to properly capture return value from call to stored procedure

Question

First of all I would like to thank anyone who reads this and answers. Your help is greatly appreciated!

I have been developing an ASP.NET web application in which I have need to read from and write to a database. For this I have been attempting to call, from my C# code, some stored procedures I have written. Specifically this one:

public static bool userExistsInDB(string username)
{
    int userExistsInDB = -1;
    using (SqlConnection con = new SqlConnection(DBConfig.DbConnectString))
    {
        using (SqlCommand cmd = new SqlCommand("tb_user_exists", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
            con.Open();
            userExistsInDB = cmd.ExecuteNonQuery();
        }
    }
    return userExistsInDB == 1;
}

The stored procedure I call was created with this SQL script:

CREATE PROCEDURE tb_is_logged_in
    @username VARCHAR(20)
AS
IF(EXISTS(SELECT * FROM logged_in_users WHERE "username" = @username))
BEGIN
    RETURN 1; 
END
ELSE
BEGIN
    RETURN 0; 
END

I tested it on its own in SQL Server Management Studio, and it seems to work. However, upon stepping through my C# code I find a problem with the line

userExistsInDB = cmd.ExecuteNonQuery();

The stored procedure is supposed to return 1 or 0 if the specified user exists in the database or not, respectively, and as you can see userExistsInDB is initialized with -1. However after the line userExistsInDB = cmd.ExecuteNonQuery(); is executed userExistsInDB is never modified; it always retains the value -1. So it seems I am getting the return value incorrectly. What am I doing wrong?

EDIT:

Thanks to Leonel, I found a solution to the problem. First, I realized that the stored procedure I pasted above was not the correct procedure. It is not the one I am actually calling in my C# code, and when I tried Leonel's fix, it didn't work because I accidentally edited the procedure I pasted above instead of the one I actually call. Here is the actual stored procedure I was calling:

CREATE PROCEDURE tb_user_exists
    @username VARCHAR(20)
AS
IF(EXISTS (SELECT * FROM users WHERE username = @username))
BEGIN
    RETURN 1;  
END
ELSE
BEGIN
    RETURN 0; 
END

My solution is as follows: change this line

userExistsInDB = cmd.ExecuteNonQuery();

to

userExistsInDB = (int)cmd.ExecuteScalar();

and change the stored procedure to:

CREATE PROCEDURE tb_user_exists
    @username VARCHAR(20)
AS
IF(EXISTS (SELECT * FROM users WHERE username = @username))
BEGIN
    SELECT 1;  
END
ELSE
BEGIN
    SELECT 0; 
END

Answer 1

Use SqlCommand.ExecuteScalar(); method from MSDN

For example:

userExistsInDB = (int)cmd.ExecuteScalar();

The example code will execute the SqlCommand and retrieve the first row of the table if exist and set the value of the variable into number of items in table. In this case you can remove the return statement and use the

SELECT * FROM logged_in_users WHERE "username" = @username

And also always close your connection after using it

con.Open();
userExistsInDB = cmd.ExecuteScalar();
con.Close();

Answer 2

If you just getting one value, executeScalar() would be good enough. If you still have problem you can ask

String get;
Get=cmd.executescalar().tostring();