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

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.