在 Dapper 中调用 QueryAsync 会抛出 System.Data.DataException

Question

我正在尝试使用 Dapper 查询一个看起来像这样的 Sqlite 表：

CREATE TABLE "Running" (
"Id"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
"MachineName"   BLOB NOT NULL DEFAULT 'machine' UNIQUE,
"IsOnline"  INTEGER DEFAULT 0,
"TimesRan"  INTEGER DEFAULT 0,
"LoginDateTime" TEXT,
"LogoutDateTime"    TEXT,
"ForceUpgrade"  INTEGER NOT NULL DEFAULT 0,
"Version"   TEXT
)

目前在该表中，我只得到一条记录：

1 DESKTOP-KQT7CGC 0
1
2019-09-21 03:04:10.099067
2019-09-21 03:04:37.6825109 0
4.0.0

var data = await _globalConfig.Connection.GetStatusAsync();

public async Task<List<ApplicationInfoModel>> GetStatusAsync()
{
using (IDbConnection cnn = new SQLiteConnection(ConfigurationManager.GetLocalOrSharedConnectionString(), true))
{
var output = await cnn.QueryAsync<ApplicationInfoModel>("select * from Running", new DynamicParameters());
return output.ToList();
}
}

ApplicationInfoModel如下所示：

public class ApplicationInfoModel
{
public int Id { get; set; }
public string MachineName { get; set; }
public bool IsOnline { get; set; }
public int TimesRan { get; set; }
public DateTime LoginDateTime { get; set; } = DateTime.Now;
public DateTime LogoutDateTime { get; set; }
public bool ForceUpgrade { get; set; }
public string Version { get; set; }
}

我收到消息Error parsing column 1 (MachineName=System.Byte[] - Object)的System.Data.DataException。

下面是调用堆栈：

at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in C:projectsdapperDapperSqlMapper.cs:line 3633
at Dapper.SqlMapper.<QueryAsync>d__33`1.MoveNext() in C:projectsdapperDapperSqlMapper.Async.cs:line 439
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at SdeHelper.Library.Main.DataAccess.SQLiteConnector.<GetStatusAsync>d__11.MoveNext() in G:UsersmdumiGitRepossdehelperSdeHelper.Library.MainDataAccessSQLiteConnector.cs:line 154
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at SdeHelper.WpfUI.Framework.Helpers.ApplicationManager.<StartApplication>d__3.MoveNext() in G:UsersmdumiGitRepossdehelperSdeHelper.WpfUI.FrameworkHelpersApplicationManager.cs:line 39

而内部的例外：

Message: Object must implement IConvertible.
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType)

有人可以帮助我理解为什么要在模型上实现IConvertible吗？

我尝试仅为MachineName属性实现IConvertible但它仍然抛出相同的异常。

Answer 1

在此特定情况下，对象必须实现 IConvertible内部异常意味着 Dapper 无法自动将MachineName数据库字段从数据库类型BLOB转换为模型类型string

您应该对MachineName列使用 DB 类型TEXT(这应该是正确的选择(，或者将其转换为 SQLselect以正确TEXT。喜欢这个：

select cast(MachineName AS TEXT), IsOnline, TimesRan, LoginDateTime, LogoutDateTime, ForceUpgrade, Version from Running

这两个选项都应该解决您当前的问题，但例外。

有关转换为文本的更多信息，请参阅此处：Sqlite：如何为 BLOB 转换(数据作为文本(