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

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

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