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 ExecuteAsync
method.
As a quick reminder, here is the Aircraft
class:
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.
Insert
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 INSERT
statement.
Our Aircraft
table’s 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 Aircraft
.
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<int>
. The 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.
[ ] |
Update
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 Id
.
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 Aircraft
entity.
// PUT api/aircraft/id |
Delete
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