2. ExecuteReader vs ExecuteScalar vs ExecuteNonQuery

// ExecuteScalar — first column of first row
var count = (int)(await new SqlCommand("SELECT COUNT(*) FROM Orders", conn).ExecuteScalarAsync())!;

// ExecuteNonQuery — INSERT/UPDATE/DELETE/DDL; returns rows affected
var cmd = new SqlCommand("UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = @c", conn);
cmd.Parameters.AddWithValue("@c", 3);
int affected = await cmd.ExecuteNonQueryAsync();

// Insert and get the new identity in one round trip
var ins = new SqlCommand("INSERT INTO Customers(Name) OUTPUT INSERTED.Id VALUES (@n)", conn);
ins.Parameters.Add("@n", SqlDbType.NVarChar, 100).Value = "Acme";
int newId = (int)(await ins.ExecuteScalarAsync())!;