Explain the ExecuteNonQuery, ExecuteScalar and ExecuteReader methods of the Command object.

In ADO.NET, the Command object (such as SqlCommand) provides several execution methods depending on what type of result you expect from the database.

The three most commonly used methods are:

1. ExecuteNonQuery()
2. ExecuteScalar()
3. ExecuteReader()

1. ExecuteNonQuery()

Purpose

Used when you want to execute a SQL statement that does NOT return result rows.

Common Use Cases

• INSERT
• UPDATE
• DELETE
• CREATE, ALTER, DROP
• Stored procedures that do not return result sets

Return Value

• Returns an int → number of rows affected
• Returns -1 for some statements like table creation

Example

using (SqlCommand cmd = new SqlCommand(
    "UPDATE Users SET IsActive = 1 WHERE Role = 'Admin'", conn))
{
    int rowsAffected = cmd.ExecuteNonQuery();
}

When to Use

• When you only care whether the operation succeeded
• When no data needs to be read back

2. ExecuteScalar()

Purpose

Used when you want to retrieve a single value (one row, one column) from the database.

Common Use Cases

• SELECT COUNT(*)
• SELECT MAX(Id)
• SELECT @@IDENTITY
• Getting a single configuration value

Return Value

• Returns an object
• Returns the value of the first column of the first row
• Returns null if result set is empty

Example

using (SqlCommand cmd = new SqlCommand(
    "SELECT COUNT(*) FROM Users", conn))
{
    int userCount = (int)cmd.ExecuteScalar();
}

Important Notes

• Extra rows or columns are ignored
• You must cast the result to the expected type

3. ExecuteReader()

Purpose

Used when you want to read multiple rows and columns from the database.

Common Use Cases

• SELECT queries returning multiple records
• Reading large datasets efficiently

Return Value

• Returns a SqlDataReader
• Provides forward-only, read-only access to data

Example

using (SqlCommand cmd = new SqlCommand(
    "SELECT Id, Name FROM Users", conn))
using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        int id = reader.GetInt32(0);
        string name = reader.GetString(1);
    }
}

When to Use

• When performance matters
• When working with large result sets

Comparison Table

Key Differences at a Glance

Interview Tip (Important)

Rule of thumb:

• Use ExecuteNonQuery → no data expected
• Use ExecuteScalar → exactly one value expected
• Use ExecuteReader → multiple rows expected