Rows number of SqlDataReader

Question

I'm trying to get rows count of SqlDataReader with SQL COUNT(). Because of some technical reasons I can't use a counter (increasing a variable while going forwarder). It's my SQL Statement:

(SELECT * FROM [Menus] WHERE MenuParent=@ID);
(SELECT COUNT(*) AS rowsCount FROM [Menus] WHERE MenuParent=@ID)

And it's my .net code:
(It's simple, C#ers can understand it also)

While DR.Read
    If Not DR("rowsCount") Is Nothing Then
    temp &= DR("rowsCount")
    End If
    'blah blah
End While

It does not recognize rowsCount.

What's my wrong?

Whole function: (I used my own classes to connect to the DB) http://pastebin.com/YBZCvvBH

You need to show us the code before this code. The code where you connect and execute the query and return the results into DR. — RBarryYoung
– RBarryYoung, Commented Jan 30, 2013 at 19:12
You can use a SqlDataReader for both. Use NextResult to advance to the next result. — Tim Schmelter
– Tim Schmelter, Commented Jan 30, 2013 at 19:16
@Blam because it's a recursive function and before making decision I have to know row counts. — Maysam
– Maysam, Commented Jan 30, 2013 at 19:30

Frederic · Accepted Answer · 2013-01-30 19:25:03Z

4

well if you realy want the recordcount inside the resultset

can do this, that seems like your query

    select V.rowsCount,* from [Menus]
    cross join (select COUNT(*) as rowsCount from [Menus] WHERE MenuParent=@ID)V
            WHERE MenuParent=@ID

or just do

    select COUNT(*) over () as rowsCount
    ,* from [Menus]
    WHERE MenuParent=@ID

answered Jan 30, 2013 at 19:25

Frederic

1,0286 silver badges11 bronze badges

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

1 Comment

Joel Coehoorn Over a year ago

There's also the (old now) COMPUTE keyword, or @@rowcount

Jeff S · Accepted Answer · 2013-01-30 19:12:18Z

4

Your SQL statement is returning two result sets. Assuming the order of the SQL statements in your question is the same as the order you are executing, you need to process the data returned by the first query (processing the various menu options) and then execute:

DR.NextResult()

before trying to read the count.

answered Jan 30, 2013 at 19:12

Jeff S

7,49432 silver badges32 bronze badges

Comments

Stu · Accepted Answer · 2013-01-30 19:12:04Z

0

Why &=?
You're selecting two recordsets. If you want the count, do that first, and separately.

answered Jan 30, 2013 at 19:12

Stu

15.8k4 gold badges46 silver badges74 bronze badges

1 Comment

paparazzo Over a year ago

Why be chattier than necessary?

Collectives™ on Stack Overflow

Rows number of SqlDataReader

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related