Execute: Insert, Update and Delete

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.

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(quot;Added a product: {newId > 77}");

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

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

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

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:

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:

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(quot;Shippers added: {added}");

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

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