ADO.NET 3.5 Cookbook, 2nd Edition

3.2. Executing a Query That Returns a Single Value

Problem

You want to execute a query that returns a single value.

Solution

Use the ExecuteScalar() method of the Command object to return a single value from a query.

The solution uses the ExecuteScalar() method to get the number of records in the Person.Contact table in AdventureWorks. The number of records is output to the console.

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

Example 3-3. File: Program.cs for RetrieveSingleValueFromQuery solution

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

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

            string sqlSelect = "SELECT COUNT(*) FROM Person.Contact";

            SqlConnection connection = new SqlConnection(sqlConnectString);

            // Create the scalar command and open the connection
            SqlCommand command = new SqlCommand(sqlSelect, connection);
            connection.Open( );

            // Execute the scalar SQL statement and store results.
            int count = Convert.ToInt32(command.ExecuteScalar( ));
            connection.Close( );

            Console.WriteLine("Record count in Person.Contact = {0}", count);

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

The output is shown in Figure 3-2.

Figure 3-2. Output for RetrieveSingleValueFromQuery ...