INSERT, UPDATE, DELETE with Dapper Execute: Affected Rows and the New Id

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

We insert three books by Stanislaw Lem with a single Execute call. As the parameter we pass not a single object but an array with three elements:

var newBooks = new[]
{
    new { AuthorId = 3, Title = "The Cyberiad", Price = 10.75m },
    new { AuthorId = 3, Title = "Fiasco", Price = 12.00m },
    new { AuthorId = 3, Title = "Eden", Price = 8.50m }
};

int affected = connection.Execute(
    "INSERT INTO books (author_id, title, price) VALUES (@AuthorId, @Title, @Price)",
    newBooks);

Console.WriteLine(affected);

Two questions. Which number is printed? And more importantly, how many INSERT commands does this call send to the database: a single three-row command, or three separate commands? The answer to the second question will decide whether you use this technique for large data loads.

The Bookstore Database

The setup is the same as in the first post: a .NET 10 console project, Dapper 2.1.89, Microsoft.Data.Sqlite 10.0.14 and BookstoreDb.cs, which rebuilds the database on every run:

using Dapper;
using Microsoft.Data.Sqlite;

public static class BookstoreDb
{
    public const string ConnectionString = "Data Source=bookstore.db";

    public static void Reset()
    {
        using var connection = new SqliteConnection(ConnectionString);
        connection.Execute("""
            DROP TABLE IF EXISTS order_items;
            DROP TABLE IF EXISTS orders;
            DROP TABLE IF EXISTS books;
            DROP TABLE IF EXISTS authors;

            CREATE TABLE authors (
                id      INTEGER PRIMARY KEY,
                name    TEXT NOT NULL,
                country TEXT
            );
            CREATE TABLE books (
                id             INTEGER PRIMARY KEY,
                author_id      INTEGER NOT NULL REFERENCES authors(id),
                title          TEXT NOT NULL,
                price          REAL NOT NULL,
                published_year INTEGER,
                stock          INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE orders (
                id             INTEGER PRIMARY KEY,
                customer_email TEXT NOT NULL,
                created_at     TEXT NOT NULL
            );
            CREATE TABLE order_items (
                id         INTEGER PRIMARY KEY,
                order_id   INTEGER NOT NULL REFERENCES orders(id),
                book_id    INTEGER NOT NULL REFERENCES books(id),
                quantity   INTEGER NOT NULL,
                unit_price REAL NOT NULL
            );

            INSERT INTO authors (id, name, country) VALUES
                (1, 'Ursula K. Le Guin', 'USA'),
                (2, 'Frank Herbert', 'USA'),
                (3, 'Stanislaw Lem', 'Poland'),
                (4, 'Douglas Adams', 'UK'),
                (5, 'Ted Chiang', 'USA');

            INSERT INTO books (id, author_id, title, price, published_year, stock) VALUES
                (1, 1, 'A Wizard of Earthsea', 12.50, 1968, 8),
                (2, 1, 'The Left Hand of Darkness', 14.00, 1969, 3),
                (3, 1, 'The Dispossessed', 15.25, 1974, 0),
                (4, 2, 'Dune', 18.90, 1965, 12),
                (5, 3, 'Solaris', 11.40, 1961, 5),
                (6, 4, 'The Hitchhiker''s Guide to the Galaxy', 9.99, 1979, 20),
                (7, 5, 'Exhalation', 13.20, NULL, 4);
            """);
    }
}

In this post we also use two small records:

public record NewBook(int AuthorId, string? Title, decimal Price);

public record CreatedOrder(long Id, string CreatedAt);

All snippets run in the order of the post on a single connection created after BookstoreDb.Reset();; the numbers depend on the data left by the previous step.

Execute and the Number of Affected Rows

For reading we used the Query family. For commands that return no result, that is INSERT, UPDATE and DELETE, there is Execute. Its return value is an int: the number of rows the command affected.

int inserted = connection.Execute(
    "INSERT INTO books (author_id, title, price, stock) VALUES (@AuthorId, @Title, @Price, @Stock)",
    new { AuthorId = 5, Title = "Stories of Your Life and Others", Price = 12.80m, Stock = 7 });

int updated = connection.Execute(
    "UPDATE books SET price = price * 1.10 WHERE author_id = @AuthorId",
    new { AuthorId = 1 });

int deleted = connection.Execute(
    "DELETE FROM books WHERE title = @Title",
    new { Title = "Eden" });

Console.WriteLine($"inserted {inserted}, updated {updated}, deleted {deleted}");

inserted 1, updated 3, deleted 1

Parameters work as in the previous post: the values are not embedded in the SQL but travel as separate parameters from the anonymous object. The UPDATE raised the price of Le Guin's three books by ten percent and returned 3. Under the hood Dapper calls ADO.NET's ExecuteNonQuery method and hands you the number it returns, so the meaning of that number depends on what the provider reports. For INSERT/UPDATE/DELETE it is usually the number of rows actually changed; but on SQL Server, for example, you get -1 if SET NOCOUNT ON is in effect.

Zero Rows Is Not an Error

The most overlooked property of Execute is that it does not throw when no rows are affected. Imagine making a sale: decrease the stock by one, but only if there is stock.

int sold = connection.Execute(
    "UPDATE books SET stock = stock - 1 WHERE id = @Id AND stock > 0",
    new { Id = 3 });

if (sold == 0)
{
    Console.WriteLine("Out of stock or no such book");
}

Out of stock or no such book

The Dispossessed has a stock of 0; the UPDATE matched no rows and Execute silently returned 0. The command succeeded, but the work was not done. That is why you check the return value wherever you "update the record" or "delete the record". Zero means "there is no such record" or "the condition no longer holds", and turning that into a 404, an "out of stock" message or a concurrency error is your job.

Putting the condition into the WHERE clause and checking the result through the row count is also a cheap protection against concurrency issues. Had you read the stock first and decreased it on the C# side, two requests could have sold the same last copy at the same time. A stock > 0 check inside a single UPDATE runs atomically on the database side.

The Id of a Newly Inserted Row

The most common need after an INSERT is getting back the id the database generated. On SQLite there are two ways. The modern one is RETURNING, supported since SQLite 3.35; the SQLite version that ships with Microsoft.Data.Sqlite 10.0.14 is 3.53.3. The classic one is calling last_insert_rowid() in the same command. In both cases a value comes back, so we use ExecuteScalar instead of Execute:

long asimovId = connection.ExecuteScalar<long>(
    "INSERT INTO authors (name, country) VALUES (@Name, @Country) RETURNING id",
    new { Name = "Isaac Asimov", Country = "USA" });

long clarkeId = connection.ExecuteScalar<long>(
    """
    INSERT INTO authors (name, country) VALUES (@Name, @Country);
    SELECT last_insert_rowid();
    """,
    new { Name = "Arthur C. Clarke", Country = "UK" });

Console.WriteLine($"{asimovId} | {clarkeId}");

6 | 7

Calling last_insert_rowid() in the same command as the INSERT matters. The function is tied to the connection; if you ask in a separate call, Dapper closing and reopening the connection in between, plus pooling, makes it uncertain which value you will see.

RETURNING can return more than one column. If you also want a timestamp generated in the database, map the result directly to a record:

var order = connection.QuerySingle<CreatedOrder>(
    """
    INSERT INTO orders (customer_email, created_at)
    VALUES (@Email, datetime('now'))
    RETURNING id, created_at AS CreatedAt
    """,
    new { Email = "ada@example.com" });

Console.WriteLine(order);

CreatedOrder { Id = 1, CreatedAt = 2026-11-03 22:09:56 }

Do not forget the alias here: CreatedOrder is a positional record, and as we saw in the second post, constructor mapping requires the column names to match the parameter names. Without AS CreatedAt, Dapper throws an InvalidOperationException saying it could not find a constructor with the signature (System.Int64 id, System.String created_at). Also, datetime('now') returns UTC time, which is why the time in the output may look different from your local time.

Now we have the id of the order; we use it when inserting the order lines:

int lines = connection.Execute(
    """
    INSERT INTO order_items (order_id, book_id, quantity, unit_price)
    VALUES (@OrderId, @BookId, @Quantity, @UnitPrice)
    """,
    new[]
    {
        new { OrderId = order.Id, BookId = 4, Quantity = 1, UnitPrice = 18.90m },
        new { OrderId = order.Id, BookId = 6, Quantity = 2, UnitPrice = 9.99m }
    });

Console.WriteLine(lines);

2

Writing an order and its lines with separate calls like this can leave a half-written order if something fails in the middle. In 07 we will see how to turn this into a single unit of work with a transaction.

On SQL Server and PostgreSQL

The pattern for getting the id differs slightly per database. On SQL Server you add an OUTPUT clause to the INSERT:

INSERT INTO authors (name, country)
OUTPUT INSERTED.id
VALUES (@Name, @Country);

PostgreSQL has the same RETURNING as SQLite:

INSERT INTO authors (name, country)
VALUES (@Name, @Country)
RETURNING id;

In both cases the C# side does not change: ExecuteScalar or QuerySingle. We do not run these SQL statements in this post; we will see them on a real server in posts 12 and 13, where we set up SQL Server and PostgreSQL with docker. The SELECT SCOPE_IDENTITY() you often see on SQL Server is a working alternative too, but OUTPUT is more flexible because it can return several columns.

Execute with a List: A Loop, Not Bulk

Back to the guessing question. The first answer is easy: it printed 3. The second answer is "three separate commands". When Dapper gets a collection as the parameter, it opens the connection once, prepares a single command and, for each element of the collection, refills the parameters and calls ExecuteNonQuery again; the number it returns is the sum of those calls. You can see this plainly in Dapper's source code, but feeling it through an experiment is more instructive. Let's break the middle element on purpose:

const string insertBook =
    "INSERT INTO books (author_id, title, price) VALUES (@AuthorId, @Title, @Price)";

var batch = new List<NewBook>
{
    new(4, "The Restaurant at the End of the Universe", 9.50m),
    new(4, null, 9.50m),
    new(4, "Life, the Universe and Everything", 9.50m)
};

try
{
    connection.Execute(insertBook, batch);
}
catch (SqliteException ex)
{
    Console.WriteLine(ex.Message);
}

var adams = connection.Query<string>(
    "SELECT title FROM books WHERE author_id = 4 ORDER BY id");

Console.WriteLine(string.Join(" | ", adams));

SQLite Error 19: 'NOT NULL constraint failed: books.title'.
The Hitchhiker's Guide to the Galaxy | The Restaurant at the End of the Universe

The second element hit the NOT NULL constraint, but the first book had already been inserted and stayed there. The third one never got its turn. With a single multi-row INSERT, either all or none would have been inserted. This behavior proves that Execute with a list is nothing more than a loop, and it has two consequences.

The first is atomicity: if you want the whole list written or nothing at all, you have to wrap the call in a transaction. The second is performance. Every element is a separate command, and without a transaction every command runs in its own little transaction. On SQLite that means a separate write to disk for every row. Let's insert the same thousand rows in two ways:

var drafts = Enumerable.Range(1, 1000)
    .Select(i => new NewBook(2, $"Draft {i}", 1.00m))
    .ToList();

var watch = Stopwatch.StartNew();
connection.Execute(insertBook, drafts);
Console.WriteLine($"Without a transaction: {watch.ElapsedMilliseconds} ms");

connection.Open();
using (var transaction = connection.BeginTransaction())
{
    watch.Restart();
    connection.Execute(insertBook, drafts, transaction);
    transaction.Commit();
    Console.WriteLine($"Inside one transaction: {watch.ElapsedMilliseconds} ms");
}

Without a transaction: 336 ms
Inside one transaction: 1 ms

These numbers were taken on my machine with a file-based SQLite database; they will differ on yours, but the ratio will be similar. Same code, same number of commands; the only difference is whether each command is made durable on its own or inside one transaction. We cover transactions and the transaction: parameter in detail in 07. For now keep this in mind: Execute with a list is a handy shortcut, but it is not a bulk insert. On a SQL Server reached over the network, every element also means a separate round trip. We will discuss the real bulk paths, SqlBulkCopy, PostgreSQL's COPY and batch insert techniques on SQLite, in post 15.

Common Pitfalls

First, an UPDATE or DELETE without WHERE. Dapper does not protect you; UPDATE books SET stock = 0 zeroes the whole table and Execute happily returns the number of affected rows. Comparing the return value with the number you expected is your last line of defense here as well.

Second, using Execute for a command that returns results. connection.Execute("SELECT id FROM books") does not throw in this setup; it returns -1 and the rows are thrown away unread. If you want results use Query, if you want a single value use ExecuteScalar.

Third, asking for last_insert_rowid() in a separate call. As you saw above, ask in the same command, or better, use RETURNING.

Fourth, using Execute with a list for large data. For a hundred rows it is fine; for a hundred thousand, think of a transaction first and then of real bulk tools.

A Small Experiment

★☆☆ Write an UPDATE that increases the stock of all of Ted Chiang's books by 5 and print the number of affected rows. Then run the same UPDATE with an author id that does not exist.

★★☆ Write a bool DeleteBook(int id) method that deletes a book by id. It should return true if the delete happened and false if the record does not exist. Call it twice with the same id.

★★★ Wrap the "Execute with a List" example in a transaction: connection.Open(), BeginTransaction(), pass the transaction parameter to Execute and call Rollback() if an exception occurs. How many of Douglas Adams's books stay in the database this time? Also try it without calling Rollback() at all: what happens when the using block ends?

All calls in this post were synchronous. In a real web application you do not want to block a thread while waiting for the database. In the next post we move to QueryAsync and ExecuteAsync, passing a CancellationToken through CommandDefinition, and managing connections correctly in ASP.NET Core. And the series does not end there; as new Dapper versions come out, we will keep going with new posts.