This is a part of a series of blog posts on data access with Dapper. To see the full list of posts, visit the Dapper Series Index Page.
In today's post, we explore how easy it is to perform basic Insert, Update and Delete operations using the same
Aircraft entity that we used in the first post in this series. Basically, instead of using Dapper's
QueryAsync extension method that we used to retrieve data, we will use the
As a quick reminder, here is the
public class Aircraft
NOTE: In these examples, I am ignoring some important aspects like validation. I want to focus specifically on the Dapper bits here but validation is really important. In a real-world scenario, you should be validating any data that is passed in to the server. I recommend using Fluent Validation.
Inserting a single new record is really easy. All we need to do is write an
INSERT statement with parameters for each column that we want to set.
The version of the
ExecuteAsync method we used here accepts two parameters: a string containing the SQL statement to execute and an object containing the parameter values to bind to the statement. In this case, it is an instance of the
Aircraft class which has properties with names matching the parameters defined in the
Id column is an auto-incremented identity column. That means the primary key is generated by the database when the row is inserted. We will likely need to pass that value back to whoever called the API so they know how to retrieve the newly inserted
An easy way to get the generated
Id is to add
SELECT CAST(SCOPE_IDENTITY() as int) after the
INSERT statement. The
SCOPE_IDENTITY() function returns the last identity value that was generated in any table in the current session and current scope.
Now, since the SQL statement we are executing will be returning a single value (the generated id), we need to call
ExecuteScalarAsync method executes a SQL statement that returns a single value whereas the
ExecuteAsync method executes a SQL statement that does not return a value.
Updating an existing entity is similar to inserting. All we need is a SQL statement containing an
UPDATE statement that sets the appropriate columns. We also want to make sure we include a
WHERE clause limiting the update only to the row with the specified
Again, the parameters in the SQL statement match the names of the properties in our
Aircraft class. All we need to do is call the
ExecuteAsync method passing in the SQL statement and the
// PUT api/aircraft/id
Deleting an entity is the easiest of the three operations since it only requires a single parameter: the unique Id to identify the entity being deleted. The SQL statement is a simple
DELETE with a
WHERE clause on the
Id column. To execute the delete, call the
ExecuteAsync method passing in the SQL statement and an anonymous object containing the
Id to delete.
// DELETE api/aircraft/id
I really appreciate how simple delete is using Dapper. When using Entity Framework, delete requires you to first fetch the existing entity, then delete it. That requires 2 round trips to the database while the approach we used here only requires a single round trip.
Wrapping it up
Basic insert, update and delete operations are easy to implement using Dapper. Real world scenarios are often a little more complex and we will dig into some of those scenarios in future posts:
- Bulk inserts, updates and deletes
- Managing transactions
- Optimistic concurrency checks