A command is one SQL statement waiting to be run — but there are three very different ways to run it, depending on what kind of answer you expect back.
You've opened a connection. Now you need to actually do something with it — run a SELECT to fetch rows, an UPDATE to change data, or a quick single-value check like a row count. In ADO.NET, all three of these go through the same object type — SqlCommand — but you execute them in three different ways, and picking the wrong one is a surprisingly common beginner mistake.
In this lesson, you'll learn what a DbCommand represents, and the three execution methods — ExecuteReader, ExecuteNonQuery, and ExecuteScalar — and exactly when to reach for each one.
A SqlCommand (or the provider-agnostic DbCommand) is a single SQL statement, packaged as an object, ready to run against an open connection. It holds the SQL text itself, any parameters it needs (next lesson), and it's the thing you actually "execute" to make something happen in the database.
DbCommand exposes a CommandText property (the SQL, or a stored procedure name), a CommandType (usually CommandType.Text for raw SQL, or CommandType.StoredProcedure), a Parameters collection, and a Connection it will run against. It exposes three core execution methods (plus their async equivalents), each suited to a different shape of result.
| Method | Returns | Use it for… |
|---|---|---|
| ExecuteReader() | A DbDataReader — a stream of rows | SELECT queries returning multiple rows/columns |
| ExecuteNonQuery() | An int — number of rows affected | INSERT, UPDATE, DELETE, DDL statements |
| ExecuteScalar() | An object? — a single value | COUNT(*), SUM(...), or any query returning exactly one row, one column |
Every one of these has an Async counterpart — ExecuteReaderAsync(), ExecuteNonQueryAsync(), ExecuteScalarAsync() — and in modern .NET code (and in EF Core internally), the async versions are what you'll use almost everywhere, exactly as you learned in the async/await lessons.
A SELECT query can return anywhere from zero rows to millions of rows, each with multiple columns — you need something that can stream that efficiently instead of loading it all into memory upfront. An UPDATE statement doesn't return rows at all — the only useful answer is "how many rows did this actually change?" And a query like SELECT COUNT(*) FROM Orders is overkill to wrap in a full row-streaming API when it's really just asking for one number.
Three different execution methods exist because forcing every one of these into a single, one-size-fits-all shape would either waste resources (streaming infrastructure for a single value) or throw away useful information (no way to know how many rows an UPDATE actually touched).
ADO.NET gives you three purpose-built execution methods, each optimized (and named) for exactly one kind of result shape. Picking the right one isn't just about correctness — it's also more efficient and communicates intent clearly to anyone reading your code.
using SqlCommand command = connection.CreateCommand();command.CommandText = "UPDATE Products SET Stock = Stock - 1 WHERE Id = @id";command.Parameters.AddWithValue("@id", productId);int rowsAffected = await command.ExecuteNonQueryAsync();await using SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT Id, Name FROM Products WHERE Stock < 10";
await using SqlDataReader reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
Console.WriteLine($"Low stock: {reader.GetString(1)} (Id {reader.GetInt32(0)})");
}await using SqlCommand command = connection.CreateCommand();
command.CommandText = "UPDATE Products SET Price = Price * 1.10 WHERE Category = @category";
command.Parameters.AddWithValue("@category", "Electronics");
int rowsAffected = await command.ExecuteNonQueryAsync();
Console.WriteLine($"{rowsAffected} products had their price updated.");The returned int is genuinely useful here — if you expected to update 50 rows and it comes back as 0, that's a strong signal something (a typo'd category name, a bad WHERE clause) is wrong, without needing to run a separate check.
await using SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT AVG(Price) FROM Products WHERE Category = @category";
command.Parameters.AddWithValue("@category", "Electronics");
object? result = await command.ExecuteScalarAsync();
decimal averagePrice = result is DBNull or null ? 0m : (decimal)result;
Console.WriteLine($"Average price: {averagePrice:C}");Notice ExecuteScalar returns object?, and can specifically come back as DBNull.Value (not C# null — an ADO.NET-specific marker for a SQL NULL) if the query matched zero rows. That's a real, common gotcha worth remembering.
A checkout flow in an e-commerce system might legitimately use all three in sequence: check remaining stock (ExecuteScalar), decrement it (ExecuteNonQuery), and then fetch the customer's updated order history to display (ExecuteReader):
await using SqlConnection connection = new(connectionString);
await connection.OpenAsync();
// 1. Check stock — one value expected
await using SqlCommand checkStock = connection.CreateCommand();
checkStock.CommandText = "SELECT Stock FROM Products WHERE Id = @id";
checkStock.Parameters.AddWithValue("@id", productId);
int stock = (int)(await checkStock.ExecuteScalarAsync())!;
if (stock <= 0)
throw new InvalidOperationException("Product is out of stock.");
// 2. Decrement stock — no rows returned, just an effect
await using SqlCommand decrementStock = connection.CreateCommand();
decrementStock.CommandText = "UPDATE Products SET Stock = Stock - 1 WHERE Id = @id";
decrementStock.Parameters.AddWithValue("@id", productId);
await decrementStock.ExecuteNonQueryAsync();
// 3. Fetch the customer's recent orders — multiple rows expected
await using SqlCommand recentOrders = connection.CreateCommand();
recentOrders.CommandText = "SELECT Id, OrderDate, Total FROM Orders WHERE CustomerId = @customerId ORDER BY OrderDate DESC";
recentOrders.Parameters.AddWithValue("@customerId", customerId);
await using SqlDataReader reader = await recentOrders.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
// display each order row
}Note: this example intentionally skips the transaction that a real checkout flow like this would need — you'll see exactly why (and how to add one) in the transactions lesson right after parameters.
ExecuteReader doesn't pull the entire result set across the network before returning. It opens a streaming cursor — rows are fetched from the server as you call Read(), in small batches, rather than all at once. This is why a DbDataReader is forward-only and read-once: it's a live stream, not a snapshot sitting in memory. If you need to loop over the same data twice, you either re-run the query or materialize it into a list yourself first — exactly what EF Core's ToListAsync() does for you.
ExecuteScalar, internally, still runs the full query — it just reads only the first column of the first row and discards the rest, closing the reader immediately after. It's convenient, not magically faster than ExecuteReader at the query-execution level, but it saves you from writing reader-looping boilerplate for a single value.
It's a slightly misleading name — an UPDATE or DELETE is absolutely a query in the general sense. "NonQuery" specifically means "doesn't return a result set (rows)" — it returns only a row count. DDL statements like CREATE TABLE also go through ExecuteNonQuery, and typically return -1 since "rows affected" doesn't apply to schema changes.
It returns DBNull.Value, not C#'s null, when the query runs but returns a SQL NULL or zero rows. Casting straight to a value type without checking for DBNull first throws an InvalidCastException — a genuinely common runtime surprise.
Running SELECT COUNT(*) FROM Orders through ExecuteReader, then manually calling Read() once and pulling column 0. It works, but it's more code than necessary and hides intent. Use ExecuteScalar — it says exactly what you mean.
Calling ExecuteNonQuery() on an UPDATE ... WHERE Id = @id and assuming it worked, without checking the returned count. If @id didn't match any row, the statement "succeeds" with zero rows affected — silently doing nothing. Check the count when "did this actually change something?" matters to your logic.
decimal total = (decimal)await command.ExecuteScalarAsync(); when the underlying SUM() could legitimately be NULL (e.g. summing zero matching rows). Check is DBNull or null first, as shown in the example above.
| You need… | Use… |
|---|---|
| Multiple rows and/or columns back | ExecuteReaderAsync() |
| To run an INSERT/UPDATE/DELETE and know how many rows changed | ExecuteNonQueryAsync() |
| Exactly one value — a count, sum, or single lookup | ExecuteScalarAsync() |
You've seen the three execution methods and when each one fits. Let's test it against some scenarios.
1. You need to run DELETE FROM Sessions WHERE ExpiresAt < @now and find out how many expired sessions were removed. Which method should you use?
Correct: B
Why B is correct: A DELETE statement returns no rows — only an effect. ExecuteNonQuery() is exactly built for this: it returns the number of rows the statement affected, which is precisely what you need here.
Why A is incorrect: ExecuteReader is for statements that return a result set of rows — a DELETE doesn't produce one.
Why C is incorrect: ExecuteScalar is for a query returning exactly one value — a DELETE doesn't return a value at all, only a row count via a different mechanism.
Why D is incorrect: No such method exists in ADO.NET's DbCommand API — there is no per-statement-type execute method.
Reinforcement: Any statement whose useful result is "how many rows changed" goes through ExecuteNonQuery, regardless of whether it's INSERT, UPDATE, or DELETE.
2. A query SELECT MAX(Price) FROM Products WHERE Category = @category is run with a category that matches zero rows. What does ExecuteScalarAsync() return?
Correct: B
Why B is correct: When an aggregate like MAX() has no matching rows, SQL returns a NULL, which ADO.NET represents as DBNull.Value — a distinct object from C#'s null. Casting it straight to decimal throws.
Why A is incorrect: It's DBNull.Value, a real object instance representing a database NULL — not C#'s null reference. A plain is null check would miss it.
Why C is incorrect: ADO.NET doesn't silently convert a SQL NULL to zero — you must handle that conversion explicitly in your code.
Why D is incorrect: The query executes successfully; it's your subsequent cast of the DBNull result that throws, not ExecuteScalarAsync itself.
Reinforcement: Always check for DBNull before casting an ExecuteScalar result to a value type.
3. Why is a DbDataReader described as "forward-only, read-once"?
Correct: A
Why A is correct: ExecuteReader opens a streaming cursor, pulling rows from the server as you call Read() rather than loading the entire result set into memory upfront. You move through it once, forward only — you can't rewind or re-iterate without re-running the query.
Why B is incorrect: It can read as many rows as the query returns — the "forward-only" description refers to iteration direction, not row count.
Why C is incorrect: A new reader is created every time you call ExecuteReader — nothing limits it to once per application.
Why D is incorrect: A reader can expose any number of columns per row — column count is unrelated to "forward-only, read-once."
Reinforcement: If you need to iterate the same data multiple times, materialize it into a collection first — don't try to reuse a reader.
4. You call ExecuteNonQueryAsync() for UPDATE Products SET Price = @p WHERE Id = @id and it returns 0. What does that most likely indicate?
Correct: B
Why B is correct: The returned integer is the count of rows the statement actually affected. Zero means the statement executed without error, but its WHERE clause matched nothing — a strong hint the @id parameter didn't correspond to an existing row.
Why A is incorrect: If the database were unreachable, you'd get a connection or command exception, not a successful call returning 0.
Why C is incorrect: The returned value is a row count, not the new price — it has nothing to do with what value was set.
Why D is incorrect: ExecuteNonQuery returns the actual number of affected rows — it isn't hardcoded to 0 for UPDATE statements.
Reinforcement: Checking the row count from ExecuteNonQuery is a cheap, valuable way to catch WHERE clauses that silently matched nothing.
You now know exactly which execution method fits which kind of SQL statement. Next up: a security-critical lesson — parameters, and why they're non-negotiable.
dotnetmadeeasy.com — Learn C# and .NET, the right way.