ExecuteScaler()

The sqlCommand class has a special function called ExecuteScaler() it is used when you need to retrieve just one piece of data, this data comes back as an object so you have to cast it whatever type it is.

using System;

using System.Data.SqlClient;

namespace pc.sqlscalerExample

{

    class Program

    {

        static string cs =

            @"Data Source=BEAST\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";

        static void Main(string[] args)

        {

            //using statement to ensure dispose of

            using (var conn = new SqlConnection(cs))

            {

                try

                {

                    string GetPersons = "SELECT COUNT(id) FROM Person;";

                    var cmnd = new SqlCommand(GetPersons, conn);

                    conn.Open();

                    var retunrnValue = cmnd.ExecuteScalar();

                    if (retunrnValue is Int32)

                        Console.WriteLine("There is {0} Person rows", retunrnValue);

                }

                catch (SqlException sqlEx)

                {

                    //display sql exception

                    Console.WriteLine(sqlEx.Message);

                }

                catch (Exception Ex)

                {

                    //display all other exceptions

                    Console.WriteLine(Ex.Message);

                }

                finally

                {

                    //ensure that the connection is closed everytime

                    conn.Close();

                }

            }

        }

    }

}

This is ideal when retrieving an aggregate value such as count, or max.