# Execute: Insert, Update and Delete

Execute runs INSERT, UPDATE and DELETE and returns how many rows changed. To get a new row's id back, query it with QuerySingle<int>.

Journeyman · C# · T-SQL · Updated November 3, 2026

## After this lesson you can

- Change data with Execute, and use the number of rows it returns
- Insert a row and get its new id back with QuerySingle<int> and SCOPE_IDENTITY

## Key takeaways

- `Execute` runs `INSERT`, `UPDATE` and `DELETE` and returns how many rows changed.
- An `UPDATE` or `DELETE` for an id that doesn't exist changes 0 rows without an error, so check the count, as in `return rows == 1;`.
- To get a new row's id, follow the `INSERT` with `SELECT CAST(SCOPE_IDENTITY() AS int);` and read it with `QuerySingle<int>`.
- Pass a list of parameter objects, and `Execute` runs the SQL once per item and returns the total rows changed.

Reading uses `Query`. Changing data uses **`Execute`**: it runs the SQL and returns **how many rows** it changed, just like ADO.NET's `ExecuteNonQuery`.

## Insert, update, delete

This program adds a product, raises its price, deletes it, and prints how many rows each step changed. It cleans up after itself, so Northwind ends as it started.

```csharp
using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
using SqlConnection connection = new SqlConnection(connectionString);

int newId = connection.QuerySingle<int>("""
    INSERT INTO Products (ProductName, CategoryId, UnitPrice)
    VALUES (@ProductName, @CategoryId, @UnitPrice);
    SELECT CAST(SCOPE_IDENTITY() AS int);
    """, new { ProductName = "Hot Chocolate", CategoryId = 1, UnitPrice = 5.99m });
Console.WriteLine($"Added a product: {newId > 77}");

int updated = connection.Execute(
    "UPDATE Products SET UnitPrice = UnitPrice + 1 WHERE ProductId = @ProductId;", new { ProductId = newId });
Console.WriteLine($"Rows updated: {updated}");

decimal price = connection.ExecuteScalar<decimal>(
    "SELECT UnitPrice FROM Products WHERE ProductId = @ProductId;", new { ProductId = newId });
Console.WriteLine($"New price: {price:0.00}");

int deleted = connection.Execute(
    "DELETE FROM Products WHERE ProductId = @ProductId;", new { ProductId = newId });
Console.WriteLine($"Rows deleted: {deleted}");
```

```text
Added a product: True
Rows updated: 1
New price: 6.99
Rows deleted: 1
```

## Getting the new id

An `INSERT` doesn't return anything, but the new row's `IDENTITY` value is often needed: to show the new product's page, or to add rows that refer to it. Follow the `INSERT` with `SELECT CAST(SCOPE_IDENTITY() AS int);` in the same SQL, and read that one value with **`QuerySingle<int>`**, as above.

(`SCOPE_IDENTITY()` is the id just made by your own statement. The `CAST` makes it an `int`, the type of the column.)

## Using the count

`Execute`'s return value tells you whether anything happened. An `UPDATE` or `DELETE` for an id that doesn't exist changes **0** rows, without an error. A DAO can turn that into a `bool`:

```csharp
public bool Delete(int productId)
{
    using SqlConnection connection = new SqlConnection(connectionString);
    int rows = connection.Execute(
        "DELETE FROM Products WHERE ProductId = @ProductId;", new { ProductId = productId });
    return rows == 1;
}
```

The caller then knows whether the product existed: a web app can answer **404 Not Found** when it didn't.

## Many rows in one call

Pass a **list** of parameter objects, and `Execute` runs the SQL once per item, returning the total rows changed:

```csharp
using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
using SqlConnection connection = new SqlConnection(connectionString);

var newShippers = new[]
{
    new { CompanyName = "Rapid Couriers", Phone = "(503) 555-0199" },
    new { CompanyName = "Night Owl Freight", Phone = "(503) 555-0123" }
};

int added = connection.Execute(
    "INSERT INTO Shippers (CompanyName, Phone) VALUES (@CompanyName, @Phone);", newShippers);
Console.WriteLine($"Shippers added: {added}");

int removed = connection.Execute(
    "DELETE FROM Shippers WHERE CompanyName IN ('Rapid Couriers', 'Night Owl Freight');");
Console.WriteLine($"Shippers removed: {removed}");
```

```text
Shippers added: 2
Shippers removed: 2
```

(`var` here holds an array of anonymous objects, a type with no name you could write.)

## Check yourself

2 questions to try on the lesson page.

## So what

`Execute` for changes, its count to see what happened, and `QuerySingle<int>` with `SCOPE_IDENTITY()` for a new row's id. That's every write a DAO needs.

## Key terms

- Execute()
- SCOPE_IDENTITY

## Cheat sheets

- [Execute: Insert, Update and Delete](https://www.ask-codey.com/cheat-sheets/dapper-execute-insert-update-and-delete-cheat-sheet)

Source: https://www.ask-codey.com/lessons/dapper-execute-insert-update-and-delete