How to Get inserted identity value with Dapper in C#

Insert a Product and Get the Identity Value

Let’s say we have a Products table, and we want to insert a new product and retrieve its generated ProductId.

INSERT INTO Products
(ProductName, Price, CategoryId)
OUTPUT INSERTED.ProductId
VALUES
(@ProductName, @Price, @CategoryId)

To fetch the identity value using Dapper, you can execute the query and use ExecuteScalar<int>():

public int InsertProduct(Product product)
{
    using (var con = new SqlConnection(ConnectionString))
    {
        var productId = con.ExecuteScalar<int>(INSERT_SQL, param: product);
        return productId;
    }
}

This inserts the new product and returns the generated identity value:

ProductId = 10123

Note: This is equivalent to using QuerySingle<int>(). However, ExecuteScalar<T>() is preferred here because it specifically returns a single value, which makes the intention clear. QuerySingle<T>() is typically used to retrieve a single row and map it to an object.

Use QuerySingle<T>() for Returning Multiple Columns

If you want to insert a row and retrieve multiple columns, you can use OUTPUT INSERTED.* (or list the specific columns) in the SQL query:

INSERT INTO Products
(ProductName, Price, CategoryId)
OUTPUT INSERTED.ProductId, INSERTED.ProductName, INSERTED.Price, INSERTED.CategoryId
VALUES
(@ProductName, @Price, @CategoryId)

To handle this with Dapper and map the result to an object:

public Product InsertProduct(Product productToInsert)
{
    using (var con = new SqlConnection(ConnectionString))
    {
        var insertedProduct = con.QuerySingle<Product>(INSERT_SQL, param: productToInsert);
        return insertedProduct;
    }
}

This inserts the product and returns the full product details (including the generated ProductId) mapped to a Product object:

{
  "ProductId": 10124,
  "ProductName": "New Product",
  "Price": 29.99,
  "CategoryId": 5
}

Updates, Deletes, and Multiple Output Rows

Just as you can output inserted values, you can also output updated and deleted values using the INSERTED and DELETED special temporary tables:

• INSERT – Values inserted are available in INSERTED.
• UPDATE – Old values are in DELETED, and new values are in INSERTED.
• DELETE – Values deleted are available in DELETED.

If you modify multiple rows, you will get multiple rows of output. You can retrieve these results in Dapper using Query<T>().

Deleting Multiple Products and Getting Deleted IDs

If you want to delete products from a specific category and retrieve the ProductIds of the deleted products, you can use the OUTPUT DELETED.<column name> clause in your SQL query:

DELETE FROM Products
OUTPUT DELETED.ProductId
WHERE CategoryId = 3

To fetch the deleted product IDs using Dapper, use Query<int>():

public IEnumerable<int> DeleteProductsByCategory(int categoryId)
{
    using (var con = new SqlConnection(ConnectionString))
    {
        var deletedProductIds = con.Query<int>(DELETE_SQL, new { CategoryId = categoryId });
        return deletedProductIds;
    }
}

This will delete the products and return the deleted product IDs (represented as a JSON array):

[
  10120,
  10121,
  10122
]

This method works similarly to the insert and update examples, but allows you to handle multiple rows and the returned data efficiently.