Data Reader row count

[packman]

Other than using a counter within a loop, is there a method/function that will determine how many records were returned from a query that uses the DataReader. Somthing similar to the record count property for Active X ADO?

[jkw119]

are you using a dataset? because if you are, you can do the following...


EndofFile = somedataset.tables(0).rows.count()

[bontyboy]

No, it looks like he is using a data reader instead of a data set.

[Lethal]

Nope. When you execute a command which returns a DataReader, the database is using a mechanism known as a firehose cursor. You read one record at a time and once a record is read, that's it, no turning back. If you use stored procedures, you could return an output paramater that holds the record count.

[Edneeis]

Yes its as Lethal said, the reader doesn't read ahead until the Read method is called so it doesn't know how many records there are until it reaches the end. You can append a count field into the query to find out.

If your original query was this:
"SELECT * FROM MyTable WHERE MyField=1"

Then change it to this:
"SELECT *,Count(*) AS RowCount FROM MyTable WHERE MyField=1"

Now each datarow will have a RowCount field that has the total number of records that matched the query that you can check.

[indydavid32]

Edneeis, how would you do it if you were hitting a stored proecedure?

Here is my select statement in my stored procedure.


Select Location, [Date], rootitem, ProductCode, SaleItems, SaleTotal From Item_Sales
Where [Date] >= @BeginDate And [Date] <= @EndDate And (ProductCode = '12' Or ProductCode = '13' Or ProductCode = '15' Or ProductCode = '17' Or ProductCode = '18' Or ProductCode = '19' Or ProductCode = '20' Or ProductCode = '61' Or ProductCode = '62' Or ProductCode = '63' Or ProductCode = '85' Or ProductCode = '86' Or ProductCode = '87' Or ProductCode = '88' Or ProductCode = '89' Or ProductCode = '90')

[Edneeis]

Select Location, [Date], rootitem, ProductCode, SaleItems, SaleTotal From Item_Sales, Count(*) AS RowCount
Where [Date] >= @BeginDate And [Date] <= @EndDate And (ProductCode = '12' Or ProductCode = '13' Or ProductCode = '15' Or ProductCode = '17' Or ProductCode = '18' Or ProductCode = '19' Or ProductCode = '20' Or ProductCode = '61' Or ProductCode = '62' Or ProductCode = '63' Or ProductCode = '85' Or ProductCode = '86' Or ProductCode = '87' Or ProductCode = '88' Or ProductCode = '89' Or ProductCode = '90')

[Memnoch1207]

Just an fyi
If you try to read the count of the reader like this

count = reader["count"]


it won't work

the reader returns an object and you need to convert it to an int to get the value of the count

count = Convert.ToInt32(reader["count"])

[indydavid32]

Edneeis, I tried that in my Store Procedure and I got the following error message.

Error 156: Incorrect syntax near the keyword 'RowCount'

I tried single and double quotes around RowCount and that didn't work either.

[Memnoch1207]

post the code for your stored procedure.

[indydavid32]

I did that already. Here it is again though.


Select Location, [Date], rootitem, ProductCode, SaleItems, SaleTotal From Item_Sales, Count(*) AS RowCount
Where [Date] >= @BeginDate And [Date] <= @EndDate And (ProductCode = '12' Or ProductCode = '13' Or ProductCode = '15' Or ProductCode = '17' Or ProductCode = '18' Or ProductCode = '19' Or ProductCode = '20' Or ProductCode = '61' Or ProductCode = '62' Or ProductCode = '63' Or ProductCode = '85' Or ProductCode = '86' Or ProductCode = '87' Or ProductCode = '88' Or ProductCode = '89' Or ProductCode = '90')

[Memnoch1207]

You had the RowCount after the From statement...change it to this

Select Location, [Date], rootitem, ProductCode, SaleItems, SaleTotal, Count(*) AS RowCount From Item_Sales
Where [Date] >= @BeginDate And [Date] <= @EndDate And (ProductCode = '12' Or ProductCode = '13' Or ProductCode = '15' Or ProductCode = '17' Or ProductCode = '18' Or ProductCode = '19' Or ProductCode = '20' Or ProductCode = '61' Or ProductCode = '62' Or ProductCode = '63' Or ProductCode = '85' Or ProductCode = '86' Or ProductCode = '87' Or ProductCode = '88' Or ProductCode = '89' Or ProductCode = '90')

[Edneeis]

That was my fault I cut and pasted quickly and stuck the RowCount in the wrong spot. I though the second line started with the FROM but it starts with the WHERE. Sorry about that.

[indydavid32]

This still doesn't work..


CREATE PROC dbo.CigUnits
@BeginDate Datetime, @EndDate datetime

as
If @BeginDate is null or @EndDate is null
Begin
Raiserror('Null values are not allowed',14,1)
Return
End
Select Location, [Date], rootitem, ProductCode, SaleItems, SaleTotal, Count(*) as RowCount From Item_Sales
Where [Date] >= @BeginDate And [Date] <= @EndDate And (ProductCode = '12' Or ProductCode = '13' Or ProductCode = '15' Or ProductCode = '17' Or ProductCode = '18' Or ProductCode = '19' Or ProductCode = '20' Or ProductCode = '61' Or ProductCode = '62' Or ProductCode = '63' Or ProductCode = '85' Or ProductCode = '86' Or ProductCode = '87' Or ProductCode = '88' Or ProductCode = '89' Or ProductCode = '90')
GO


I get error message.
Err 156: Incorrect syntax near the keyword 'RowCount'

I also tried to use Exp1 and I tried putint single quotes around RowCount

[Memnoch1207]

if you're using Sql Server you need to put tick (') marks around the RowCount variable.

Select Location, [Date], rootitem, ProductCode, SaleItems, SaleTotal, Count(*) AS 'RowCount' From Item_Sales

[indydavid32]

Dude, I just said I put single quotes around RowCount which causes the error attached to this message.

[Edneeis]

Yeah I guess I was wrong because I just tried it and got an error. I could have swore i've used it before though. Maybe it only works in GroupBy queries. Anyway since you are running a stored procedure you can still get it, give me a sec.

[Memnoch1207]

Because you're using an aggregate function..you need to use group by in you sql statement...so try using something like

GROUP BY Location

[Edneeis]

Yes it should work if it is grouped or you can just run a seperate count query in the stored procedure here is an example:

ALTER PROCEDURE getSamplewCount
@ID as int
AS
DECLARE @Num As Int
SELECT @Num=(SELECT Count(*) FROM Sample WHERE Sample.ID=@ID)


SELECT *, @Num As 'RowCount'
FROM Sample
WHERE (Sample.ID=@ID)

RETURN

[indydavid32]

Where would the Group By code go?


CREATE PROC dbo.CigUnits
@BeginDate Datetime, @EndDate datetime

as
If @BeginDate is null or @EndDate is null
Begin
Raiserror('Null values are not allowed',14,1)
Return
End
Select Location, [Date], rootitem, ProductCode, SaleItems, SaleTotal, Count(*) As RowCount From Item_Sales
Where [Date] >= @BeginDate And [Date] <= @EndDate And (ProductCode = '12' Or ProductCode = '13' Or ProductCode = '15' Or ProductCode = '17' Or ProductCode = '18' Or ProductCode = '19' Or ProductCode = '20' Or ProductCode = '61' Or ProductCode = '62' Or ProductCode = '63' Or ProductCode = '85' Or ProductCode = '86' Or ProductCode = '87' Or ProductCode = '88' Or ProductCode = '89' Or ProductCode = '90')
GO

[Memnoch1207]

at the very end of you procedure

Select Location, [Date], rootitem, ProductCode, SaleItems, SaleTotal, Count(*) As RowCount
From Item_Sales
Where [Date] >= @BeginDate And [Date] <= @EndDate And (ProductCode = '12' Or ProductCode = '13' Or ProductCode = '15' Or ProductCode = '17' Or ProductCode = '18' Or ProductCode = '19' Or ProductCode = '20' Or ProductCode = '61' Or ProductCode = '62' Or ProductCode = '63' Or ProductCode = '85' Or ProductCode = '86' Or ProductCode = '87' Or ProductCode = '88' Or ProductCode = '89' Or ProductCode = '90')
GROUP BY Location

[indydavid32]

Still doesn't work.


CREATE PROC dbo.CigUnits
@BeginDate Datetime, @EndDate datetime

as
If @BeginDate is null or @EndDate is null
Begin
Raiserror('Null values are not allowed',14,1)
Return
End
Select Location, [Date], rootitem, ProductCode, SaleItems, SaleTotal, Count(*) as RowCount
From Item_Sales
Where [Date] >= @BeginDate And [Date] <= @EndDate And (ProductCode = '12' Or ProductCode = '13' Or ProductCode = '15' Or ProductCode = '17' Or ProductCode = '18' Or ProductCode = '19' Or ProductCode = '20' Or ProductCode = '61' Or ProductCode = '62' Or ProductCode = '63' Or ProductCode = '85' Or ProductCode = '86' Or ProductCode = '87' Or ProductCode = '88' Or ProductCode = '89' Or ProductCode = '90')
Group By Location
GO


Same error message. Incorrect syntax near RowCount.

I tried the Group by before and after the GO command.

I tried single and double quotes, and nothing around RowCount.

Guess I'll just do an additional sql call to get the record count.

[MrNorth]

Sorry for bumping in, but when you want to count rows, the easiest thing to do is to use a select count(whatever) and then use the executescalar? Then you don't have to iterate through the entire reader to get the result... easier than the SP too...

just my two cents
Henrik