How to fetch data into dictionaries?

[mnkumar8]

Hi,

I have a Xamarin.Forms app with SQLite.Net. I have a requirement wherein I need to create tables dynamically in the sqlite DB i.e without using the model class. I can create the table dynamically using "create table .." statement and insert records using "insert into ..." statement. But, how do i fetch data from these dynamically created tables?

I am trying to get the data into dictionaries like below. I get the correct number of records in the output, but with no keys in the output dictionaries. string cmdText = "select id, name from TestTable2"; SQLiteCommand command = database.CreateCommand(cmdText); List<Dictionary<string, string>> records = command.ExecuteQuery<Dictionary<string, string>>();

Any help will be greatly appreciated.

Thanks, Naveen Kumar Madas

[mnkumar8]

I wrote the below method in SQLite.Net.SQLiteCommand.cs and that solved my problem of fetching the data into dictionaries and without specifying the class object.

    [PublicAPI]
    public IEnumerable<Dictionary<string, object>> ExecuteDeferredQuery2()
    {
        _conn.TraceListener.WriteLine("Executing Query: {0}", this);
        var stmt = Prepare();
        try
        {
            var cols = new TableMapping.Column[_sqlitePlatform.SQLiteApi.ColumnCount(stmt)];
            while (_sqlitePlatform.SQLiteApi.Step(stmt) == Result.Row)
            {
                Dictionary<string, object> dict = new Dictionary<string, object>();
                for (var i = 0; i < cols.Length; i++)
                {
                    //if (cols[i] == null)
                    //{
                    //  continue;
                    //}
                    var name = _sqlitePlatform.SQLiteApi.ColumnName16(stmt, i);
                    var colType = _sqlitePlatform.SQLiteApi.ColumnType(stmt, i);
                    Type type;
                    if (colType == ColType.Integer)
                        type = typeof(int);
                    else if (colType == ColType.Text)
                        type = typeof(string);
                    else 
                        type = typeof(object);

                    var val = ReadCol(stmt, i, colType, type);
                    dict[name] = val;

                }
                yield return dict;
            }
        }
        finally
        {
            _sqlitePlatform.SQLiteApi.Finalize(stmt);
        }
    }

And then i call it in this way in my project:

    public IEnumerable<Dictionary<string, object>> GetRecords(string cmdText)
    {
        SQLiteCommand command = database.CreateCommand(cmdText);
        IEnumerable<Dictionary<string, object>> records = command.ExecuteDeferredQuery2();
        return records;
    }