Basic Insert Update and Delete with Dapper

Dapper

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 
{
    public int Id { get; set; }

    public string Manufacturer {get; set;}

    public string Model {get; set;}

    public string RegistrationNumber {get; set;}

    public int FirstClassCapacity {get; set;}

    public int RegularClassCapacity {get; set;}

    public int CrewCapacity {get; set;}

    public DateTime ManufactureDate {get; set; }

    public int NumberOfEngines {get; set;}
}

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. 

[HttpPost()]
public async Task<IActionResult> Post([FromBody] Aircraft model)
{
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var sqlStatement = @"
INSERT INTO Aircraft 
(Manufacturer
,Model
,RegistrationNumber
,FirstClassCapacity
,RegularClassCapacity
,CrewCapacity
,ManufactureDate
,NumberOfEngines
,EmptyWeight
,MaxTakeoffWeight)
VALUES (@Manufacturer
,@Model
,@RegistrationNumber
,@FirstClassCapacity
,@RegularClassCapacity
,@CrewCapacity
,@ManufactureDate
,@NumberOfEngines
,@EmptyWeight
,@MaxTakeoffWeight)";
        await connection.ExecuteAsync(sqlStatement, model);
    }
    return Ok();
}

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.

[HttpPost()]
public async Task<IActionResult> Post([FromBody] Aircraft model)
{
    int newAircraftId;
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var sqlStatement = @"
INSERT INTO Aircraft 
(Manufacturer
,Model
,RegistrationNumber
,FirstClassCapacity
,RegularClassCapacity
,CrewCapacity
,ManufactureDate
,NumberOfEngines
,EmptyWeight
,MaxTakeoffWeight)
VALUES (@Manufacturer
,@Model
,@RegistrationNumber
,@FirstClassCapacity
,@RegularClassCapacity
,@CrewCapacity
,@ManufactureDate
,@NumberOfEngines
,@EmptyWeight
,@MaxTakeoffWeight);

SELECT CAST(SCOPE_IDENTITY() as int)";
        newAircraftId = await connection.ExecuteScalarAsync<int>(sqlStatement, model);
    }
    return Ok(newAircraftId);
}

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
[HttpPut("{id}")]
public async Task<IActionResult> Put(int id, [FromBody] Aircraft model)
{
    if (id != model.Id) 
    {
        return BadRequest();
    }

    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var sqlStatement = @"
UPDATE Aircraft 
SET  Manufacturer = @Manufacturer
,Model = @Model
,RegistrationNumber = @RegistrationNumber 
,FirstClassCapacity = @FirstClassCapacity
,RegularClassCapacity = @RegularClassCapacity
,CrewCapacity = @CrewCapacity
,ManufactureDate = @ManufactureDate
,NumberOfEngines = @NumberOfEngines
,EmptyWeight = @EmptyWeight
,MaxTakeoffWeight = @MaxTakeoffWeight
WHERE Id = @Id";
        await connection.ExecuteAsync(sqlStatement, model);
    }
    return Ok();
}

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
[HttpDelete("{id}")]
public async Task<IActionResult> Delete(int id)
{

    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var sqlStatement = "DELETE Aircraft WHERE Id = @Id";
        await connection.ExecuteAsync(sqlStatement, new {Id = id});
    }
    return Ok();
}

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: