Dapper control dates

Question

This question is meant to bring some light around control date times using Dapper.

These controls are used to audit the information in a data storage and figure out when a particular row has been created / updated. I couldn't manage to find any information on GitHub's project, either here in StackOverflow, so I would like this post to become a central source of truth to help others or even to turn into a future extension of the library.

Any answer, resource or best practice will be appreciated.

Date stamping records for creation and updates is more the responsibility of your application you implement with Dapper, either in your app code or in the SQL database. Dapper is just the object mapper between your code and your database. You're best off looking at SQL triggers (https://weblogs.asp.net/jongalloway/adding-simple-trigger-based-auditing-to-your-sql-server-database) or other database methods of creating audit trails (http://stackoverflow.com/questions/17546601/best-way-to-implement-an-audit-trail-in-sql-server) — G Davison, Oct 24 '16 at 14:01
Here's some information for you for solving it in sql server. http://stackoverflow.com/questions/17116334/sql-server-2008-row-insert-and-update-timestamps — JFM, Oct 26 '16 at 11:17

score 0 · Accepted Answer · answered Oct 26 '16 at 15:56

I've ran into a case where I was working with a database that was consumed by both Rails and Dapper. Rails was managing created_at and updated_at, not the database. So with the .net application I had to implement a solution that managed these and provided the ability to add additional business logic at these layers such as events.

I've included a basic example of how I handled this with a wrapper around Dapper Simple Crud for inserts and updates. This example does not include exposing the other critical methods from dapper and simplecrud such as Query, GET, Delete, etc. You will need to expose those at your discresion.

For safety ensure that you decorate your models created_at property with the attribute [Dapper.IgnoreUpdate]

[Table("examples")]
public partial class example
{
    [Key]
    public virtual int id { get; set; }
    [Required(AllowEmptyStrings = false)]
    [StringLength(36)]
    public virtual string name { get; set; }
    [Dapper.IgnoreUpdate]
    public virtual DateTime created_at { get; set; }
    public virtual DateTime updated_at { get; set; }
}

public class ExampleRepository : IExampleRepository
{
    private readonly IYourDapperWrapper db;

    public PartnerRepository(IYourDapperWrapper yourDapperWrapper){
        if (yourDapperWrapper == null) throw new ArgumentNullException(nameof(yourDapperWrapper));
        db = yourDapperWrapper;
    }

    public void Update(example exampleObj)
    {
      db.Update(exampleObj);
    }

    public example Create(example exampleObj)
    {
      var result = db.Insert(exampleObj);
      if (result.HasValue) exampleObj.id = result.value;
      return exampleObj;
    }
}

public class YourDapperWrapper : IYourDapperWrapper
{
    private IDbConnectionFactory db;

    public YourDapperWrapper(IDbConnectionFactory dbConnectionFactory){
      if (dbConnectionFactory == null) throw new ArgumentNullException(nameof(dbConnectionFactory));
      db = dbConnectionFactory;
    }

    public int Insert(object model, IDbTransaction transaction = null, int? commandTimeout = null)
    {
      DateUpdate(model, true);
      var results = Db.NewConnection().Insert(model, transaction, commandTimeout);
      if (!results.HasValue || results == 0) throw new DataException("Failed to insert object.");
      return results;
    }

    public int Update(object model, IDbTransaction transaction = null, int? commandTimeout = null)
    {
      DateUpdate(model, false);
      var results = Db.NewConnection().Update(model, transaction, commandTimeout);
      if (!results.HasValue || results == 0) throw new DataException("Failed to update object.");     
      return results;
    }

    private void DateUpdate(object model, bool isInsert)
    {
      model.GetType().GetProperty("updated_at")?.SetValue(model, DateTime.UtcNow, null);
      if (isInsert) model.GetType().GetProperty("created_at")?.SetValue(model, DateTime.UtcNow, null);
    }
}

Dapper control dates

1 Answers1

Linked