How to Get inserted identity value with Dapper in C#
By FoxLearn 3/10/2025 9:49:43 AM 103
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 inINSERTED
. - 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 ProductId
s 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.
- How to use JsonConverterFactory in C#
- How to serialize non-public properties using System.Text.Json
- The JSON value could not be converted to System.DateTime
- Try/finally with no catch block in C#
- Parsing a DateTime from a string in C#
- Async/Await with a Func delegate in C#
- How to batch read with Threading.ChannelReader in C#
- How to ignore JSON deserialization errors in C#