Counting Records


You want to determine how many rows that meet certain criteria are in a table.


Use the ExecuteScalar( ) method of the Command object to determine the number of records in the table.

The sample code executes the COUNT function on the results of a query returning rows from the Orders table in the Northwind sample database, where the rows match a user -specified CustomerID.

The C# code is shown in Example 10-5.

Example 10-5. File: CountRecordForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

// . . . 

// Create the connection.
SqlConnection conn = new SqlConnection(

// Build the query to count, including CustomerID criteria if specified.
String selectText = "SELECT COUNT(*) FROM Orders";
if(customerIdTextBox.Text.Trim( ) != "")
 selectText += " WHERE CustomerId='" + customerIdTextBox.Text + "'";

// Create the command to count the records.
SqlCommand cmd = new SqlCommand(selectText, conn);
// Execute the command, storing the results.
conn.Open( );
int recordCount = (int)cmd.ExecuteScalar( );
conn.Close( );


The ExecuteScalar( ) method of the Command object returns a single value from a query rather than a table or a data stream. If the query returns a result set, this method returns the value of the first column of the first row.

The number of records matching certain criteria can be determined by executing a SQL statement that returns the COUNT(*) aggregate function and including a WHERE clause that specifies the criteria. Use the ExecuteScalar( ) method to execute the statement and return the count. Cast the result to an integer data type.

This technique can also be used with other aggregate functions to determine values such as averages or sums. For more information about aggregate functions, see Microsoft SQL Server Books Online.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax

ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton © 2008-2020.
If you may any questions please contact us: