Its becuase ExecuteNonQuery returns No. rows affected. Now as you are using Select Query it does not affect any of the rows so it returns 0.
Secondly when you used ExecuteScaler It returns single selected value. In this case as you used Select count(*) So it would return selected amount. Thats why its has the difference.
NOTE:- As far as ADO.NET concerns nonQuery is not intended to use for select command. Select Commad is counted as Query and delete update and insert command counted as NonQuery.
Insert, Update and delete command affects rows that the why When you executeNonQuery it returns no. of affected rows. While using select Query doent affect rows it returns 0.
