Calling QueryAsync in Dapper throws System.Data.DataException

Question

I am trying to query with Dapper a Sqlite table which looks like this:

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
)

Currently in that table I got only one record:

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

The ApplicationInfoModel looks like this:

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

}

I am getting a System.Data.DataException with the message Error parsing column 1 (MachineName=System.Byte[] - Object).

Here is the call stack:

   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in C:\projects\dapper\Dapper\SqlMapper.cs:line 3633
   at Dapper.SqlMapper.<QueryAsync>d__33`1.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.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:\Users\mdumi\GitRepos\sdehelper\SdeHelper.Library.Main\DataAccess\SQLiteConnector.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:\Users\mdumi\GitRepos\sdehelper\SdeHelper.WpfUI.Framework\Helpers\ApplicationManager.cs:line 39

And the inner exception:

Message: Object must implement IConvertible.

   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType)

Can someone help me understand why should I implement IConvertible on the model?

I tried to implement IConvertible only for MachineName property but it still throws the same exception.

Answer 1

In this specific situation Object must implement IConvertible inner exception means that Dapper cannot automatically convert your MachineName DB field from DB type BLOB to model type string

You should either use DB type TEXT for MachineName column (which is the right choice supposedly) or cast it in SQL selectto TEXT properly. Like this:

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

Both options should fix your current problem with the exception.

For more on casting to TEXT see here: Sqlite: How to cast(data as TEXT) for BLOB