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?

[gartmeier]

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

[ghost]

@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

[rogerskk]

I have a select query that I need to use a dynamic type with. I am trying
var result= _database.Query<Dictionary<string,object>>(query);
This returns the correct number in the list, but the keys and values are blank. Is there a different way I should be running this to get the dynamic results?