Cheok Yan Cheng Cheok Yan Cheng - 2 months ago 27
C# Question

How to perform raw SQLite query through SQLiteAsyncConnection

I am referring to http://timheuer.com/blog/archive/2012/08/07/updated-how-to-using-sqlite-from-windows-store-apps.aspx and http://wp.qmatteoq.com/using-sqlite-in-your-windows-8-metro-style-applications/

As far as I know,

sqlite-net
doesn't have good support on foreign key. Hence, instead of creating a class to map to a table, I was using the following way for table creation.

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





Now, I would like to perform query. For instance, how many rows in a particular table?

I may perform
QueryAsync
as in read column names from sqlite table windows 8 app. However, I do not have a class to map to a table.

In Java, I can perform raw query as in follow :

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


How can I do so in C# Windows Store App?

Answer

To get a single value, like a count, you would use ExecuteScalarAsync:

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