c# - 如何通过 SQLiteAsyncConnection 执行原始 SQLite 查询

Question

我指的是 http://timheuer.com/blog/archive/2012/08/07/updated-how-to-using-sqlite-from-windows-store-apps.aspx和 http://wp.qmatteoq.com/using-sqlite-in-your-windows-8-metro-style-applications/

据我所知，sqlite-net 对外键的支持不是很好。因此，我没有创建类来映射到表，而是使用以下方式创建表。

    public static readonly String PROFILE_TABLE_CREATE = "create table if not exists " 
            + PROFILE_TABLE_NAME + "(" 
            + COLUMN_PROFILE_ID + " integer primary key autoincrement, "
            + COLUMN_TIMESTAMP + " integer not null, "
            + COLUMN_PROFILE_NAME + " text not null, "
            + COLUMN_AGE + " integer not null, "
            + COLUMN_GENDER + " integer not null"
            + ");";

    private async void CreateDatabase()
    {
        SQLiteAsyncConnection conn = new SQLiteAsyncConnection(DATABASE_NAME);
        await conn.ExecuteAsync(PROFILE_TABLE_CREATE);
    }

---

现在，我想执行查询。例如，特定表中有多少行？

我可以像 read column names from sqlite table windows 8 app 那样执行 QueryAsync .但是，我没有要映射到表的类。

在 Java 中，我可以执行原始查询，如下所示:

    String sql =  "select count(*) from " + tableName + " where " + HistorySQLiteOpenHelper.COLUMN_FK_TIMESTAMP + " = " + profileTimestamp;
    Cursor cursor = database.rawQuery(sql, null);

如何在 C# Windows 应用商店应用程序中执行此操作？

Answer 1

要获取单个值(如计数)，您可以使用 ExecuteScalarAsync:

SQLiteAsyncConnection conn = new SQLiteAsyncConnection(DATABASE_NAME);
int profileCount = await conn.ExecuteScalarAsync<int>("select count(*) from " + PROFILE_TABLE);