Sqlite-net: ExecuteQuery for unknown # of fields

[tofutim]

I have a certain scenario where I generate SQL statements on the fly - the fields inside may vary. Is there anyway I can query for this without building a class ahead of time?

I'm looking for ExecuteQuery() without the .

[tofutim]

I added the following to my version of SQLite. If anyone has any comments on improvements or gotchas, they are much appreciated. To SQLiteCommand:

    public List<Dictionary<string, object>> ExecuteQuery()
    {
        return ExecuteDeferredQuery().ToList();
    }

   public IEnumerable<Dictionary<string, object>> ExecuteDeferredQuery()
    {
        if (_conn.Trace)
        {
            Debug.WriteLine("Executing Query: " + this);
        }

        var stmt = Prepare();
        try
        {
            var cols = new string[SQLite3.ColumnCount (stmt)];

            for (int i = 0; i < cols.Length; i++) {
                var name = SQLite3.ColumnName16 (stmt, i);
                cols [i] = name;
            }

            while (SQLite3.Step(stmt) == SQLite3.Result.Row)
            {
                var obj = new Dictionary<string, object>();
                for (int i = 0; i < cols.Length; i++)
                {
                    var colType = SQLite3.ColumnType(stmt, i);

                    Type targetType;
                    switch (colType)
                    {
                        case SQLite3.ColType.Text:
                            targetType = typeof(string);
                            break;
                        case SQLite3.ColType.Integer:
                            targetType = typeof(int);
                            break;
                        case SQLite3.ColType.Float:
                            targetType = typeof(double);
                            break;
                        default:
                            targetType = typeof(object);
                            break;
                    }

                    var val = ReadCol(stmt, i, colType, targetType);
                    obj.Add(cols[i], val);
                }
                OnInstanceCreated(obj);
                yield return obj;
            }
        }
        finally
        {
            SQLite3.Finalize(stmt);
        }
    }

[marcucio]

Sorry if this seems ignorant but I'm new to c#... how would you call this function, currently I am using:

var results = db.Query<Dictionary<string, object>>(query, query_params);

And it only calls this function not yours:

    public List<T> ExecuteQuery<T> () where T : new()
    {
        return ExecuteDeferredQuery<T>(_conn.GetMapping(typeof(T))).ToList();
    }

[marcucio]

Just an update, to work around this I made this function to call your functions:

    public List<Dictionary<string, object>> Query2(string query, params object[] args)
    {
        var cmd = CreateCommand(query, args);
        return cmd.ExecuteQuery();
    }

and call it with

var results = db.Query2(query, query_params);

maybe there is a better way to do this?

[jameshowe]

This was exactly what I was looking for! Created a new issue #255 before I came across this issue. If you have been using this code without issue for while maybe you could use your post as a pull request to get it into master?

[rgl]

Why did you close this issue @tofutim ?

@praeclarum Can you consider this? Maybe using dynamics?

[torfeld6]

Hi @tofutim. Thanks you for the great code

[dazinator]

Is there anyone that can get this into a new NuGet release?

[praeclarum]

Yes this does seem like a reasonable feature. I think I would prefer dynamic over dictionary though.

[MKuckert]

Should be pretty simple to leverage the ExpandoObject

[igium]

@tofutim, I love you, man!

Putting the code in a partial SQLiteCommand implementation safely extends the class without the risk of loosing the changes after the sqlite.net module update/reinstall.

[bill2004158]

@praeclarum

how about this? if no problem, can you add to master branch?

public partial class SQLiteConnection
{
    public List<ExpandoObject> Query(string query, params object[] args)
    {
        var cmd = CreateCommand (query, args);
        return cmd.ExecuteQuery();
    }
}

public partial class SQLiteCommand
{
    public List<ExpandoObject> ExecuteQuery() {
        return ExecuteDeferredQuery().ToList();
    }

    private IEnumerable<ExpandoObject> ExecuteDeferredQuery () {
        if (_conn.Trace) {
            _conn.Tracer?.Invoke ("Executing Query: " + this);
        }

        var stmt = Prepare ();
        try {
            var cols = new string[SQLite3.ColumnCount (stmt)];

            for (var i = 0; i < cols.Length; i++) {
                var name = SQLite3.ColumnName16 (stmt, i);
                cols[i] = name;
            }

            while (SQLite3.Step (stmt) == SQLite3.Result.Row) {
                var obj = new ExpandoObject() as IDictionary<string, object>;
                for (var i = 0; i < cols.Length; i++) {
                    var colType = SQLite3.ColumnType (stmt, i);

                    var val = ReadCol (stmt, i, colType, MapToCSharpType(colType));
                    obj[cols[i]] = val;
                }
                OnInstanceCreated (obj);
                yield return (ExpandoObject) obj;
            }
        }
        finally {
            SQLite3.Finalize (stmt);
        }
    }

    private Type MapToCSharpType (SQLite3.ColType colType)
    {
        Type targetType;
        switch (colType) {
            case SQLite3.ColType.Text:
                targetType = typeof (string);
                break;
            case SQLite3.ColType.Integer:
                targetType = typeof (int);
                break;
            case SQLite3.ColType.Float:
                targetType = typeof (double);
                break;
            case SQLite3.ColType.Blob:
                targetType = typeof (byte[]);
                break;
            default:
                targetType = typeof (object);
                break;
        }

        return targetType;
    }
}

[lapoPaolacci]

with this method if I do complex query is very slow and device crash