ExecuteReader

Used to perform field-to-field reading on a data extraction. This is done through the object of type SqlDataReader returned by the method.

Example:

using (var connection = new SqlConnection(
           connectionString))
{
    connection.Open();

    var command = new SqlCommand("Select * from MinhaTabela", connection);
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        for(int i = 0 ; i < count ; i++) {
            Console.WriteLine(reader.GetValue(i));
        }
    }
}

This is a rather verbose method for direct column access.

ExecuteNonQuery

Executes a command that does not have a row and column relationship. Your return is actually the number of rows affected by the command.

Used for sentences like INSERT , UPDATE , DELETE , etc.

Example:

using (var connection = new SqlConnection(
           connectionString))
{
    var command = new SqlCommand("UPDATE MinhaTabela set MeuCampo = 1 where OutroCampo = 2", connection);
    command.Connection.Open();
    var registrosAfetados = command.ExecuteNonQuery();
}

ExecuteScalar

Executes a command and returns the first column of the first row returned. Usually useful for executing, for example, Stored Procedures that returns a specific value or functions as a count of records.

Example:

using (var connection = new SqlConnection(connString))
{
    var cmd = new SqlCommand(conn);
    cmd.CommandText = "SELECT COUNT(*) FROM MinhaTabela";

    try
    {
        conn.Open();
        var contagem = (Int32)cmd.ExecuteScalar();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}