ADO.NET 3.5 Cookbook, 2nd Edition

3.12. Determining the Number of Records Returned in a DataReader

Problem

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

Solution

Use one of the following three techniques:

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

• Iterate over the rows in the DataReader and use a counter.

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

The solution uses a single stored procedure:

Person.GetContacts

Returns a result set containing all records in the Person.Contact table in AdventureWorks. Also, the stored procedure returns the @@ROWCOUNT value for the query in an output parameter. The stored procedure is shown in Example 3-15.

Example 3-15. Stored procedure: Person.GetContacts

CREATE PROCEDURE Person.GetContacts
    @RowCount int OUTPUT
AS
    SET NOCOUNT ON

    SELECT * FROM Person.Contact

    set @RowCount = @@ROWCOUNT

    RETURN @RowCount

The C# code in Program.cs in the project DataReaderRowCount is shown in Example 3-16.

Example 3-16. File: Program.cs for DataReaderRowCount solution

using System; using System.Data; using System.Data.SqlClient; namespace DataReaderRowCount { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; // Batch query ...