C# – Get inserted identity value with Dapper

When you insert a record into a table with an identity column, the value for the identity column is automatically generated for you. The simplest way to get the inserted identity value is to put OUTPUT INSERTED.<identity column name> in the insert statement:

INSERT INTO Orders
(CustomerId, Status, StoreId)
OUTPUT INSERTED.OrderId
VALUES
(@CustomerID, @Status, @StoreId)
Code language: SQL (Structured Query Language) (sql)

To get the output value with Dapper, use ExecuteScalar<int>():

public int InsertOrder(Order order)
{
	using (var con = new SqlConnection(ConnectionString))
	{
		var identity = con.ExecuteScalar<int>(INSERT_SQL, param: order);
		return identity;
	}
}
Code language: C# (cs)

This inserts the new row and returns the generated identity integer:

75129Code language: plaintext (plaintext)

Note: This is equivalent to using QuerySingle<int>(). I prefer to use ExecuteScalar<T>(), because its only purpose is to fetch a single value, so the intention is clear. QuerySingle<T> is usually used to fetch a single row and map it to an object.

Use QuerySingle<T>() when you’re outputting multiple columns

Let’s say you want to return multiple columns from the inserted row. You can use OUTPUT INSERTED.* (or type out the column names as shown) in the insert statement:

INSERT INTO Orders
(CustomerId, Status, StoreId)
OUTPUT INSERTED.OrderId, INSERTED.CustomerId, INSERTED.Status, INSERTED.StoreId
VALUES
(@CustomerID, @Status, @StoreId)
Code language: SQL (Structured Query Language) (sql)

With Dapper, use QuerySingle<T>() to map the output columns to an object:

public Order InsertOrder(Order orderToInsert)
{
	using (var con = new SqlConnection(ConnectionString))
	{
		var insertedOrder = con.QuerySingle<Order>(INSERT_SQL, param: orderToInsert);
		return insertedOrder;
	}
}
Code language: C# (cs)

This inserts the new order row and returns all of the columns, which Dapper maps to an Order object (shown as JSON):

{
  "OrderId": 75131,
  "CustomerId": 1,
  "Status": "New",
  "StoreId": 1
}Code language: JSON / JSON with Comments (json)

Updates, deletes, and multiple output rows

You can output updated and deleted values in the same way that you can output inserted values. Whenever you modify data, values are available in the INSERTED and DELETED special temp tables.

• INSERT – Values you insert are available in INSERTED.
• UPDATE – Old values are in DELETED. New values are in INSERTED.
• DELETE – Values you deleted are in DELETED.

These values are per modified row. This means if you’re modifying multiple rows, and outputting values from INSERTED/DELETED, you’ll get back multiple rows of output values. With Dapper, use Query<T>() to get the multiple rows of output.

For example, let’s say you’re deleting a bunch of records output the deleted record ids.

First, put OUTPUT DELETED.<column name> in the delete statement:

DELETE FROM Orders
OUTPUT DELETED.OrderId
WHERE [Status]='Canceled'
Code language: SQL (Structured Query Language) (sql)

With Dapper, use Query<int>() to get all of the deleted order ids:

public IEnumerable<int> DeleteCanceledOrders()
{
	using (var con = new SqlConnection(ConnectionString))
	{
		var deletedOrderIds = con.Query<int>(DELETE_SQL);
		return deletedOrderIds;
	}
}
Code language: C# (cs)

This deletes multiple orders and outputs the following deleted order ids (shown as a JSON array):

[
  43659,
  43660,
  43661
]Code language: JSON / JSON with Comments (json)