Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: VB7
Expertise: Intermediate
Oct 7, 2002

Determine how many records the DataReader is about to return

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 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:

' 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.
Francesco Balena
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap