ExecuteNonQuery

In simple words#

Learning objectives#

• Insert, update and delete with ExecuteNonQuery
• Use the rows-affected result
• Get a new identity value
• Understand when -1 is returned

Explanation#

For INSERT, UPDATE and DELETE, ExecuteNonQuery returns the number of rows affected. 0 often means “not found”.

For other statements (CREATE TABLE, or when SET NOCOUNT ON is in effect for the whole batch), it returns -1.

To get the new Id of an inserted row, use OUTPUT INSERTED.Id with ExecuteScalar, or SCOPE_IDENTITY().

Code example: Create, update and delete a product#

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

4// INSERT and get the new Id
5await using var insert = new SqlCommand(
6    "INSERT INTO Products (Name, Price, Stock) OUTPUT INSERTED.Id VALUES (@name, @price, @stock)", conn);
7insert.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = "Webcam";
8insert.Parameters.Add("@price", SqlDbType.Decimal).Value = 3200m;
9insert.Parameters.Add("@stock", SqlDbType.Int).Value = 15;
10int newId = (int)(await insert.ExecuteScalarAsync())!;
11

12// UPDATE → check rows affected
13await using var update = new SqlCommand("UPDATE Products SET Stock = Stock - 1 WHERE Id = @id AND Stock > 0", conn);
14update.Parameters.Add("@id", SqlDbType.Int).Value = newId;
15int updated = await update.ExecuteNonQueryAsync();
16

17// DELETE
18await using var delete = new SqlCommand("DELETE FROM Products WHERE Id = @id", conn);
19delete.Parameters.Add("@id", SqlDbType.Int).Value = 999;
20int deleted = await delete.ExecuteNonQueryAsync();
21

22Console.WriteLine(
quot;New Id: {newId}, updated: {updated}, deleted: {deleted}");

New Id: 4, updated: 1, deleted: 0

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. OUTPUT INSERTED.Id returns the generated identity, so ExecuteScalar is used for the insert.
2. The UPDATE only succeeds when stock is available; 1 row affected confirms it.
3. Deleting Id 999 affects 0 rows — the product does not exist, so the API can return 404.

Real-world use case#

Common mistakes#

Best practices#

• Check rows affected for updates and deletes
• Use OUTPUT INSERTED for new keys
• Put conditions in the WHERE clause to avoid race conditions
• Use transactions for multi-statement changes

Remember this#

Practice exercise#

1static async Task<bool> UpdatePriceAsync(SqlConnection conn, int id, decimal price)
2{
3    await using var cmd = new SqlCommand("UPDATE Products SET Price = @price WHERE Id = @id", conn);
4    cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = price;
5    cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
6    return await cmd.ExecuteNonQueryAsync() == 1;
7}

Mini quiz#

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

Related topics#

My notes#

Saved in this browser.