When using the SqlDataReader, how can I tell how many records where found?

When using the SqlDataReader, how can I tell how many records where found?

The SqlDataReader provides a means of reading a "forward-only" stream of rows from a SQL Server database. Hence the number of rows read from the database is not known until the last row is read.

This is the same type of problem we had back in classic ADO where the Recordset's RecordCount was always -1 for a server-side, forward-only cursor. The solution in classic ADO was to use a static or keyset cursor. Or use a client-side cursor location, which always used a static cursor. However in ADO.NET 1.0, there is no such thing as a server-side, static or keyset cursor. There is only the server-side, forward-only DataReader. Or there is the client-side, static DataSet (in classic ADO "terms").

You can work-around this issue by either

1) Increment a counter while looping throuh all of the rows, or

2) use two SELECT statements in your query. The first one returns the row count and the second one returns the actual rows. e.g. "SELECT COUNT(*) FROM myTableName; SELECT * FROM myTableName". Then you can use the NextResult method to move from the first to the second result set, or

3) Or use a DataSet instead of the SqlDataReader. Then use the DataSet's Table Rows count. e.g. oDataSet.Tables("Products").Rows.Count

For more information, see: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q308050

#) Best Practices for Using ADO.NET
Learn about the best solutions for implementing and achieving optimal performance, scalability, and functionality in Microsoft ADO.NET applications


* Information about the .NET Framework data providers included with the .NET Framework.
* Comparisons between the DataSet and the DataReader, and an explanation of the best use for each of these objects.
* An explanation on how to use the DataSet, Commands, and Connections.
* Information about integrating with XML.
* General tips and issues.

more here..http://msdn.microsoft.com/library/defaultasp?url=/library/en-us/dnadonet/html/adonetbest.asp

#) What is the difference between a Recordset and Dataset?

A Recordset is a single set of data from a database, whereas a DataSet is a relational set of data. ADO.NET allows for DataSets to be populated from the database, but DataSets are not exclusively an ADO.NET construct. A single dataset may house related, but distinct sets of data. For example, a dataset may contain a table of information about a patient (think a patient recordset) and also a related table of all the medications the patient is taking. In ADO, you would need to treat these as different recordsets. In ADO.NET this is remarkably different. DataSets are also inherited as disconnected.


With Best Regards,
Mitesh Mehta
Email : miteshvmehta@gmail.com
http://cc.1asphost.com/miteshvmehta/