在c# . net 6 API项目中，Dapper不支持 TimeOnly数据类型

Question

我在ASP中使用Dapper。. NET Core 6 Web API项目，后端为SQL Server 2016数据库。Dapper的ExecuteScalar方法返回错误

System类型的成员DepartureTime。TimeOnly不能用作参数值

有什么解决办法吗?

public virtual int Create(TModel entity)
{
int result = -1;
var insertQuery = QueryGenerator.GenerateInsertQuery(typeof(TModel));
var factory = new SqlServerDbConnectionFactory(connectionString);
var profiler = CustomDbProfiler.Current;
using (var connection = ProfiledDbConnectionFactory.New(factory, profiler))
{
try
{
if (connection.State == ConnectionState.Closed)
{
connection.ConnectionString = connectionString;
connection.Open();
}
result =  connection.ExecuteScalar<int>(insertQuery, entity, transaction: null); // Throwing error
}
catch (Exception e)
{
var sql = profiler.GetCommands();
throw;
}
}
return result;
}

insertQuery包含如下查询:

INSERT INTO [Flight] ([FlightNumber], [SourceCode], [DestinCode],[DepartureTime], [EffectDate], [Stops], [TravelMinutes]) 
VALUES (@FlightNumber, @SourceCode, @DestinCode, @DepartureTime, @EffectDate, @Stops, @TravelMinutes);
SELECT SCOPE_IDENTITY()

entity为FlightModel型:

public class FlightModel
{
public int Id { get; set; }
public string FlightNumber{ get; set; }
public string SourceCode{ get; set; }
public string DestinCode { get; set; }
public TimeOnly DepartureTime{ get; set; }
public DateOnly EffectDate { get; set; }
public int Stops{ get; set; }
public int TravelMinutes { get; set; }
}

Answer 1

从这里，创建您自己的类型处理程序:

在您的配置中添加以下内容:

SqlMapper.AddTypeHandler(new SqlTimeOnlyTypeHandler());

public class SqlTimeOnlyTypeHandler : SqlMapper.TypeHandler<TimeOnly>
{
public override void SetValue(IDbDataParameter parameter, TimeOnly time)
{
parameter.Value = time.ToString();
}
public override TimeOnly Parse(object value)
{
return TimeOnly.FromTimeSpan((TimeSpan)value);
}
}

Answer 2

您可以使用TimeSpan:

public class FlightModel
{
...
public TimeSpan DepartureTime{ get; set; }
public DateTime EffectDate { get; set; }
}