CODEWITHSUNDEEP
c#sqlsql-server
fifamaniac04
fifamaniac04

Reputation: 2383

How to read SQL Server COUNT from SqlDataReader

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?

Upvotes: 14

Views: 19419

Answers (2)

Harshan01
Harshan01

Reputation: 67

ExecuteScalar is what you require.

Upvotes: 0

LarsTech
LarsTech

Reputation: 81610

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.

Upvotes: 26

Related Questions