Dapper returns 01-01-0001 00:00:00 from MySQL DATETIME

dapper datetime mysql

Question

I can't get Dapper to work together with MySQL database DATETIME type. It always returns "01-01-0001 00:00:00" for DATETIME columns. Here are some details:

.NET: 
DNX 4.5.1
MysqlData 6.9.8
Dapper - 1.50.0-beta6
MySQL: Server Version: 5.5.43-0+deb8u1

Table:

CREATE TABLE campaign (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  ...
  creation_date datetime DEFAULT NULL,
  last_modified datetime DEFAULT NULL,
  ...
)
ENGINE = INNODB

C#:

public class Campaign 
 {
     public int Id { get; set; }
     public DateTime CreationDate {get; set; }
     public DateTime LastModified { get; set; }
     public DateTime StartDate { get; set; }
     public DateTime EndDate { get; set; }
 }


...
const string myConnectionString = "server=[IP];uid=[usr];pwd=[pass];database=[db];AllowZeroDatetime=false;";
 try
 {
     _conn = new MySqlConnection { ConnectionString = myConnectionString };
     _conn.Open();
 }
...

public Dictionary<int, Campaign> GetCampaigns()
{
            var campaignsDict = _conn.Query<Campaign>("select * from campaign").ToDictionary(row => row.Id,row => row);
            return campaignsDict;
} 

that's how one retrieved row looks inside the campaignsDict:

enter image description here

P.S. I'm just learning .NET, so I'm a noob in this technology.

Accepted Answer

Dapper does not automatically remove underscores unless told to do so, via:

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

So what you actually have here is properties that haven't been mapped from columns. The default value of a DateTime is: 01-01-0001 00:00:00.

Options:

  • enable underscore matching
  • alias the columns in the query to match the properties
  • change the properties to match the database columns
  • write a custom type map


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why