Published / updated29 Aug 2026

Commands and Execution Methods

Before you start

You need: connections (Article 01).

Time: about 45 minutes, plus the practice.

Learning objective

Choose the right execution method for any statement, and interpret what each one returns — including when it returns nothing.

Topics

SqlCommand and its properties
CommandType: text versus stored procedure
ExecuteNonQuery and the affected row count
ExecuteScalar and its two kinds of empty
ExecuteReader and CommandBehavior
CommandTimeout
Cancellation
Choosing the right method

SqlCommand

using (SqlConnection connection = new SqlConnection(_connectionString))
using (SqlCommand command = new SqlCommand())
{
    command.Connection = connection;
    command.CommandText = "SELECT Name FROM dbo.Student WHERE Id = @Id";
    command.CommandType = CommandType.Text;
    command.CommandTimeout = 30;

    command.Parameters.Add("@Id", SqlDbType.Int).Value = studentId;

    connection.Open();
    // execute
}

Usually written more compactly:

using (SqlCommand command = new SqlCommand(sql, connection))

Property	Purpose
`Connection`	The connection to run on
`CommandText`	SQL text, or a procedure name
`CommandType`	`Text` (default) or `StoredProcedure`
`CommandTimeout`	Seconds to wait for execution (default 30)
`Parameters`	The parameter collection
`Transaction`	Required when inside a transaction

CommandType

// Text — the default
command.CommandText = "SELECT Name FROM dbo.Student WHERE Id = @Id";
command.CommandType = CommandType.Text;

// Stored procedure — the name only, no EXEC
command.CommandText = "dbo.usp_GetStudentsByClass";
command.CommandType = CommandType.StoredProcedure;

Forgetting CommandType.StoredProcedure is a top-three ADO.NET error. The provider sends the procedure name as a literal SQL statement, and SQL Server responds:

Incorrect syntax near 'usp_GetStudentsByClass'.

That message points at the SQL, so people look for a typo in a query that does not exist.

EXEC dbo.usp_GetStudentsByClass @SchoolId, @ClassName as CommandType.Text also works, but it is more to get wrong and gains nothing.

ExecuteNonQuery

For INSERT, UPDATE, DELETE and DDL. Returns the number of rows affected.

public int DeactivateStudent(int schoolId, Guid publicId)
{
    const string sql = @"
        UPDATE dbo.Student
        SET    Status = 1
        WHERE  SchoolId = @SchoolId AND PublicId = @PublicId;";

    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
        command.Parameters.Add("@PublicId", SqlDbType.UniqueIdentifier).Value = publicId;

        connection.Open();

        int affected = command.ExecuteNonQuery();

        if (affected == 0)
        {
            throw new InvalidOperationException(
                "No student was updated. The record may have been removed.");
        }

        return affected;
    }
}

Check the return value. An UPDATE matching nothing is not an error — no exception is thrown, and the method returns successfully having done nothing. The caller shows "Saved" and the user's change is gone.

Zero rows affected usually means one of three things: the record was deleted by someone else, the tenant filter excluded it, or the key you passed is wrong.

SET NOCOUNT ON changes this

If the procedure you call starts with SET NOCOUNT ON, ExecuteNonQuery returns -1 instead of a row count. That is correct behaviour, and it is why row-count checks against a procedure need an explicit OUTPUT parameter instead:

CREATE OR ALTER PROCEDURE dbo.usp_DeactivateStudent
    @SchoolId INT, @PublicId UNIQUEIDENTIFIER, @Affected INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Student SET Status = 1
    WHERE  SchoolId = @SchoolId AND PublicId = @PublicId;

    SET @Affected = @@ROWCOUNT;
END;

ExecuteScalar

Returns the first column of the first row as object, discarding everything else.

public decimal GetOutstandingBalance(int schoolId, int feeAccountId)
{
    const string sql = @"
        SELECT fa.TotalFees - fa.DiscountAmount - fa.PaidAmount
        FROM   dbo.FeeAccount AS fa
        WHERE  fa.Id = @FeeAccountId AND fa.SchoolId = @SchoolId;";

    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
        command.Parameters.Add("@FeeAccountId", SqlDbType.Int).Value = feeAccountId;

        connection.Open();

        object? result = command.ExecuteScalar();

        if (result == null || result == DBNull.Value)
        {
            throw new InvalidOperationException("Fee account not found.");
        }

        return (decimal)result;
    }
}

The two kinds of empty

ExecuteScalar can return two different things that both look like "nothing", and they mean different things:

Return	Meaning
`null`	No rows matched
`DBNull.Value`	A row matched, and the value in it is `NULL`

object? result = command.ExecuteScalar();

if (result == null)
{
    // the fee account does not exist
}
else if (result == DBNull.Value)
{
    // the account exists but the value is NULL
}
else
{
    decimal balance = (decimal)result;
}

Casting either directly throws NullReferenceException or InvalidCastException. Code that does (decimal)command.ExecuteScalar() works until the first missing record.

For an aggregate that may cover no rows, handle it in SQL instead:

SELECT ISNULL(SUM(p.Amount), 0) FROM dbo.FeePayment AS p WHERE p.FeeAccountId = @FeeAccountId;

Now the result is always a number, and the C# is simpler.

Retrieving a new identity

const string sql = @"
    INSERT INTO dbo.Student (PublicId, SchoolId, Name, RollNumber, ClassName, Section,
                             DateOfBirth, ParentName, ParentPhone, Status)
    VALUES (@PublicId, @SchoolId, @Name, @RollNumber, @ClassName, @Section,
            @DateOfBirth, @ParentName, @ParentPhone, 0);

    SELECT CAST(SCOPE_IDENTITY() AS INT);";

int newId = (int)command.ExecuteScalar()!;

SCOPE_IDENTITY() returns NUMERIC(38,0), so the CAST avoids an InvalidCastException on the C# side. Use SCOPE_IDENTITY() rather than @@IDENTITY, which returns the last identity in the session — including one created by a trigger on a different table.

ExecuteReader

For anything returning rows.

public List<Student> GetStudentsByClass(int schoolId, string className)
{
    const string sql = @"
        SELECT   s.Id, s.PublicId, s.RollNumber, s.Name, s.ClassName, s.Section
        FROM     dbo.Student AS s
        WHERE    s.SchoolId = @SchoolId AND s.ClassName = @ClassName AND s.Status <> 1
        ORDER BY s.Section, s.Name;";

    List<Student> students = new List<Student>();

    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
        command.Parameters.Add("@ClassName", SqlDbType.NVarChar, 10).Value = className;

        connection.Open();

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                students.Add(MapStudent(reader));
            }
        }
    }

    return students;
}

Read() advances one row and returns false when there are no more. The reader starts before the first row, so Read() must be called before accessing anything.

For a single expected row:

using (SqlDataReader reader = command.ExecuteReader())
{
    if (!reader.Read())
    {
        return null;
    }

    return MapStudent(reader);
}

CommandBehavior

using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))

Behavior	Effect
`Default`	Normal
`SingleRow`	Hint that one row is expected — can optimise
`SingleResult`	One result set only
`SequentialAccess`	Stream columns in order — for large binary data
`CloseConnection`	Closes the connection when the reader is closed
`KeyInfo` / `SchemaOnly`	Metadata without data

CloseConnection is the one worth knowing. It lets a method return a reader whose connection closes when the caller disposes it:

// The caller owns the reader; the connection closes with it
SqlConnection connection = new SqlConnection(_connectionString);
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();

return command.ExecuteReader(CommandBehavior.CloseConnection);

Use this sparingly. It moves resource ownership to the caller, and a caller who forgets to dispose leaks a connection. Returning a materialised List<T> is safer and almost always fast enough.

Multiple result sets

const string sql = @"
    SELECT COUNT(*) FROM dbo.Student WHERE SchoolId = @SchoolId AND Status <> 1;

    SELECT Id, Name, RollNumber FROM dbo.Student
    WHERE SchoolId = @SchoolId AND Status <> 1
    ORDER BY Name;";

using (SqlDataReader reader = command.ExecuteReader())
{
    reader.Read();
    int totalCount = reader.GetInt32(0);

    reader.NextResult();          // move to the second result set

    while (reader.Read())
    {
        students.Add(MapStudent(reader));
    }
}

NextResult() advances to the next result set. One round trip instead of two — the pattern behind paged lists that need both a page of rows and a total count.

CommandTimeout

command.CommandTimeout = 60;   // seconds; 0 means no limit

This is not the connection timeout. Connect Timeout in the connection string governs establishing the connection; CommandTimeout governs how long a statement may run.

A timeout surfaces as SqlException with Number == -2:

catch (SqlException ex) when (ex.Number == -2)
{
    _logger.LogWarning("Query timed out after {Timeout}s", command.CommandTimeout);
    throw new TimeoutException("The report took too long. Narrow the date range.", ex);
}

Do not set CommandTimeout = 0 to make a timeout go away. A query that needs unlimited time needs an index, not a longer leash — and an unbounded command can hold locks indefinitely.

Raising it to 120 for a genuinely large report is reasonable, and should carry a comment saying which report and why.

Cancellation

public async Task<List<Student>> GetStudentsAsync(
    int schoolId, CancellationToken cancellationToken)
{
    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;

        await connection.OpenAsync(cancellationToken);

        using (SqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken))
        {
            List<Student> students = new List<Student>();

            while (await reader.ReadAsync(cancellationToken))
            {
                students.Add(MapStudent(reader));
            }

            return students;
        }
    }
}

Passing the token means an abandoned web request stops its database work instead of running to completion for nobody. On a busy API this measurably reduces load.

Choosing the method

Statement	Method	Returns
`INSERT` / `UPDATE` / `DELETE`	`ExecuteNonQuery`	Rows affected, or -1 with `SET NOCOUNT ON`
`SELECT` one value	`ExecuteScalar`	`object?` — check for `null` and `DBNull.Value`
`SELECT` rows	`ExecuteReader`	Forward-only stream
`INSERT` returning an id	`ExecuteScalar` with `SCOPE_IDENTITY()`	The new key
Procedure with output parameters	`ExecuteNonQuery`	Read parameters after any reader is closed
Several result sets	`ExecuteReader` + `NextResult()`	Each in turn

Using the wrong one is usually harmless but wasteful — ExecuteReader for a single value builds a whole reader. The exception is ExecuteScalar on a multi-row query, which silently discards everything except the first value, producing a plausible but wrong answer.

Errors you will hit

Message	Cause	Fix
`ExecuteScalar` returns `null`	No rows, or the first column is `NULL`	Check for `null` before casting
`Specified cast is not valid`	Cast the wrong type from `ExecuteScalar`	It returns `object` — check the SQL type
`Invalid attempt to call Read when reader is closed`	Reader used after its `using` ended	Read inside the block
`There is already an open DataReader associated with this Command`	Two readers on one connection	Finish one first, or enable MARS
`ExecuteNonQuery` returns `-1`	The statement was not a DML statement, or `SET NOCOUNT ON`	Expected for DDL
`CommandTimeout` expired	Slow query or blocking	Tune the query; do not just raise the timeout

ExecuteNonQuery returns rows affected — and 0 is information. An UPDATE that matched nothing is not an error, but it usually means the WHERE did not do what you thought.

Common mistakes

Omitting CommandType.StoredProcedure
Ignoring the ExecuteNonQuery return value
Being surprised by -1 from a procedure with SET NOCOUNT ON
Casting ExecuteScalar directly without checking null and DBNull.Value
Not distinguishing "no rows" from "value is NULL"
@@IDENTITY instead of SCOPE_IDENTITY()
Not casting SCOPE_IDENTITY() to INT
ExecuteScalar on a query that returns many rows
Accessing a reader before calling Read()
CommandTimeout = 0 instead of fixing the query
Confusing CommandTimeout with Connect Timeout
Reading output parameters before the reader is closed
Not passing a CancellationToken in async web code

Practice

Write GetStudentsByClass with ExecuteReader, returning a List<Student>.
Write DeactivateStudent with ExecuteNonQuery. Call it with a PublicId that does not exist and confirm it returns 0 without throwing.
Add the zero-rows check and confirm the caller now learns about it.
Write GetOutstandingBalance with ExecuteScalar. Call it for a valid account, a non-existent account, and an account where the computed value is NULL. Record what each returns.
Rewrite the same query with ISNULL(..., 0) and compare how much simpler the C# becomes.
Insert a student and return the new id with SCOPE_IDENTITY(). Remove the CAST and record the exception.
Call a stored procedure without setting CommandType. Record the exact error message.
Write a paged query returning a count and a page of rows in one command, using NextResult().
Set CommandTimeout = 1 on a query with WAITFOR DELAY '00:00:05'. Catch SqlException and confirm Number == -2.
Add a CancellationToken to an async version, cancel it after 100 ms, and confirm the operation stops.

Exercise 4 is the one to internalise — null versus DBNull.Value is a distinction that only appears in production data.

You can now

Choose between ExecuteNonQuery, ExecuteScalar and ExecuteReader
Read the rows-affected count and act on 0
Set CommandType for a stored procedure
Handle a null from ExecuteScalar
Keep readers inside their using block

Review questions

What does ExecuteNonQuery return when the procedure sets NOCOUNT ON?
What is the difference between ExecuteScalar returning null and returning DBNull.Value?
Why cast SCOPE_IDENTITY() to INT in the SQL?
What is the difference between CommandTimeout and Connect Timeout?

Next: Parameters and injection

Before you start​

Learning objective​

Topics​

SqlCommand​

CommandType​

ExecuteNonQuery​

SET NOCOUNT ON changes this​

ExecuteScalar​

The two kinds of empty​

Retrieving a new identity​

ExecuteReader​

CommandBehavior​

Multiple result sets​

CommandTimeout​

Cancellation​

Choosing the method​

Errors you will hit​

Common mistakes​

Practice​

You can now​

Review questions​