Getting record count

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I need to go through a resultset record by record. Currently I am using an
OleDbDataReader to achieve this. Is there a way to get the total record
count so I can notify users of the progress? If not via OleDbDataReader, is
there another way?

Thanks

Regards
 
John said:
Hi

I need to go through a resultset record by record. Currently I am using an
OleDbDataReader to achieve this. Is there a way to get the total record
count so I can notify users of the progress? If not via OleDbDataReader, is
there another way?

Thanks

Regards
Click to expand...

A data reader doesn't know how many records there are in the result.
There are basically two ways that you can get the record count before
processing the records:

1. Make two queries, where the first query only counts the result.

2. First read the records from the data reader into a list, and the
process the items in the list.
 
Hi

I have used below code for record count;

Dim Cmd2 As New OleDbCommand(SQLStr, Conn)
iCount = Cmd2.ExecuteNonQuery

But iCount comes out to zero. However the data reader code below;

Dim objResult As OleDb.OleDbDataReader
Dim Cmd As New OleDbCommand(SQLStr, Conn)

objResult = Cmd.ExecuteReader()
While objResult.Read()
...
End while

brings records fine. What can I do to record count?

Thanks

Regards
 
Most of the operations I've seen in code-reviews that process row-by-row
could better be done on the server in a stored procedure--unless you don't
care about performance or scalability.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
John said:
Hi

I have used below code for record count;

Dim Cmd2 As New OleDbCommand(SQLStr, Conn)
iCount = Cmd2.ExecuteNonQuery

But iCount comes out to zero. However the data reader code below;

Dim objResult As OleDb.OleDbDataReader
Dim Cmd As New OleDbCommand(SQLStr, Conn)

objResult = Cmd.ExecuteReader()
While objResult.Read()
...
End while

brings records fine. What can I do to record count?

Thanks

Regards
Click to expand...

The ExecuteNonQuery method returns the number of records affected. If
you are not updating any records in the query, it will return zero.

To count the number of records you use the count function. Example:

"select count(*) from SomeTable where SomeField = something"

Use the ExecuteScalar method to run the query and cast the result to an int.
 
If you are doing something (else besides a count) with the IDataReader
results, then just add a counter.
If you're only interested in the count, do a count(*) from the database and
use .ExecuteScalar.



1.

dim counter as integer = 0

Dim objResult As OleDb.OleDbDataReader
Dim Cmd As New OleDbCommand(SQLStr, Conn)

objResult = Cmd.ExecuteReader()
While objResult.Read()
counter = counter + 1 '' i can't remember if vb.net does counter
+=1;

''Assume you're doing something else with the results from the
reader
End while


2. If you ONLY want the count, then the above code (1) is very bad.

Select count(*) as EmployeeCOUNT from dbo.Employee

Then research the .ExecuteScalar method.
 
Yes, but Count(*) is only an approximation on an active DBMS. It returns the
number of rows that would be returned (which can take considerable time to
resolve) at the time of the query. After that, as rows are added and deleted
this count will vary.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
William said:
Yes, but Count(*) is only an approximation on an active DBMS. It returns
the number of rows that would be returned (which can take considerable
time to resolve) at the time of the query. After that, as rows are added
and deleted this count will vary.
Click to expand...

Good point.

If the count is just going to be used for displaying the progress, a
small risk of a discrepancy should be acceptable. Of course the
possibility has to be handled in the code.

If the count has to be exact, the two queries would have to be put in a
transaction, so that the data can not change inbetween them.
 
In that case, why bother? Most applications today use approximation and
progress bars that simply entertain and show steady progress as opposed to
informing of exact progress. The cost of generating the count can be
considerable... wouldn't the user's time be better served by just fetching
the rows or not fetching so many as to have it make any difference?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
 
Back
Top