locked
How to perform raw SQLite query (With multiple row result) through SQLiteAsyncConnection

    Question

  • I do not have a class map to a table. I'm referring to the following thread. The get the number of row in a table, I am applying the following technique

    How to perform raw SQLite query through SQLiteAsyncConnection

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

    Now, instead of obtaining result as number of row, I would like to retrieve result in multiple row data.

    In Java, to obtain multiple row result data, I would perform

    Cursor cursor = database.rawQuery(sql, null);
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        // For every cursor, obtain its col data by 
        // cursor.getLong(0), cursor.getInt(1), ...
        cursor.moveToNext();
    }

    Given a same sql statement, how can I achieve using SQLiteAsyncConnection?

    Monday, December 3, 2012 8:38 AM

Answers

  • I added 2 new functions into SQLite.cs. Not elegant, but it works for me.

        // Invented by yccheok :)
        public IEnumerable<IEnumerable<object>> ExecuteScalarEx()
        {
            if (_conn.Trace)
            {
                Debug.WriteLine("Executing Query: " + this);
            }
    
            List<List<object>> result = new List<List<object>>();
            var stmt = Prepare();
    
            while (SQLite3.Step(stmt) == SQLite3.Result.Row)
            {
                int columnCount = SQLite3.ColumnCount(stmt);
    
                List<object> row = new List<object>();
                for (int i = 0; i < columnCount; i++)
                {
                    var colType = SQLite3.ColumnType(stmt, i);
                    object val = ReadColEx (stmt, i, colType);
                    row.Add(val);
                }
                result.Add(row);
            }
            return result;
        }
    
        // Invented by yccheok :)
        object ReadColEx (Sqlite3Statement stmt, int index, SQLite3.ColType type)
        {
            if (type == SQLite3.ColType.Null) {
                return null;
            } else {
                if (type == SQLite3.ColType.Text) {
                    return SQLite3.ColumnString (stmt, index);
                }
                else if (type == SQLite3.ColType.Integer)
                {
                    return (int)SQLite3.ColumnInt (stmt, index);
                }
                else if (type == SQLite3.ColType.Float)
                {
                    return SQLite3.ColumnDouble(stmt, index);
                }
                else if (type == SQLite3.ColType.Blob)
                {
                    return SQLite3.ColumnBlob(stmt, index);
                }
                else
                {
                    throw new NotSupportedException("Don't know how to read " + type);
                }
            }
        }
    Wednesday, December 5, 2012 3:28 AM

All replies

  • Hi,
    I think you should use LINQ which will return a list of classes every class represent a row in the database.
    Regards,

    Ibraheem Osama Mohamed | My Blog | @IbraheemOsamaMo

    (If my reply answers your question, please propose it as an answer)

    Tuesday, December 4, 2012 5:56 PM
  • I added 2 new functions into SQLite.cs. Not elegant, but it works for me.

        // Invented by yccheok :)
        public IEnumerable<IEnumerable<object>> ExecuteScalarEx()
        {
            if (_conn.Trace)
            {
                Debug.WriteLine("Executing Query: " + this);
            }
    
            List<List<object>> result = new List<List<object>>();
            var stmt = Prepare();
    
            while (SQLite3.Step(stmt) == SQLite3.Result.Row)
            {
                int columnCount = SQLite3.ColumnCount(stmt);
    
                List<object> row = new List<object>();
                for (int i = 0; i < columnCount; i++)
                {
                    var colType = SQLite3.ColumnType(stmt, i);
                    object val = ReadColEx (stmt, i, colType);
                    row.Add(val);
                }
                result.Add(row);
            }
            return result;
        }
    
        // Invented by yccheok :)
        object ReadColEx (Sqlite3Statement stmt, int index, SQLite3.ColType type)
        {
            if (type == SQLite3.ColType.Null) {
                return null;
            } else {
                if (type == SQLite3.ColType.Text) {
                    return SQLite3.ColumnString (stmt, index);
                }
                else if (type == SQLite3.ColType.Integer)
                {
                    return (int)SQLite3.ColumnInt (stmt, index);
                }
                else if (type == SQLite3.ColType.Float)
                {
                    return SQLite3.ColumnDouble(stmt, index);
                }
                else if (type == SQLite3.ColType.Blob)
                {
                    return SQLite3.ColumnBlob(stmt, index);
                }
                else
                {
                    throw new NotSupportedException("Don't know how to read " + type);
                }
            }
        }
    Wednesday, December 5, 2012 3:28 AM