| Lesson 3 | Creating a command object |
| Objective | Create a command object to query a database. |
Creating a Command Object to Query the Database
The previous lesson built a complete SqlConnection/SqlCommand/SqlDataReader round trip, retrieving every row from VirtualBookShelf's Inventory table and displaying it on a Razor Page. That lesson only used one of SqlCommand's three ways of running, though, the one built for SELECT statements that return rows. This lesson looks at SqlCommand more broadly: what a command object actually represents, the other two ways it can execute, and the difference between handing it a raw SQL statement and pointing it at a stored procedure that already exists.
What a Command Object Actually Is
A command object is part of the application layer, not the database engine itself. It's a package: a SQL statement or a stored procedure name, any parameters that statement needs, and a reference to the connection it should run on. The database engine doesn't know or care that a "command object" asked it to run something; as far as SQL Server is concerned, a request arrived and got executed.
This concept isn't new to this course's modern stack. In Visual Basic and Visual InterDev, a command object was created in code to send SQL or stored procedure calls to SQL Server, the direct ancestor of what this lesson covers. The legacy command object's job and
SqlCommand's job are, at this level, the same job: hold a statement, hold whatever values that statement needs, and know which connection to run on. What changed is almost entirely syntax and safety, not the underlying idea. Where a Classic ASP page might have built a command's SQL by concatenating a visitor's input directly into a string,
SqlCommand's
Parameters collection, used in every example in this lesson, keeps that value separate from the statement text at all times, closing off the SQL injection risk a concatenated string leaves wide open.
Modern .NET still uses command objects, in a more structured form:
| Layer | Modern Practice |
| SQL Server Engine | Stored procedures, views, and functions live here, SQL Server 2025 in this course. |
| .NET Application | Use SqlCommand (ADO.NET), or, once this course introduces it, LINQ and Entity Framework Core. |
| Best Practice | Use parameterized commands to prevent SQL injection, the same rule Module 3 already established for raw SQL. |
Three Ways to Execute a Command
Every SqlCommand runs one of three ways, chosen by which method gets called on it. The right one depends entirely on what kind of statement it's holding.
ExecuteReader, for Statements That Return Rows
ExecuteReader is what Lesson 2 already used: it runs a
SELECT and hands back a
SqlDataReader to step through whatever rows come back.
SqlCommand command = new("SELECT BookID, Title, Author, Price FROM Inventory", connection);
using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// read each row, as shown in the previous lesson
}
Nothing here is new; it's included as a reference point for the two methods that follow.
ExecuteNonQuery, for Statements That Change Rows
INSERT,
UPDATE, and
DELETE don't return rows, so running one through
ExecuteReader would be the wrong tool entirely.
ExecuteNonQuery is built for exactly this case: it runs the statement and returns an
int, the number of rows the statement actually affected, instead of a reader to loop through.
Module 3 wrote the SQL for updating a book's stock on hand using a compound assignment operator. Running that same statement from C#, parameterized, looks like this:
SqlCommand command = new("UPDATE Inventory SET QuantityOnHand -= 1 WHERE BookID = @BookID", connection);
command.Parameters.AddWithValue("@BookID", bookId);
int rowsAffected = command.ExecuteNonQuery();
rowsAffected is worth checking. If
bookId doesn't match any row in
Inventory, the statement still runs successfully, no error is thrown, it just updates zero rows, and
rowsAffected comes back as
0 rather than
1. A production application usually treats that as a condition worth handling, logging it or telling the caller the update didn't find anything to change, rather than silently assuming it worked.
INSERT and
DELETE run through
ExecuteNonQuery exactly the same way. Adding a new customer, using the parameterized pattern Module 3 already established, looks like this:
SqlCommand command = new(
"INSERT INTO Customer (FirstName, LastName, Email) VALUES (@FirstName, @LastName, @Email)",
connection);
command.Parameters.AddWithValue("@FirstName", firstName);
command.Parameters.AddWithValue("@LastName", lastName);
command.Parameters.AddWithValue("@Email", email);
int rowsAffected = command.ExecuteNonQuery();
Here,
rowsAffected should always come back as
1 for a single-row
INSERT that succeeds; anything else signals that the statement didn't do what it was expected to.
ExecuteScalar, for a Single Value
Sometimes a query's entire purpose is to return exactly one value, a count, a total, a single name, and looping through a
SqlDataReader for that would be more machinery than the task needs.
ExecuteScalar runs the query and returns just its first column, first row, as an
object that needs casting to the actual type expected:
SqlCommand command = new("SELECT COUNT(*) FROM Inventory", connection);
int bookCount = (int)command.ExecuteScalar();
If the query returns no rows at all,
ExecuteScalar returns
null instead of throwing, which is worth guarding against before the cast runs.
CommandType.Text Versus CommandType.StoredProcedure
Every example so far has handed
SqlCommand a raw SQL statement as plain text, which is its default mode,
CommandType.Text. The other mode,
CommandType.StoredProcedure, tells
SqlCommand that the string it was given isn't SQL at all, it's the name of a stored procedure already saved in the database, and any parameters should be passed to that procedure rather than substituted into a SQL string.
Assuming a stored procedure named
usp_GetLowStockBooks already exists on the server, accepting a threshold and returning every book at or below it, calling it looks like this:
SqlCommand command = new("usp_GetLowStockBooks", connection)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@Threshold", 3);
using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// process each low-stock book
}
Notice this still calls
ExecuteReader, a stored procedure that returns rows is executed exactly the same way a raw
SELECT is, the only difference is
CommandType and what the command's text actually names. Writing the procedure itself, the
CREATE PROCEDURE statement that defines
usp_GetLowStockBooks on the server, is a database-engine topic this course hasn't covered yet; what matters here is how a command object calls a procedure that already exists, not how that procedure gets written.
A stored procedure that only changes data, rather than returning any, is called the same way as the
INSERT example above, just with
CommandType.StoredProcedure set and
ExecuteNonQuery called instead of
ExecuteReader.
CommandType and the choice of execute method are two separate decisions: one says what kind of text the command holds, the other says what kind of result to expect back.
Testing Each Statement in Azure Data Studio First
Every statement this lesson wired into a SqlCommand is worth running on its own in Azure Data Studio before it goes anywhere near C# code, the same habit this course has followed since Module 3. An UPDATE or DELETE statement is harder to inspect once it's buried inside a SqlCommand's text, a typo in a column name produces a SqlException at runtime rather than a compiler error, since C# has no way to check a SQL string's contents against the actual database schema. Confirming a statement's behavior directly against VirtualBookShelf, including checking how many rows it actually affects, catches that kind of mistake in the tool built for finding it, rather than after it's wrapped in application code.
This matters especially for UPDATE and DELETE, where running a statement with the wrong WHERE clause by accident changes real data rather than simply returning the wrong rows. Testing first in Azure Data Studio, where the affected row count is visible immediately after running a statement, is a cheap way to catch a dangerously broad WHERE clause before it ever runs from a Razor Page handling a real visitor's request.
Summary: Command Objects, Legacy and Modern
| Component | Legacy Equivalent | Modern Replacement |
| Command Object | Visual InterDev / classic ADO Command | SqlCommand (ADO.NET) |
| Result Set | Recordset | SqlDataReader, or later, EF Core's DbSet<T> |
| Stored Procedure Execution | Classic ADO Command.Execute | SqlCommand with CommandType.StoredProcedure |
| Data-Changing Statements | Classic ADO Command.Execute | SqlCommand.ExecuteNonQuery |
| Data Connection | OLE DB / DSN | Connection string in appsettings.json |
ADO.NET's
SqlCommand is this course's answer for every row in this table. Entity Framework Core, introduced later in this course, wraps the same ideas in a higher-level API, generating its own
SqlCommand calls underneath, including a
FromSqlRaw method for running an existing stored procedure much the way shown above. Nothing in this lesson needs EF Core yet; it's mentioned here only so the terms line up once this course reaches it.
Disposing Commands Alongside Connections
Every example in this lesson created a SqlCommand without wrapping it in its own using block, only SqlConnection and SqlDataReader were wrapped that way. That's a deliberate, common pattern rather than an oversight: SqlCommand does implement IDisposable, the same interface covered in Lesson 1, but it holds no resource of its own beyond a reference to its connection. Disposing the connection it belongs to is enough to release everything that actually matters, which is why most ADO.NET code in practice wraps SqlConnection and SqlDataReader explicitly and leaves SqlCommand to be cleaned up when the method it lives in finishes and nothing more holds a reference to it.
A command built for a long-lived operation, reused many times before its connection closes, is the one case worth wrapping in its own using block regardless; nothing in this lesson's examples needed that, since each command here runs once and is done.
Frequently Asked Questions
What does the stored procedure behind the usp_GetLowStockBooks example actually look like? Something like this, defined once on the server rather than written out in application code:
CREATE PROCEDURE usp_GetLowStockBooks
@Threshold int
AS
BEGIN
SELECT BookID, Title, QuantityOnHand FROM Inventory WHERE QuantityOnHand < @Threshold;
END
Writing and modifying procedures like this is a database-engine skill, not something this lesson's command-object focus covers; what this lesson teaches is how a
SqlCommand calls a procedure once it exists.
Why does ExecuteNonQuery matter when Module 3 already covered INSERT, UPDATE, and DELETE in Azure Data Studio? Module 3 taught the SQL itself, typed directly into a query tool and run by hand.
ExecuteNonQuery is how that exact same SQL gets run automatically, from inside a Razor Page, as part of a visitor's request, say, clicking a button to mark a book as sold. The statement doesn't change; what changes is who's running it and when.
Does ExecuteScalar work with a query that returns multiple columns or rows? It runs, but only the first column of the first row is actually returned; everything else the query would have produced is simply discarded. If more than one value is genuinely needed,
ExecuteReader is the right method, even for a query that happens to return just one row.
Is command.Parameters.AddWithValue the only way to add a parameter? No, it's the most concise.
Parameters.Add accepts an explicit
SqlDbType as well, which avoids a rare class of type-inference mistake
AddWithValue can make with certain values. For this course's purposes,
AddWithValue is accurate and far more common in everyday code.
Can one SqlCommand object be reused for more than one statement? Its
CommandText property can be changed and it can be executed again, but it's just as common, and often clearer, to create a new
SqlCommand for each distinct statement, the pattern every example in this lesson and the previous one has followed.
What happens if ExecuteNonQuery is called on a SELECT statement by mistake? It runs without throwing an error, but the rows it would have returned are simply thrown away;
ExecuteNonQuery only ever reports a row count, never the data itself. Calling the wrong execute method doesn't fail loudly, which is exactly why matching the method to the statement, covered throughout this lesson, matters.
Does parameterizing a command with AddWithValue fully prevent SQL injection? Yes, for the value it protects. A parameter's value is never combined with the SQL text the way string concatenation would combine it; it's sent to SQL Server separately and can never be interpreted as part of the statement itself, the same protection Module 3 already established for raw parameterized queries.
Module 3's INSERT lesson used an OUTPUT clause to capture a generated CustomerID. Can that be retrieved with ExecuteNonQuery? No. An
OUTPUT clause makes a statement return a row, even though it's an
INSERT, and
ExecuteNonQuery only ever returns a row count, never any actual data; using it on a statement with
OUTPUT inserted.CustomerID would silently discard the generated key.
ExecuteScalar is the right method for that case instead, since a single
OUTPUT value is exactly the one value
ExecuteScalar is built to return.
Do parameter names have to start with @? For SQL Server, yes;
@ is how T-SQL marks a parameter placeholder, in both a plain
SqlCommand and a stored procedure call. The name after the
@ just has to match whatever name the SQL text or the stored procedure's own definition expects,
@BookID,
@FirstName, or
@Threshold in this lesson's examples.
What if a stored procedure needs to return more than one result set? SqlDataReader has a
NextResult method for exactly that, moving the reader from the first result set a procedure returns to the next one. None of this lesson's procedures do that, a single result set is the common case, but it's worth knowing
NextResult exists rather than assuming a reader is limited to one result set per call.
Does a SqlCommand need to be recreated for every single call, even inside a loop? Not necessarily. A command's
Parameters values can be changed and
ExecuteNonQuery or
ExecuteReader called again on the same object, which avoids rebuilding the command's text repeatedly inside something like a loop that inserts several rows in sequence. This lesson built a fresh
SqlCommand for each example for clarity, not because reuse is wrong.
What's Ahead
With SqlCommand now covered from more than one angle, retrieving rows, changing rows, and calling an existing stored procedure, this module's later lessons build on these same objects rather than introducing new ones from scratch. Rendering results with Tag Helpers instead of a plain @foreach loop, paging through a result set larger than VirtualBookShelf's current catalog, and handling the errors that come up when a database call doesn't go as expected, a dropped connection, a timeout, a constraint violation, all start from the same SqlConnection and SqlCommand foundation this lesson and the one before it just built. Nothing about retrieving or changing data changes going forward; what changes is how gracefully an application handles the cases where something doesn't go as planned, and how much of the resulting work gets handed off to a visitor versus handled automatically.
