SqlCommand & Query Execution

Level: Beginner

ℹ️ Where This Fits

SqlCommand is the tool that executes SQL against the database. Understanding different execution methods (ExecuteReader, ExecuteScalar, ExecuteNonQuery) lets you choose the right method for each scenario.

ℹ️ What You'll Learn

• SqlCommand purpose: Execute SQL query or stored procedure against database
• Creating command: var cmd = conn.CreateCommand() (inherits connection)
• CommandText property: SQL query or stored procedure name (cmd.CommandText = "SELECT * FROM Students")
• CommandType.Text: Execute raw SQL query (default)
• CommandType.StoredProcedure: Execute stored procedure from database
• CommandType.TableDirect: Direct table access (rarely used)
• ExecuteReader(): Returns SqlDataReader for SELECT queries (multiple rows, forward-only)
• ExecuteReaderAsync(): Async version of ExecuteReader (doesn't block thread)
• ExecuteScalar(): Returns single value from first column, first row (COUNT, SUM, MAX, ID)
• ExecuteScalarAsync(): Async version of ExecuteScalar
• ExecuteNonQuery(): Execute INSERT/UPDATE/DELETE, returns rows affected
• ExecuteNonQueryAsync(): Async version of ExecuteNonQuery (preferred in modern apps)
• CommandTimeout: Timeout in seconds (default 30), set before execution
• Prepared statements: cmd.Prepare() pre-compiles statement for repeated use
• Multiple result sets: One SqlCommand can execute multiple SELECT statements
• School Management examples: Get all students, count by class, insert new student, update fees
• Query results: SELECT returns data, INSERT/UPDATE/DELETE return row count only
• Common mistakes: Wrong CommandType for stored procs, not checking ExecuteNonQuery return value, long timeouts

ExecuteReader - SELECT with Rows

Use when query returns multiple rows:

using (var conn = new SqlConnection(connString))
{
    await conn.OpenAsync();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT Id, Name, ClassName FROM Students WHERE Status = @status";
        cmd.Parameters.AddWithValue("@status", "Active");

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            var students = new List<Student>();
            while (await reader.ReadAsync())
            {
                students.Add(new Student
                {
                    Id = (int)reader["Id"],
                    Name = (string)reader["Name"],
                    ClassName = (string)reader["ClassName"]
                });
            }
            return students;
        }
    }
}

ExecuteScalar - Single Value

Use when query returns one value (count, sum, ID):

using (var conn = new SqlConnection(connString))
{
    await conn.OpenAsync();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT COUNT(*) FROM Students WHERE ClassName = @className";
        cmd.Parameters.AddWithValue("@className", "10-A");

        var count = (int)await cmd.ExecuteScalarAsync();
        return count;  // Returns 45 students in class 10-A
    }
}

ExecuteNonQuery - INSERT/UPDATE/DELETE

Use for INSERT/UPDATE/DELETE (returns affected rows):

using (var conn = new SqlConnection(connString))
{
    await conn.OpenAsync();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "UPDATE Students SET Status = @status WHERE Id = @id";
        cmd.Parameters.AddWithValue("@id", 101);
        cmd.Parameters.AddWithValue("@status", "Graduated");

        var rowsAffected = await cmd.ExecuteNonQueryAsync();
        
        if (rowsAffected == 0)
        {
            throw new Exception("Student not found");  // ID doesn't exist
        }
    }
}

CommandType.StoredProcedure

Execute stored procedure:

using (var conn = new SqlConnection(connString))
{
    await conn.OpenAsync();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_CreateStudent";
        cmd.Parameters.AddWithValue("@name", "Ravi Kumar");
        cmd.Parameters.AddWithValue("@className", "10-A");

        var newId = (int)await cmd.ExecuteScalarAsync();
        return newId;
    }
}

CommandTimeout

Set timeout for long-running queries:

using (var cmd = conn.CreateCommand())
{
    cmd.CommandTimeout = 60;  // Wait 60 seconds (default 30)
    cmd.CommandText = "SELECT ... FROM LargeDataset";
    var result = await cmd.ExecuteReaderAsync();
}

Best Practices

✓ Use parameters always (prevents SQL injection) ✓ Choose correct execution method (Reader/Scalar/NonQuery) ✓ Use async methods (.ExecuteReaderAsync) ✓ Check ExecuteNonQuery return value for validation ✓ Set appropriate CommandTimeout for expected duration

💡 ExecuteNonQuery Return Value

Returns number of rows affected. Use to validate:

• INSERT: Returns 1 if successful
• UPDATE: Returns 0 if ID not found (validation!)
• DELETE: Returns 0 if ID not found

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on SqlCommand & Query Execution. Try these prompts:

• "When should you use ExecuteReader vs ExecuteScalar vs ExecuteNonQuery?"
• "How do you execute a stored procedure in ADO.NET?"
• "Why is checking ExecuteNonQuery's return value important?"
• "What's the difference between CommandType.Text and CommandType.StoredProcedure?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> 04. Parameters & SQL Injection Prevention