ADO.NET Cookbook

10.5. Counting Records

Problem

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

Solution

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(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);

// 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( );

Discussion

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