SqlCommand

In simple words#

Learning objectives#

• Create commands with SQL text or procedure names
• Choose the right Execute method
• Set CommandTimeout
• Reuse a command with different parameter values

Explanation#

CommandText holds the SQL (or a stored procedure name); Connection is where it runs; CommandType is Text (default) or StoredProcedure.

ExecuteNonQuery for INSERT, UPDATE, DELETE and DDL. ExecuteScalar for a single value. ExecuteReader for rows. Each has an Async version.

CommandTimeout (default 30 seconds) limits how long a command waits for SQL Server; 0 means wait forever and should be avoided.

Commands can be reused: change parameter values and execute again — useful in loops.

Code example: The three Execute methods#

1await using var conn = new SqlConnection(cs);
2await conn.OpenAsync();
3

4// 1. ExecuteNonQuery → rows affected
5await using var update = new SqlCommand("UPDATE Products SET Price = Price * 1.05 WHERE CategoryId = @cat", conn);
6update.Parameters.Add("@cat", SqlDbType.Int).Value = 1;
7int changed = await update.ExecuteNonQueryAsync();
8

9// 2. ExecuteScalar → one value
10await using var count = new SqlCommand("SELECT COUNT(*) FROM Products", conn);
11int total = (int)(await count.ExecuteScalarAsync())!;
12

13// 3. ExecuteReader → rows
14await using var list = new SqlCommand("SELECT TOP (2) Name FROM Products ORDER BY Price DESC", conn) { CommandTimeout = 10 };
15await using var r = await list.ExecuteReaderAsync();
16while (await r.ReadAsync()) Console.WriteLine(r.GetString(0));
17

18Console.WriteLine(
quot;{changed} prices updated, {total} products in total");

Laptop
Keyboard
2 prices updated, 3 products in total

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

1. The UPDATE returns how many rows it changed.
2. COUNT(*) returns an int boxed as object, so it is cast.
3. The reader streams the two most expensive product names; CommandTimeout is set to 10 seconds.

Real-world use case#

Common mistakes#

Best practices#

• Match the Execute method to the result
• Use async methods in web apps
• Always use parameters for values
• Set timeouts deliberately for long reports

Remember this#

Practice exercise#

1await using var cmd = new SqlCommand("UPDATE Products SET Stock = 0 WHERE Id = @id", conn);
2var id = cmd.Parameters.Add("@id", SqlDbType.Int);
3foreach (var value in new[] { 4, 5, 6 })
4{
5    id.Value = value;
6    await cmd.ExecuteNonQueryAsync();
7}

Mini quiz#

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

Related topics#

My notes#

Saved in this browser.