Using Dapper Output Params for Optimistic Concurrency

Ever had a problem with two users editing the same record? Maybe one of them overwrote the other’s changes.

The answer to this is optimistic concurrency, which is a fancy term for the practice where each entity checks, before saving, that no-one else has updated the record since it was originally loaded.

As an aside, “pessimistic concurrency” is so-called because under this model, the records are locked when someone opens them for editing, and unlocked once the record is saved or the changes discarded. Optimistic concurrency only checks for changes at the point of saving.

In practical terms, this involves adding a column into your SQL database table; this column is updated each time the row is updated. You can do this manually, but SQL Server gives it to you for free using the rowversion data type.

CREATE TABLE Employee (
  Id int identity not null primary key,
  Name nvarchar(200) not null,
  StartDate datetime not null,
  EndDate datetime null,
  ConcurrencyToken rowversion not null
)

The ConcurrencyToken field is technically a byte array, but can be cast to a bigint if you want a readable representation. It’s simply a value that increments every time that the individual row changes.

Let’s say we create a .NET object for this. It looks like this:

public class Employee {
  public int Id { get; set; }
  public string Name { get; set; }
  public DateTime StartDate { get; set; }
  public DateTime? EndDate { get; set; }
  public byte[] ConcurrencyToken { get; set; }
}

Using Dapper, we can write the following simple data access layer:

public class EmployeeDAL
{
  private DbConnection _connection; // assume we've already got this

  public async Task<Employee> GetAsync(int id)
  {
    const string Sql = "SELECT * FROM Employee WHERE Id = @id";
    return _connection.SingleOrDefaultAsync(Sql, new { id });
  }

  public async Task<Employee> InsertAsync(Employee employee)
  {
    const string Sql = @"
INSERT INTO Employee ( Name, StartDate, EndDate )
VALUES ( @Name, @StartDate, @EndDate )";

    await _connection.ExecuteAsync(Sql, employee);
    return employee;
  }

  public async Task<Employee> UpdateAsync(Employee employee)
  {
    const string Sql = @"
UPDATE Employee SET
  Name = @Name,
  StartDate = @StartDate,
  EndDate = @EndDate
WHERE Id = @Id";

    await _connection.ExecuteAsync(Sql, employee);
    return employee;
  }
}

That’s great, but if you ran this, you’d notice that, on inserting or updating the Employee, the Id and ConcurrencyToken fields don’t change. To do that, you’d have to load a new version of the object. Also, the concurrency field isn’t actually doing anything – what’s that about?

Let’s make some changes. In our UpdateAsync method, let’s do:

public async Task<Employee> UpdateAsync(Employee employee)
{
  const string Sql = @"
UPDATE Employee SET
 Name = @Name,
 StartDate = @StartDate,
 EndDate = @EndDate
WHERE Id = @Id
AND ConcurrencyToken = @ConcurrencyToken";

  var rowCount = await _connection.ExecuteAsync(Sql, employee);
  if (rowCount == 0)
  {
    throw new Exception("Oh no, someone else edited this record!");
  }

  return employee;
}

This is crude, but we now can’t save the employee if the ConcurrencyToken field that we have doesn’t match the one in SQL. Dapper is taking care of mapping our object fields into parameters for us, and we can use a convention in our SQL that our parameter names will match the object fields.

However, we still won’t update the concurrency token on save, and when inserting we still don’t know what the ID of the new employee is. Enter output parameters!

public async Task<Employee> InsertAsync(Employee employee)
{
  const string Sql = @"
INSERT INTO Employee ( Name, StartDate, EndDate )
VALUES ( @Name, @StartDate, @EndDate )

SELECT @Id = Id, @ConcurrencyToken = ConcurrencyToken
FROM Employee WHERE Id = SCOPE_IDENTITY()";

  var @params = new DynamicParameters(employee)
    .Output(employee, e => e.ConcurrencyToken)
    .Output(employee, e => e.Id);

  await _connection.ExecuteAsync(Sql, @params);
  return employee;
}

Now after saving, the employee object will have updated Id and ConcurrencyToken values. We’ve used DynamicParameters instead of an object, which has allowed us to map explicit Output params to update the target object. How does it work? You give Dapper a target object, and a function to tell it which property to update. It then looks for a SQL output parameter or result that matches that property, and then uses that knowledge to update the property based on the results from SQL.