How to read SQL Server COUNT from SqlDataReader

Question

I'm trying to find the count for a table using C# SqlDataReader but I keep getting

invalid attempt to read when no data is present

My code:

string sql = "SELECT COUNT(*) FROM [DB].[dbo].[myTable]";

SqlCommand cmd = new SqlComman(sql, connectionString);
SqlDataReader mySqlDataReader = cmd.ExecuteReader();

int count = mySqlDataReader.GetInt32(0); // Here is where I get the error.

I know I have a valid connection to the database because I can read and write to it in many places, what's special about the COUNT(*) that I cannot read it properly? How do I get the int count to be populated?

Answer 1

You have to read it:

if (mySqlDataReader.Read()) {
  count = mySqlDataReader.GetInt32(0);
}

Alternatively, you can just use ExecuteScalar:

int count = (int)cmd.ExecuteScalar();

which is defined as:

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.