Count Always return -1 SQL Server. ASP.NET C#

Question

Query always return -1 don't know why. Will someone please explain. Value of count always remains -1.

string query = "SELECT COUNT(*) AS Emails FROM users";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(query, connection);
    command.Parameters.AddWithValue("@email", email);

    try
    {
       connection.Open();
       count = command.ExecuteNonQuery();

       if (count > 0)
          return "Something Wrong1";
    }
    catch
    {
       return "Something Wrong2";
    }

    return count + "Every thing ok";
 }

Answer 1

I'm no C# expert, but that command.ExecuteNonQuery() doesn't seem right ... it's a query after all!

Answer 2

That is because ExecuteNonQuery does not return the result of the query, it just executes it on the SQL server. The return value is the number of rows affected by your statement, -1 when the statement does not affect any rows. ExecuteNonQuery (as the name implies) is not intended for returning query results, but rather for running a statement that changes data (such as INSERT, DELETE, UPDATE). The docs state:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. (...) For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

You could use:

count = (int)command.ExecuteScalar();

To get the count you are looking for. There is also an example in the docs for ExecuteScalar.

Answer 3

I think that perhaps what you mean is for your SQL statement to be:

SELECT COUNT(*) FROM users WHERE Email = @email

Besides that, you must use the ExecuteScalar method to retrieve the count.

Answer 4

You need ExecuteScalar not ExecuteNonQuery to retrieve the count value.