# Counting Records in a DataReader

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:4

0
58
lượt xem
5

## Counting Records in a DataReader

Mô tả tài liệu

[ Team LiB ] Recipe 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

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: Counting Records in a DataReader

1. [ Team LiB ] Recipe 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
2. // 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"]); SqlCommand cmd = new SqlCommand(sqlText, conn); conn.Open( ); // Create a DataReader on the first result set. SqlDataReader dr = cmd.ExecuteReader( ); // Get the count of records from the select count(*) statement. dr.Read( ); resultTextBox.Text = "Orders table record count, using COUNT(*)= " + dr.GetInt32(0) + Environment.NewLine; // Move to the data result set. dr.NextResult( ); int count = 0; // Iterate over the records in the DataReader. while(dr.Read( )) { count++; // . . . Do something interesting with the data here. } // Close the DataReader and the connection. dr.Close( ); resultTextBox.Text += "Orders table record count, " + "iterating over result set = " + count + Environment.NewLine;