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 aDataReader
after theDataReader
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"]); ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.