In this article I will explain the difference between ExecuteNonQuery, ExecuteScalar and ExecuteReader functions of the SqlCommand class in ADO.Net.
All the three functions i.e. ExecuteNonQuery, ExecuteScalar and ExecuteReader are for different purposes. Newbies generally face difficulty in understanding these three methods.
ExecuteNonQuery
ExecuteNonQuery is basically used for operations where there is nothing returned from the SQL Query or Stored Procedure. Preferred use will be for INSERT, UPDATE and DELETE Operations.
INSERT
string name = "Mudassar Khan";
string city = "Pune";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
    using (SqlCommand cmd = new SqlCommand("INSERT INTO Persons (Name, City) VALUES (@Name, @City)", con))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Name", name);
        cmd.Parameters.AddWithValue("@City", city);
        con.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        con.Close();
    }
}
difference between executereader executescalar and executenonquery
UPDATE
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();
        int rowsAffected = cmd.ExecuteNonQuery();
        con.Close();
    }
}
The screenshot below displays the rows affected.
difference between executereader executescalar and executenonquery
DELETE
string name = "Mudassar Khan";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
    using (SqlCommand cmd = new SqlCommand("DELETE FROM Persons WHERE Name = @Name", con))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Name", name);
        con.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        con.Close();
    }
}
The screenshot below displays the rows affected.
difference between executereader executescalar and executenonquery
What happens when I use ExecuteNonQuery for SELECT statement?
ExecuteNonQuery will work flawlessly for SELECT SQL Query or Stored Procedure but that will simply execute the query and do nothing. Even if you use it you will not throw any error but the Rows Affected will be negative i.e. -1.
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Persons", con))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Name", name);
        cmd.Parameters.AddWithValue("@City", city);
        con.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        con.Close();
    }
}
The screenshot below displays the rows affected returned -1.
difference between executereader executescalar and executenonquery
Thus concluding it, we must use ExecuteNonQuery for INSERT, UPDATE and DELETE operations only.
ExecuteScalar
ExecuteScalar is a handy function when you want to just need one Cell value i.e. one column and one row.
For example in a case where I need to get the City of a person based on its Name.
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.
difference between executereader executescalar and executenonquery
Another case would when you have an Identity (Auto Increment) column and you need to get the value of the ID column newly added record. You can refer my article for more reference
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.
difference between executereader executescalar and executenonquery
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.
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.
difference between executereader executescalar and executenonquery
Thus concluding it, we must use ExecuteScalar must be when used when you need to fetch a single cell (Scalar) value.
ExecuteReader
ExecuteReader is strictly used for fetching records from the SQL Query or Stored Procedure i.e. SELECT Operation.
Example would be fetching Name City for all records in the Person Table.
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
    using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons", con))
    {
        cmd.CommandType = CommandType.Text;
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            string name = dr["Name"].ToString();
            string city = dr["City"].ToString();
            Response.Write("Name: " + name);
            Response.Write("City: " + city);
        }
        con.Close();
    }
}
difference between executereader executescalar and executenonquery
Once the ExecuteReader method is executed it returns an object belonging to IDataReader Interface. Since we are dealing with SQL Server, I have used SqlDataReader. Until the DataReader Read method is returning true, it means that it is fetching record.
Hence a while loop is executed and records are fetched one by one.
ExecuteReader can also be used to bind a GridView control, but do it only if the GridView does not need Paging to be implemented. This is necessary since if you set AllowPaging to True and bind GridView using DataReader then you will land into an Exception as DataReader fetches records in ForwardOnly Mode.
For binding GridView using DataReader you can refer my article How to bind GridView with DataReader in ASP.Net using C# and VB.Net
But I would recommend using DataReader for GridViews without paging since DataReader is more efficient that SqlDataAdapter.
Thus I have tried to cover most of the aspects of this topic and tried to explain the difference between each of these three methods in a short and illustrious way.