Using Dapper to Insert into a Table with Default Values

Question

I have a table that has a field that is not null and has a default value defined

[CreatedOn] not null default(getdate())

The the property I use in the model for dapper is a nullable DateTime

public DateTime? CreatedOn { get; set; }

If I try to insert, an exception is thrown for trying to insert an null value in a not null field.

cm.Insert<MyObject>(obj); //Throws because CreatedOn == null

What I'm looking for is the default to be set by the database, and to still be able to see that value when I read objects. Because Dapper is passing the value as null, the database throws the error. If I ignore the property then I lose the ability to read it. I'm hoping for a one way ignore, or a flag to let dapper know there will be a default. How does one make this work?

Answer 1

For the Question above, I handle dapper defaults very simply by adding them in the constructor. In your case this would like like so:

  [Table("my_objects")]
  public class MyObject
  {
    public DateTime? CreatedOn { get; set; }

    public MyObject()
    {
      CreatedOn = DateTime.UtcNow;
    }
  }

However, if you are attempting to handle created_at and updated_at through dapper rather than in the database, i would advise following a route like i have outlined here - Dapper control dates

Lastly, if you want the database to handle this field, you can use the following example:

  [Table("my_objects")]
  public class MyObject
  {
    [Dapper.IgnoreUpdate, Dapper.IgnoreInsert]
    public DateTime? CreatedOn { get; set; }
  }