# 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&lt;int>.

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

By Gregor Dzierzon

## 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&lt;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.)

## Do it: count the rows a price change touches

`Execute` hands back how many rows its SQL changed, and 0 is an answer, not an error. Practice that rule in plain C#. The practice box can't reach SQL Server, so here the `Products` table is a `List<Product>`, and you write what this `UPDATE` does, and the count `Execute` would give back for it:

```sql
UPDATE Products SET UnitPrice = UnitPrice + @Amount WHERE ProductId = @ProductId;
```

The method gets three values:

- `products`: the table's rows, a `List<Product>`. Each `Product` has a `ProductId` (`int`), a `ProductName` (`string`) and a `UnitPrice` (`decimal`). The class is ready in the box.
- `productId`: the id of the product to change, an `int`.
- `amount`: how much to add to its price, a `decimal`.

For example, Hot Chocolate goes up by a dollar:

```csharp
List<Product> products = new List<Product>
{
    new Product { ProductId = 1, ProductName = "Chai", UnitPrice = 18.00m },
    new Product { ProductId = 78, ProductName = "Hot Chocolate", UnitPrice = 5.99m }
};
int rows = RaisePrice(products, 78, 1.00m);
```

| Product | Price before | Price after |
|---|---|---|
| Chai (1) | 18.00 | 18.00: not id 78, so it's left alone |
| Hot Chocolate (78) | 5.99 | 5.99 + 1.00 = 6.99 |

One product changed, so `rows` is `1`.

**Your job:** write the method `int RaisePrice(List<Product> products, int productId, decimal amount)`.

- Add `amount` to the `UnitPrice` of the product whose `ProductId` is `productId`.
- Leave every other product as it is.
- Return how many products changed.
- When no product has that id, change nothing and return `0`, the way an `UPDATE` for a missing id changes 0 rows without an error.

Practiced live on the lesson page: write the C# right there and check it.

## 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
