ADO.NET 3.5 Cookbook, 2nd Edition

3.11. Counting Records Meeting Criteria

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 solution executes the COUNT function on the results of a query returning rows from the Person.Contact table in the AdventureWorks sample database, where the last name starts with A.

The C# code in Program.cs in the project CountRecordsMeetingCriteria is shown in Example 3-14.

Example 3-14. File: Program.cs for CountRecordsMeetingCriteria solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace CountRecordsMeetingCriteria
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            // Create the connection.
            SqlConnection connection = new SqlConnection(sqlConnectString);

            // Build the query to count including criteria.
            string selectText = "SELECT COUNT(*) FROM Person.Contact " +
                "WHERE LastName LIKE 'A%'";

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

            Console.WriteLine("Person.Contact records starting with 'A' = {0}",
                recordCount);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey( );
        }
    }
}

The output is shown in Figure 3-11 ...