O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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"]); ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required