POSTS PODCASTS ABOUT WHAT WE'VE DONE SPEAKING
Email Subscribe Twitter GitHub

POST POST

FEB
6
2019

Managing Database Transactions in Dapper

ORIGINALLY POSTED TO: https://www.davepaquette.com/archive/2019/02/06/managing-transactions-in-dapper.aspx

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 a more complex scenario that involves executing multiple write operations. In order to ensure consistency at the database level, these operations should all succeed / fail together as a single transaction. In this example, we will be inserting a new ScheduledFlight entity along with an associated set of Flight entities.

As a quick reminder, a Flight represents a particular occurrence of a ScheduledFlight on a particular day. That is, it has a reference to the ScheduledFlight along with some properties indicating the scheduled arrival and departure times.

1
2
3
4
5
6
7
8
9
public class Flight 
{
    public int Id {get; set;}
    public int ScheduledFlightId {get; set;}
    public ScheduledFlight ScheduledFlight { get; set;}
    public DateTime Day {get; set;}
    public DateTime ScheduledDeparture {get; set;}
    public DateTime ScheduledArrival {get; set;}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public class ScheduledFlight 
{
    public int Id {get; set;}
    public string FlightNumber {get; set;}

    public int DepartureAirportId {get; set;}
    public Airport DepartureAirport {get; set;}
    public int DepartureHour {get; set;}
    public int DepartureMinute {get; set;}

    public int ArrivalAirportId {get; set;}
    public Airport ArrivalAirport {get; set;}        
    public int ArrivalHour {get; set;}
    public int ArrivalMinute {get; set;}

    public bool IsSundayFlight {get; set;}
    public bool IsMondayFlight {get; set;}
    // Some other properties
}

Inserting the ScheduledFlight

Inserting the ScheduledFlight and retrieving the database generated id is easy enough. We can use the same approach we used in the previous blog post.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
// POST api/scheduledflight
[HttpPost()]
public async Task<IActionResult> Post([FromBody] ScheduledFlight model)
{
    int newScheduledFlightId;
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var insertScheduledFlightSql = @"
INSERT INTO [dbo].[ScheduledFlight]
    ([FlightNumber]
    ,[DepartureAirportId]
    ,[DepartureHour]
    ,[DepartureMinute]
    ,[ArrivalAirportId]
    ,[ArrivalHour]
    ,[ArrivalMinute]
    ,[IsSundayFlight]
    ,[IsMondayFlight]
    ,[IsTuesdayFlight]
    ,[IsWednesdayFlight]
    ,[IsThursdayFlight]
    ,[IsFridayFlight]
    ,[IsSaturdayFlight])
VALUES
    (@FlightNumber
    ,@DepartureAirportId
    ,@DepartureHour
    ,@DepartureMinute
    ,@ArrivalAirportId
    ,@ArrivalHour
    ,@ArrivalMinute
    ,@IsSundayFlight
    ,@IsMondayFlight
    ,@IsTuesdayFlight
    ,@IsWednesdayFlight
    ,@IsThursdayFlight
    ,@IsFridayFlight
    ,@IsSaturdayFlight);
SELECT CAST(SCOPE_IDENTITY() as int)";
        newScheduledFlightId = await connection.ExecuteScalarAsync<int>(insertScheduledFlightSql, model);
    }
    return Ok(newScheduledFlightId);
}

According to the bosses at Air Paquette, whenever we create a new ScheduledFlight entity, we also want to generate the Flight entities for the next 12 months of that ScheduledFlight. We can add a method to the ScheduledFlight class to generate the flight entities.

NOTE: Let's just ignore the obvious bugs related to timezones and to flights that take off and land on a different day.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
public IEnumerable<Flight> GenerateFlights(DateTime startDate, DateTime endDate)
{
    var flights = new List<Flight>();
    var currentDate = startDate;

    while (currentDate <= endDate)
    {
        if (IsOnDayOfWeek(currentDate.DayOfWeek))
        {
            var departureTime = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, DepartureHour, DepartureMinute, 0);
            var arrivalTime = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, ArrivalHour, ArrivalMinute, 0);
            var flight = new Flight
            {
                ScheduledFlightId = Id,
                ScheduledDeparture = departureTime,
                ScheduledArrival = arrivalTime,
                Day = currentDate.Date
            };
            flights.Add(flight);
        }
        currentDate = currentDate.AddDays(1);
    }
    return flights;
}
public bool IsOnDayOfWeek(DayOfWeek dayOfWeek)
{
    return     (dayOfWeek == DayOfWeek.Sunday && IsSundayFlight)
            || (dayOfWeek == DayOfWeek.Monday && IsMondayFlight)
            || (dayOfWeek == DayOfWeek.Tuesday && IsTuesdayFlight)
            || (dayOfWeek == DayOfWeek.Wednesday && IsWednesdayFlight)
            || (dayOfWeek == DayOfWeek.Thursday && IsThursdayFlight)
            || (dayOfWeek == DayOfWeek.Friday && IsFridayFlight)
            || (dayOfWeek == DayOfWeek.Saturday && IsSaturdayFlight);
}

Now in the controller, we can add some logic to call the GenerateFlight method and then insert those Flight entities using Dapper.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
// POST api/scheduledflight
[HttpPost()]
public async Task<IActionResult> Post([FromBody] ScheduledFlight model)
{
    int newScheduledFlightId;
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var insertScheduledFlightSql = @"
INSERT INTO [dbo].[ScheduledFlight]
    ([FlightNumber]
    ,[DepartureAirportId]
    ,[DepartureHour]
    ,[DepartureMinute]
    ,[ArrivalAirportId]
    ,[ArrivalHour]
    ,[ArrivalMinute]
    ,[IsSundayFlight]
    ,[IsMondayFlight]
    ,[IsTuesdayFlight]
    ,[IsWednesdayFlight]
    ,[IsThursdayFlight]
    ,[IsFridayFlight]
    ,[IsSaturdayFlight])
VALUES
    (@FlightNumber
    ,@DepartureAirportId
    ,@DepartureHour
    ,@DepartureMinute
    ,@ArrivalAirportId
    ,@ArrivalHour
    ,@ArrivalMinute
    ,@IsSundayFlight
    ,@IsMondayFlight
    ,@IsTuesdayFlight
    ,@IsWednesdayFlight
    ,@IsThursdayFlight
    ,@IsFridayFlight
    ,@IsSaturdayFlight);
SELECT CAST(SCOPE_IDENTITY() as int)";
        newScheduledFlightId = await connection.ExecuteScalarAsync<int>(insertScheduledFlightSql, model);

        model.Id = newScheduledFlightId;
        var flights = model.GenerateFlights(DateTime.Now, DateTime.Now.AddMonths(12));

    var insertFlightsSql = @"INSERT INTO [dbo].[Flight]
    ([ScheduledFlightId]
    ,[Day]
    ,[ScheduledDeparture]
    ,[ActualDeparture]
    ,[ScheduledArrival]
    ,[ActualArrival])
VALUES
    (@ScheduledFlightId
    ,@Day
    ,@ScheduledDeparture
    ,@ActualDeparture
    ,@ScheduledArrival
    ,@ActualArrival)";

        await connection.ExecuteAsync(insertFlightsSql, flights);

    }
    return Ok(newScheduledFlightId);
}

Note that we passed in an IEnumerable<Flight> as the second argument to the ExecuteAsync method. This is a handy shortcut in Dapper for executing a query multiple times. Instead of writing a loop and calling ExecuteAsync for each flight entity, we can pass in a list of flights and Dapper will execute the query once for each item in the list.

Explicitly managing a transaction

So far, we have code that first inserts a ScheduledFlight, next generates a set of Flight entities and finally inserting all of those Flight entities. That's the happy path, but what happens if something goes wrong along the way. Typically when we execute a set of related write operations (inserts, updates and deletes), we want those operations to all succeed or fail together. In the database world, we have transactions to help us with this.

The nice thing about using Dapper is that it uses standard .NET database connections and transactions. There is no need to re-invent the wheel here, we can simply use the transaction patterns that have been around in .NET since for nearly 2 decades now.

After opening the connection, we call connection.BeginTransaction() to start a new transaction. Whenever we call ExecuteAsync (or any other Dapper extension method), we need to pass in that transaction. At the end of all that work, we call transaction.Commit(). Finally, we wrap the logic in a try / catch block. If any exception is raised, we call transaction.Rollback() to ensure that none of those write operations are committed to the database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
[HttpPost()]
public async Task<IActionResult> Post([FromBody] ScheduledFlight model)
{
    int? newScheduledFlightId = null;
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        var transaction = connection.BeginTransaction();

        try
        {
            var insertScheduledFlightSql = @"
INSERT INTO [dbo].[ScheduledFlight]
    ([FlightNumber]
    ,[DepartureAirportId]
    ,[DepartureHour]
    ,[DepartureMinute]
    ,[ArrivalAirportId]
    ,[ArrivalHour]
    ,[ArrivalMinute]
    ,[IsSundayFlight]
    ,[IsMondayFlight]
    ,[IsTuesdayFlight]
    ,[IsWednesdayFlight]
    ,[IsThursdayFlight]
    ,[IsFridayFlight]
    ,[IsSaturdayFlight])
VALUES
    (@FlightNumber
    ,@DepartureAirportId
    ,@DepartureHour
    ,@DepartureMinute
    ,@ArrivalAirportId
    ,@ArrivalHour
    ,@ArrivalMinute
    ,@IsSundayFlight
    ,@IsMondayFlight
    ,@IsTuesdayFlight
    ,@IsWednesdayFlight
    ,@IsThursdayFlight
    ,@IsFridayFlight
    ,@IsSaturdayFlight);
SELECT CAST(SCOPE_IDENTITY() as int)";
            newScheduledFlightId = await connection.ExecuteScalarAsync<int>(insertScheduledFlightSql, model, transaction);

            model.Id = newScheduledFlightId.Value;
            var flights = model.GenerateFlights(DateTime.Now, DateTime.Now.AddMonths(12));

            var insertFlightsSql = @"INSERT INTO [dbo].[Flight]
    ([ScheduledFlightId]
    ,[Day]
    ,[ScheduledDeparture]
    ,[ActualDeparture]
    ,[ScheduledArrival]
    ,[ActualArrival])
VALUES
    (@ScheduledFlightId
    ,@Day
    ,@ScheduledDeparture
    ,@ActualDeparture
    ,@ScheduledArrival
    ,@ActualArrival)";

            await connection.ExecuteAsync(insertFlightsSql, flights, transaction);
            transaction.Commit();
        }
        catch (Exception ex)
        { 
            //Log the exception (ex)
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // Handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
                // Log the exception ex2
            }
            return StatusCode(500);
        }
    }
    return Ok(newScheduledFlightId);
}

Managing database transactions in .NET is a deep but well understood topic. We covered the basic pattern above and showed how Dapper can easily participate in a transaction. To learn more about managing database transactions in .NET, check out these docs:

Wrapping it up

Using transactions with Dapper is fairly straight forward process. We just need to tell Dapper what transaction to use when executing queries. Now that we know how to use transactions, we can look at some more advanced scenarios like adding concurrency checks to update operations to ensure users aren't overwriting each other's changes.


Dave Paquette

Email Email
Web Web
Twitter Twitter
GitHub GitHub
RSS

Looking for someone else?

You can find the rest of the Western Devs Crew here.

© 2015 Western Devs. All Rights Reserved. Design by Karen Chudobiak, Graphic Designer
Posts Podcasts About Work Speaking