In this article I will explain with an example, how to use ExecuteScalar method of SqlCommand class in C# and VB.Net while developing ASP.Net, Windows and Console applications.
 
 

Database

I have made use of the following table Customers with the schema as follows.
Understanding SqlCommand ExecuteScalar in C# and VB.Net
 
I have already inserted few records in the table.
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.
Preferred use will be operations, where one Cell value i.e. one column and one row is returned from the SQL Query or Stored Procedure.
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.Data.SqlClient;
using System.Configuration;
 
VB.Net
Imports System.Data.SqlClient
Imports System.Configuration
 
 

What happens when I use ExecuteScalar for SELECT statement with multiple columns and multiple rows?

Initially, the connection is read from App.Config or Web.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 .Net 4.5: Read (Get) Connection String from App.Config file using C# and VB.Net.
          For more details on how to read connection string from Web.Config file, please refer my article Read (Get) Connection String from Web.Config file in ASP.Net 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 Country is returned.
C#
string  name = "Mudassar Khan";
string  sql = "SELECT Country FROM Customers WHERE Name=@Name";
string  constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection  con =  new SqlConnection(constr))
{
    using (SqlCommand  cmd =  new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@Name", name);
        con.Open();
        object  o = cmd.ExecuteScalar();
        if (o !=  null)
        {
            string  country = o.ToString();
        }
        con.Close();
    }
}
 
VB.Net
Dim  name As String  = "Mudassar Khan"
Dim  sql As String  = "SELECT Country FROM Customers WHERE Name=@Name"
Dim  constr As String  = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using  con As SqlConnection  =  New SqlConnection(constr)
    Using  cmd As SqlCommand  =  New SqlCommand(sql, con)
        cmd.Parameters.AddWithValue("@Name", name)
        con.Open()
        Dim  o As Object  = cmd.ExecuteScalar()
        If  oIsNot Nothing Then
            Dim  country As String  = o.ToString()
        End If
        con.Close()
    End Using
End Using
 
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.
C#
string  name = "Mudassar Khan";
string  sql = "SELECT Name, Country FROM Customers WHERE Name=@Name";
string  constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection  con =  new SqlConnection(constr))
{
    using (SqlCommand  cmd =  new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@Name", name);
        con.Open();
        object  o = cmd.ExecuteScalar();
        if (o !=  null)
        {
            string  country = o.ToString();
        }
        con.Close();
    }
}
 
VB.Net
Dim  name As String  = "Mudassar Khan"
Dim  sql As String  = "SELECT Name, Country FROM Customers WHERE Name=@Name"
Dim  constr As String  = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using  con As SqlConnection  =  New SqlConnection(constr)
    Using  cmd As SqlCommand  =  New SqlCommand(sql, con)
        cmd.Parameters.AddWithValue("@Name", name)
        con.Open()
        Dim  o As Object  = cmd.ExecuteScalar()
        If  oIsNot Nothing Then
            Dim  country As String  = o.ToString()
        End If
        con.Close()
    End Using
End Using
 
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.
C#
string  name = "Mudassar Khan";
string  country = "India";
string  sql = "UPDATE Customers SET Country = @Country WHERE Name = @Name";
string  constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection  con =  new SqlConnection(constr))
{
    using (SqlCommand  cmd =  new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@Name", name);
        cmd.Parameters.AddWithValue("@Country", country);
        con.Open();
        object  o = cmd.ExecuteScalar();
        con.Close();
    }
}
 
VB.Net
Dim  name As String  = "Mudassar Khan"
Dim  country As String  = "India"
Dim  sql As String  = "UPDATE Customers SET Country = @Country WHERE Name = @Name"
Dim  constr As String  = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using  con As SqlConnection  =  New SqlConnection(constr)
    Using  cmd As SqlCommand  =  New SqlCommand(sql, con)
        cmd.Parameters.AddWithValue("@Name", name)
        cmd.Parameters.AddWithValue("@Country", country)
        con.Open()
        Dim  o As Object  = cmd.ExecuteScalar()
        con.Close()
    End Using
End Using
 
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 of 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.
 
C#
int customerId;
string  name = "Mudassar Khan";
string  country = "India";
string  sql = "INSERT INTOCustomers(Name, Country) VALUES(@Name, @Country)";
sql += " SELECT SCOPE_IDENTITY()";
string  constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection  con =  new SqlConnection(constr))
{
    using (SqlCommand  cmd =  new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@Name", name);
        cmd.Parameters.AddWithValue("@Country", country);
        con.Open();
         customerId = Convert.ToInt32(cmd.ExecuteScalar());
        con.Close();
    }
}
 
VB.Net
Dim  customerId As Integer
 Dim  name As String  = "Mudassar Khan"
 Dim  country As String  = "India"
 Dim  sql As String  = "INSERT INTOCustomers(Name, Country) VALUES(@Name, @Country)"
  sql += " SELECT SCOPE_IDENTITY()"
 Dim  constr As String  = ConfigurationManager.ConnectionStrings("constr").ConnectionString
 Using  con As SqlConnection  =  New SqlConnection(constr)
     Using  cmd As SqlCommand  =  New SqlCommand(sql, con)
         cmd.Parameters.AddWithValue("@Name", name)
         cmd.Parameters.AddWithValue("@Country", country)
         con.Open()
          customerId = Convert.ToInt32(cmd.ExecuteScalar())
         con.Close()
     End Using
 End Using  
 
Understanding SqlCommand ExecuteScalar in C# and VB.Net
 
Thus, concluding it, we must use ExecuteScalar to fetch a single cell (Scalar) value.