Binaryworld - Determine how many records the DataReader is about to return ... [ VB.net -> ADO.net ]

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

Click here to copy the following block

' open the connection towards SQL Server's Northwind
Dim 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 rows
Dim cmd As New OleDbCommand("SELECT COUNT(*) FROM Customers", cn)
Dim totalRows As Long = CInt(cmd.ExecuteScalar())

' read the individual rows
cmd = New OleDbCommand("SELECT * FROM Customers", cn)
Dim dr As OleDbDataReader = cmd.ExecuteReader()

Dim currRow As Integer
Do While dr.Read
   ' update the label
   currRow += 1
   Label1.Text = String.Format("{0}% complete", currRow * 100 \ totalRows)
   Label1.Refresh()
   ' process the row here
   ' ...

Loop
dr.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:

Click here to copy the following block

' open the connection towards SQL Server's Northwind
Dim 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 command
Dim 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 resultset
dr.Read()
Dim totalRows As Long = CInt(dr(0))

' actual values are in the second resultset
dr.NextResult()
Dim currRow As Integer
Do While dr.Read
  ' update the label
  currRow += 1
  Label1.Text = String.Format("{0}% complete", currRow * 100 \ totalRows)
  Label1.Refresh()
  ' process the row here
  ' ...
Loop
dr.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.