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?

give an alias to count(*).

Dan Bracuk
– Dan Bracuk

2013-10-23 16:17:53 +00:00
Commented Oct 23, 2013 at 16:17 — Dan Bracuk
– Dan Bracuk, Commented Oct 23, 2013 at 16:17

LarsTech · Accepted Answer · 2013-10-23 16:13:43Z

26

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.

answered Oct 23, 2013 at 16:13

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harshan01 · Accepted Answer · 2015-01-11 08:21:56Z

0

ExecuteScalar is what you require.

answered Jan 11, 2015 at 8:21

Harshan01

671 silver badge10 bronze badges

Collectives™ on Stack Overflow

How to read SQL Server COUNT from SqlDataReader

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related