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?

score 2 · Accepted Answer · edited May 23 '17 at 12:16

2

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; }
  }

edited May 23 '17 at 12:16

Community

1
1

answered Feb 03 '17 at 19:40

Zabbu

1,387
1
8
11

1

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. – QueueHammer Feb 03 '17 at 19:58
1

Ahhh, you can use the following attributes over CreatedOn - [Dapper.IgnoreUpdate, Dapper.IgnoreInsert]. I edited the above example to show how this attribute fits in. This will prevent DapperSimpleCrud from using them on inserts or updates. – Zabbu Feb 03 '17 at 20:09
What namespace are those attributes in? Dapper.Contrib has an attribute called Write, which takes a true of false, but dose not prevent the write. – QueueHammer Feb 06 '17 at 16:43
these are present in DapperSimpleCrud - https://github.com/ericdc1/Dapper.SimpleCRUD – Zabbu Feb 06 '17 at 16:52
I tried to confirm this but I think it was prevented by multiple references to the base dapper extensions throughout the solution. I'll create a reference project with just dapper.simplecrud and try again. – QueueHammer Feb 15 '17 at 20:19

Using Dapper to Insert into a Table with Default Values

1 Answers1

Linked