No RecordCount property exists to indicate how many records are being fetched when you use the OleDbDataReader or SqlDataReader class in Visual C#

Article translations Article translations
Article ID: 308352 - View products that this article applies to.
This article was previously published under Q308352
For a Microsoft Visual Basic .NET version of this article, see 308050.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data
  • System.Data.SqlClient
Expand all | Collapse all

On This Page

SYMPTOMS

When you use the OleDbDataReader or SqlDataReader class, no RecordCount property exists to indicate how many records are being fetched.

CAUSE

The DataReader object or the back-end data source does not typically know how many records are being fetched until the last record is sent to the client.

Even an ActiveX Data Objects (ADO) Recordset returns -1 for the RecordCount property when it uses a forward-only cursor to retrieve data. DataReader exhibits similar behavior because it uses a forward-only cursor to retrieve rows and columns.

RESOLUTION

To work around this problem, use one of the following methods:
  • Count the records as you go through the reader.
  • Run a SELECT COUNT(*) query first. Note that this query may be out of date by the time you finish reading the data.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to reproduce the behavior

  1. Start Microsoft Visual Studio .NET.
  2. Create a new Windows Application in Visual C# .NET. Form1 is created by default.
  3. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
  4. Add a Command button to Form1, and change its Name property and its Text property to btnTest.
  5. Use the using statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code.
    using System;
    using System.Data;
    using System.Data.SqlClient;
    					
  6. Return to Form view, and double-click btnTest to add the click event handler. Add the following code to the handler:
        String myConnString = 
        "User ID=sa;password=sa;Initial Catalog=pubs;Data Source=mySQLServer";
    
        String mySelectQuery  = "SELECT * FROM Authors";
        SqlConnection myConnection = new SqlConnection(myConnString);
        SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
        myConnection.Open();
    
        SqlDataReader myReader ;
        myReader = myCommand.ExecuteReader();
        int RecordCount = 0;
    
        try
        {
    	while (myReader.Read())
    	{
    	    RecordCount++;
    	}
    	if (RecordCount == 0)
                MessageBox.Show("No data returned");
    	else
    	    MessageBox.Show("Number of Records returned: " + RecordCount);
        }
        catch (Exception ex)
        {
    	MessageBox.Show(ex.ToString());
        }    
        finally
        {
    	myReader.Close();
    	myConnection.Close();
        }
    					
  7. Modify the connection string (myConnString) as appropriate for your environment.
  8. Save your project.
  9. On the Debug menu, click Start to run your project.
  10. Click on the button. Notice that you can see the record count.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
194973 ADO: Recordcount may return -1

Properties

Article ID: 308352 - Last Review: December 11, 2006 - Revision: 3.7
APPLIES TO
  • Microsoft ADO.NET 2.0
  • Microsoft ADO.NET 1.0
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
  • Microsoft Visual C# 2005
Keywords: 
kbtshoot kbnofix kbprb kbsqlclient kbsystemdata KB308352

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com