In this article I will explain the difference between ExecuteReader, ExecuteScalar and Execute functions of the Dapper library.
All the three functions i.e. ExecuteReader, ExecuteScalar and Execute are for different purposes. Newbies generally face difficulty in understanding these three methods.
 
 

Installing Dapper package using Nuget

In order to install Dapper library using Nuget, please refer my article Install Dapper from Nuget in Visual Studio.
 
 

ExecuteReader

ExecuteReader is strictly used for fetching records from the SQL Query or Stored Procedure i.e. SELECT Operation.
Example would be fetching Name and Country for all records in the Customers Table.
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string sql = "SELECT Name, Country FROM Customers";
using (SqlConnection con = new SqlConnection(constr))
{
    using (IDataReader sdr = con.ExecuteReader(sql))
    {
        while (sdr.Read())
        {
            string name = sdr["Name"].ToString();
            string country = sdr["Country"].ToString();
        }
    }
}
 
Difference between Dapper ExecuteReader ExecuteScalar and Execute
 
Once the ExecuteReader method is executed, it returns an object belonging to IDataReader Interface. Since we are dealing with SQL Server, I have used IDataReader. 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.
 
 

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 case where I need to get the Country of a customer based on its Name.
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))
{
    object o = con.ExecuteScalar(sql, new { name });
    if (o != null)
    {
        string country = o.ToString();
    }
}
 
The screenshot below displays the value of the column returned.
Difference between Dapper ExecuteReader ExecuteScalar and Execute
 

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.
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))
{
    object o = con.ExecuteScalar(sql, new { name });
    if (o != null)
    {
        string country = o.ToString();
    }
}
 
The screenshot below displays the value of the first cell i.e. first row and first column being returned.
Difference between Dapper ExecuteReader ExecuteScalar and Execute
 

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 Execute function.
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))
{
    object o = con.ExecuteScalar(sql, new { name, country });
}
 
The screenshot below displays the returned value being returned as NULL since there’s nothing returned from the UPDATE query.
Difference between Dapper ExecuteReader ExecuteScalar and Execute
 
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 on SCOPE_IDENTITY, please refer my article Using SCOPE_IDENTITY with ADO.Net in ASP.Net.
int customerId;
string sql = "INSERT INTO Customers (Name, Country) VALUES (@Name, @Country)";
sql += " SELECT SCOPE_IDENTITY()";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    string name = txtName.Text;
    string country = ddlCountries.SelectedItem.ToString();
    customerId = Convert.ToInt32(con.ExecuteScalar(sql, new { name, country }));
}
 
Difference between Dapper ExecuteReader ExecuteScalar and Execute
 
Thus concluding it, we must use ExecuteScalar to fetch a single cell (Scalar) value.
 
 

Execute

Execute 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.
It returns the count of the number of rows affected during the operation, for example, if one row was inserted, updated or deleted, it will return 1 and if no row was affected it will return 0.

INSERT

string name = "Mudassar Khan";
string country = "India";
string sql = "INSERT INTO Customers (Name, Country) VALUES (@Name, @Country)";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    int rowsAffected = con.Execute(sql, new { name, country });
}
 
The screenshot below displays the rows affected.
Difference between Dapper ExecuteReader ExecuteScalar and Execute
 

UPDATE

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))
{
    int rowsAffected = con.Execute(sql, new { name, country });
}
 
The screenshot below displays the rows affected.
Difference between Dapper ExecuteReader ExecuteScalar and Execute
 

DELETE

string name = "Mudassar Khan";
string sql = "DELETE FROM Customers WHERE Name = @Name";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    int rowsAffected = con.Execute(sql, new { name });
}
 
The screenshot below displays the rows affected.
Difference between Dapper ExecuteReader ExecuteScalar and Execute
 

What happens when I use Execute for SELECT statement?

Execute 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 sql = "SELECT * FROM Customers";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    int rowsAffected = con.Execute(sql);
}
 
The screenshot below displays the rows affected returned -1.
Difference between Dapper ExecuteReader ExecuteScalar and Execute
 
Thus concluding it, we must use Execute for INSERT, UPDATE and DELETE operations only.
Therefore, 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.