ADO.NET Cookbook

2.7. Counting Records in a DataReader

Problem

You want to determine how many records there are in a DataReader.

Solution

Use one of the following three techniques:

• Iterate over the rows in the DataReader.

• Issue a COUNT(*) query as part of a batch query. Note that not all data sources support batch queries. If not, execute the statements separately one after the other for a similar result.

• Use the @@ROWCOUNT function to return the number or rows in a DataReader after the DataReader has been closed. This technique is SQL Server specific.

The sample code uses a single stored procedure:

SP0207_GetOrders

Returns a result set containing all records in the Orders table in Northwind. Also, the stored procedure returns the @@ROWCOUNT value for the query in an output parameter. The stored procedure is shown in Example 2-7.

Example 2-7. Stored procedure: SP0207_GetOrders

ALTER PROCEDURE SP0207_GetOrders
    @RowCount int output
AS
    set nocount on
    
    select * from Orders
    
    set @RowCount = @@ROWCOUNT

    RETURN

The C# code is shown in Example 2-8.

Example 2-8. File: DataReaderRowCountForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; // . . . // Batch query to retrieve the COUNT of records and // all of the records in the Orders table as two result sets. String sqlText = "SELECT COUNT(*) FROM Orders; " + "SELECT * FROM Orders;"; // Create the connection. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); ...