The DataReader is the ADO.NET counterpart of the forward-only, read-only recordset in classic ADO. For this reason you never know how many rows the DataReader is about to return. In most cases you don’t need this information, because you tipically process each row as soon as it is being returned, and you never need to store the row in an array (in which case the number of rows would be a useful information). As a matter of fact, if you need to store each row you should stay clear of the DataReader and use a DataTable plus a DataAdapter instead (which internally uses a DataReader and is therefore quite efficient).

At any rate, at times it may be useful to know how many rows the DataReader is about to return, for example to let you create a progress bar or inform the user about the estimate end time of the operation. You can do this by issuing a preliminary SELECT statement that returns the number of rows in the resultset you’re about to return. For example, you can run this code

' open the connection towards SQL Server's NorthwindDim connString As String = _    "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial " _    & "Catalog=Northwind;Data Source=."Dim cn As New OleDbConnection(connString)cn.Open()' read the number of rowsDim cmd As New OleDbCommand("SELECT COUNT(*) FROM Customers", cn)Dim totalRows As Long = CInt(cmd.ExecuteScalar())' read the individual rowscmd = New OleDbCommand("SELECT * FROM Customers", cn)Dim dr As OleDbDataReader = cmd.ExecuteReader()Dim currRow As IntegerDo While dr.Read     ' update the label     currRow += 1     Label1.Text = String.Format("{0}% complete", currRow * 100  totalRows)     Label1.Refresh()     ' process the row here     ' ...Loopdr.Close()cn.Close()

The main drawbacks of this solution are the added overhead for the additional query and the fact that you need two round-trips to the server to read the totalRows value. You can’t avoid the additional query, but you can get rid of the extra roundtrip if you work with SQL Server or another database that supports multiple queries in one command. This code shows how:

' open the connection towards SQL Server's NorthwindDim connString As String = _    "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial " _    & "Catalog=Northwind;Data Source=."Dim cn As New OleDbConnection(connString)cn.Open()' prepare a two-statement commandDim cmd As New OleDbCommand("SELECT COUNT(*) FROM Customers;SELECT * FROM " _    & "Customers", cn)Dim dr As OleDbDataReader = cmd.ExecuteReader()' the value is in the only column of the only row of the first resultsetdr.Read()Dim totalRows As Long = CInt(dr(0))' actual values are in the second resultsetdr.NextResult()Dim currRow As IntegerDo While dr.Read    ' update the label    currRow += 1    Label1.Text = String.Format("{0}% complete", currRow * 100  totalRows)    Label1.Refresh()    ' process the row here    ' ...Loopdr.Close()cn.Close()

Keep in mind that you should never trust the value returned by the first query, because another user might add or delete rows before the second query is completed. For this reason you should consider it only as the approximate number of rows about to be returned. The only way to be 100% certain that the value returned by the first query is correct is running both commands in a Serializable transactions, which would seriously affect the scalability of your application.