Delphi for .NET Developer’s Guide

Retrieving Single Values

Oftentimes, you’ll need to retrieve a single value from a query, particularly when returning an aggregate value. SqlCommand implements the ExecuteScalar() method, which returns such a value.

Examine the following SQL statement:

SELECT Count(*) AS CanCust FROM customers WHERE country = 'Canada'

This particular statement will retrieve the total number of records in a table named customers, indicating customers who reside in Canada. The value returned will be returned as a single field named CanCust. Listing 19.2 illustrates using the ExecuteScalar() method to retrieve this value.

Listing 19.2. Use of IDbCommand.ExecuteScalar() Method

1: program ExScalar; 2: 3: {$APPTYPE CONSOLE} 4: 5: {%DotNetAssemblyCompiler 'C:\windows\microsoft.net\framework\v1.1.4322\System.Data.dll'} ...