SQLite .NET，ExecuteScalarAsync<int>，如何知道什么时候没有结果? [英] SQLite .NET, ExecuteScalarAsync&lt;int&gt;, how to know when there was no result?

问题描述

SQL 语句正在检索行的 ID.但可能没有这样的行.当我在 GUI 工具中执行特定的 SQL 语句时，它返回在 0 毫秒内从以下位置返回的 0 行:...".

The SQL statement is retrieving the ID of a row. But there may be no such row. When I executed a particular SQL statement in a GUI tool, it returned "0 rows returned in 0ms from:...".

但是，当我使用ExecuteScalarAsync 执行相同的SQL 语句时，它返回0，并且没有发生异常或null.我怎么知道是否没有这样的行?理论上可以有一行ID为0.

However, when I executed the same SQL statement with ExecuteScalarAsync<int>, it returned 0, and no exception or null occurred. How do I know if there was no such row? Theoretically, there can be a row whose ID is 0.

附注:这是我使用 Nuget 添加的 SQLite-net (https://github.com/praeclarum/sqlite-net).它的版本是 1.4.118.SQL 语句非常简单.我更改了字段名称，但基本上是这样的:

PS: It is SQLite-net which I added using Nuget (https://github.com/praeclarum/sqlite-net). Its version is 1.4.118. The SQL statement was very simple one. I changed the field names, but basically it is something like:

using SQLite;

public Task<int> GetLastID()
{
    return RealDatabase.ExecuteScalarAsync<int>
        ("SELECT max(ID) FROM DATA)");
}

推荐答案

我用一个扩展方法解决了这个问题:

I solved this with an extension method:

using System;
using System.Threading.Tasks;
using SQLite;

namespace MyApp.DB
{
    public class Row<T>
    {
        public T scalar_value { get; set; }
    }

    public class Maybe<T>
    {
        public readonly T Value;
        public Maybe(T v) { Value = v;  }
    }

    public static class SQLiteExtensions
    {
        public async static Task<Maybe<T>> QueryScalarAsync<T>(this SQLite.SQLiteAsyncConnection conn, string sql)
        {
            var res = await conn.QueryAsync<Row<T>>(sql);
            if (res.Count == 0)
            {
                return null;
            }
            else
            {
                return new Maybe<T>(res[0].scalar_value);
            }
        }
    }
}

null 的返回值表示没有找到行，并且任何返回值本身都包含在 Maybe 类中，以便可以区分空值:

A return value of null signifies no rows found and any return value itself is wrapped in a Maybe class so that null values can be distinguished:

var version = await conn_.QueryScalarAsync<int>("SELECT version AS scalar_value FROM schema_version");
if (version == null)
{
    Debug.WriteLine("no rows found");
}
else
{
    Debug.WriteLine(string.Format("value = {0}", version.Value));
}

唯一的问题是您的查询必须包含名为 scalar_value 的列.

The only gotcha is that your query must include a column named scalar_value.

这篇关于SQLite .NET，ExecuteScalarAsync<int>，如何知道什么时候没有结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！