如何在Dapper中获得正确的DateTime

Question

我使用Dapper来处理ASP中的用户。. NET Core与SQL Server。一切都好除了DateTime。

正确的DateTime存储在数据库中(04.10.2021 16:11:45)，但是从数据库中检索它返回默认日期时间(0001-01-01T00:00:00)。

这是User模型类:

public class User
{
public DateTime CreatedAt { get; set; }
}

我在User存储库中使用此方法向数据库添加User:

public async Task<int> AddAsync(User entity)
{
entity.CreatedAt = DateTime.Now;
string sql = "insert into users (id, username, email, password, created_at) values (@Id, @Username, @Email, @Password, @CreatedAt)";
using var connection = new SqlConnection(_configuration.GetConnectionString("Default"));
connection.Open();
var result = await connection.ExecuteAsync(sql, entity);
return result;
}

这个方法获取User的id:

public async Task<User> GetByIdAsync(string id)
{
string sql = "select * from users where id = @Id";
using var connection = new SqlConnection(_configuration.GetConnectionString("Default"));
connection.Open();
var result = await connection.QuerySingleOrDefaultAsync<User>(sql, new { Id = id });
return result;
}

User数据在控制器中获取:

[HttpGet("{id}")]
public async Task<IActionResult> GetById(string id)
{
var data = await _unitOfWork.Users.GetByIdAsync(id);
if (data == null)
{
return Ok();
}
return Ok(data);
}

表的列类型为DATETIME。

Answer 1

修改select语句，使其返回CreatedAt列

select ... , created_at as [CreatedAt], ... from

或者确保您的应用程序在应用程序开始时调用这一行

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;