Forum Cross Platform with Xamarin

SQlite query without known columns at design

I am using the sqlite-net-pcl nuget and everything is great for my tables where I know the architecture at design time

During run time, I have to attach a table with unknown column names.
Actually, I do have the column names and data types, they are stored in another table.

I have a class that uses a dictionary to store the values under their column name.
I can create the table and insert data into it just fine.

To create the table, I just loop through all the keys and generate the "CREATE TABLE ... " sql command.
To insert, I do the same thing, loop through the keys and generate the "INSERT ..." sql command.

The problem I am having is querying the table.

SQLiteConnection offers

Query<T>(string query, params object[] args) 

and

Query(TableMapping map, string query, params object[] args).

Neither of these will work since my class uses a dictionary and not Properties.

public class MyData 
{
    public class DataValue
    {
        public bool IsChanged;
        private object _value;
        public object Value
        {
            get => _value;
            set
            {
                if (value == _value) return;
                _value = value;
                IsChanged = true;
            }
        }
    }

    public readonly Dictionary<string, DataValue> KeyValues = new Dictionary<string, DataValue>();

    public T GetValue<T>(string key)
    {
        if (KeyValues.ContainsKey(key))
            return (T)KeyValues[key].Value;
        return default(T);
    }

    public void SetValue<T>(string key, T value)
    {
        if (KeyValues.ContainsKey(key))
        {
            var vv = KeyValues[key];
            vv.Value = value;
        }
        else
        {
            var vv = new DataValue { Value = value };
            KeyValues.Add(key, vv);
        }
    }
}

So the question is, how can I execute a query? I can generate a query string just fine "SELECT FROM mytable WHERE somevalue = 'whatever'"
But I can't use Query(...) because there is no TableMapping set for the class.

I'd like a way to query the table, then loop through the query results and set the value in my dictionary, but I'm at a loss as to how. Any ideas?

Best Answer

  • CaseCase US ✭✭✭
    Accepted Answer

    for anyone facing a similar issue, I did find a way to loop through the results of a query as a List<object[]>.
    The only issue is that you need to construct the SQL Query string yourself, and I couldn't figure out how to pass parameters.
    For example SELECT * FROM mytable WHERE col1 = ? AND col2 = ?
    I had to loop through the ?'s myself and replace them with the appropriate value (encapsulating with '' as needed)

        private List<object[]> RunSql(string sqlString, bool includeColumnNamesAsFirstRow)
        {
            var lstRes = new List<object[]>();
            SQLitePCL.sqlite3_stmt stQuery = null;
            try
            {
                stQuery = SQLite3.Prepare2(Connection.Handle, sqlString);
                var colLenght = SQLite3.ColumnCount(stQuery);
    
                if (includeColumnNamesAsFirstRow)
                {
                    var obj = new object[colLenght];
                    lstRes.Add(obj);
                    for (int i = 0; i < colLenght; i++)
                    {
                        obj[i] = SQLite3.ColumnName(stQuery, i);
                    }
                }
    
                while (SQLite3.Step(stQuery) == SQLite3.Result.Row)
                {
                    var obj = new object[colLenght];
                    lstRes.Add(obj);
                    for (int i = 0; i < colLenght; i++)
                    {
                        var colType = SQLite3.ColumnType(stQuery, i);
                        switch (colType)
                        {
                            case SQLite3.ColType.Blob:
                                obj[i] = SQLite3.ColumnBlob(stQuery, i);
                                break;
                            case SQLite3.ColType.Float:
                                obj[i] = SQLite3.ColumnDouble(stQuery, i);
                                break;
                            case SQLite3.ColType.Integer:
                                obj[i] = SQLite3.ColumnInt64(stQuery, i);
                                break;
                            case SQLite3.ColType.Null:
                                obj[i] = null;
                                break;
                            case SQLite3.ColType.Text:
                                obj[i] = SQLite3.ColumnString(stQuery, i);
                                break;
                            default:
                                throw new Exception("Unrecognized data type in SQLiteDatabase.cs RunSql()");
                        }
                    }
                }
                return lstRes;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                if (stQuery != null)
                {
                    SQLite3.Finalize(stQuery);
                }
            }
        }
    

Answers

  • N_BauaN_Baua INMember ✭✭✭✭✭

    Hi @Case,

    I am not SQLLite pro, however when I read about your concern, first thing it came to my mind was, could querying schema and getting column names make your life easy?!

    If Yes. the following query will give you list of all objects in your SqlLite db.

    SELECT name FROM sqlite_master
    

    You can filter your records with type column further and see if it helps.

    -- N Baua

  • CaseCase USMember ✭✭✭
    Accepted Answer

    for anyone facing a similar issue, I did find a way to loop through the results of a query as a List<object[]>.
    The only issue is that you need to construct the SQL Query string yourself, and I couldn't figure out how to pass parameters.
    For example SELECT * FROM mytable WHERE col1 = ? AND col2 = ?
    I had to loop through the ?'s myself and replace them with the appropriate value (encapsulating with '' as needed)

        private List<object[]> RunSql(string sqlString, bool includeColumnNamesAsFirstRow)
        {
            var lstRes = new List<object[]>();
            SQLitePCL.sqlite3_stmt stQuery = null;
            try
            {
                stQuery = SQLite3.Prepare2(Connection.Handle, sqlString);
                var colLenght = SQLite3.ColumnCount(stQuery);
    
                if (includeColumnNamesAsFirstRow)
                {
                    var obj = new object[colLenght];
                    lstRes.Add(obj);
                    for (int i = 0; i < colLenght; i++)
                    {
                        obj[i] = SQLite3.ColumnName(stQuery, i);
                    }
                }
    
                while (SQLite3.Step(stQuery) == SQLite3.Result.Row)
                {
                    var obj = new object[colLenght];
                    lstRes.Add(obj);
                    for (int i = 0; i < colLenght; i++)
                    {
                        var colType = SQLite3.ColumnType(stQuery, i);
                        switch (colType)
                        {
                            case SQLite3.ColType.Blob:
                                obj[i] = SQLite3.ColumnBlob(stQuery, i);
                                break;
                            case SQLite3.ColType.Float:
                                obj[i] = SQLite3.ColumnDouble(stQuery, i);
                                break;
                            case SQLite3.ColType.Integer:
                                obj[i] = SQLite3.ColumnInt64(stQuery, i);
                                break;
                            case SQLite3.ColType.Null:
                                obj[i] = null;
                                break;
                            case SQLite3.ColType.Text:
                                obj[i] = SQLite3.ColumnString(stQuery, i);
                                break;
                            default:
                                throw new Exception("Unrecognized data type in SQLiteDatabase.cs RunSql()");
                        }
                    }
                }
                return lstRes;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                if (stQuery != null)
                {
                    SQLite3.Finalize(stQuery);
                }
            }
        }
    
Sign In or Register to comment.