In this article I will explain with example, how to use SqlCommand ExecuteScalar method in C# and VB.Net while developing ASP.Net, Windows and Console applications.
 
 
 Database
I have made use of the following table Persons with the schema as follows.
Understanding SqlCommand ExecuteScalar in C# and VB.Net
 
Note: You can download the database table SQL by clicking the download link below.
         Download SQL file
 
 
The ExecuteScalar method
ExecuteScalar is a handy function when you want to just need one Cell value i.e. one column and one row.
ExecuteScalar is basically used for operations where there is nothing returned from the SQL Query or Stored Procedure. Preferred use will be operations where one Cell value i.e. one column and one row is returned.
 
Namespaces
You will need to import the following namespaces.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
What happens when I use ExecuteScalar for SELECT statement with multiple columns and multiple rows?
Initially, the connection string is fetched from the App.Config file and object of SqlConnection class is created using it.
Note: For more details on how to read Connection String from App.Config file, please refer my article Read (Get) Connection String from App.Config file using C# and VB.Net.
 
Then an object of SqlCommand class is created and the SELECT query is passed to it as parameter.
The value of the name is added as parameter to SqlCommand object.
Finally, the connection is opened and the ExecuteScalar function is executed and the value of the City is returned.
string name = "Mudassar Khan";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
    using (SqlCommand cmd = new SqlCommand("SELECT City FROM Persons WHERE Name=@Name", con))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Name", name);
        con.Open();
        object o = cmd.ExecuteScalar();
        if (o != null)
        {
            string city = o.ToString();
        }
        con.Close();
    }
}
 
The screenshot below displays the value of the column returned.
Understanding SqlCommand ExecuteScalar in C# and VB.Net
 
What happens when I use ExecuteScalar for SELECT statement with multiple columns and multiple rows?
This is a great question and the answer is yes you can use it but as its behavior it will return the very first cell i.e. first row and first column.
using (SqlConnection con = new SqlConnection(constring))
{
    using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons WHERE Name=@Name", con))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Name", name);
        con.Open();
        object o = cmd.ExecuteScalar();
        if (o != null)
        {
            string city = o.ToString();
        }
        con.Close();
    }
}
 
The screenshot below displays the value of the first cell i.e. first row and first column being returned.
Understanding SqlCommand ExecuteScalar in C# and VB.Net
 
Can we use ExecuteScalar for INSERT, UPDATE and DELETE Statements?
Yes you can. But since INSERT, UPDATE and DELETE Statements return no value you will not get any value returned from the Query as well as you will not get the Rows Affected like you get in ExecuteNonQuery function.
string name = "Mudassar Khan";
string city = "Pune";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
    using (SqlCommand cmd = new SqlCommand("UPDATE Persons SET City = @City WHERE Name = @Name", con))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Name", name);
        cmd.Parameters.AddWithValue("@City", city);
        con.Open();
        object o = cmd.ExecuteScalar();
        con.Close();
    }
}
 
The screenshot below displays the returned value being returned as NULL since there’s nothing returned from the UPDATE query.
Understanding SqlCommand ExecuteScalar in C# and VB.Net
 
Another case would be when you have an Identity (Auto Increment) column and you need to get the value of the ID column newly added record.
If you want to return a value then you can combine an INSERT, UPDATE and DELETE Statement with a SELECT statement and then the ExecuteScalar function will return value. For example, in the below code, the ID of the new record that was inserted is fetched using SCOPE_IDENTITY.
For more details, please refer my article Using SqlCommand ExecuteScalar with example in C# and VB.Net.
int customerId;
string query = "INSERT INTO Customers(Name, Country) VALUES(@Name, @Country)";
query += " SELECT SCOPE_IDENTITY()";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = newSqlConnection(constr))
{
    using (SqlCommand cmd = newSqlCommand(query))
    {
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@Country", ddlCountries.SelectedItem.Text);
        cmd.Connection = con;
        con.Open();
        customerId = Convert.ToInt32(cmd.ExecuteScalar());
        con.Close();
    }
}
 
Understanding SqlCommand ExecuteScalar in C# and VB.Net
 
Thus concluding it, we must use ExecuteScalar to fetch a single cell (Scalar) value.