UWP Custom Sqlite Query to JSON

Question

I'm searching for a way to Execute Custom SQL Queries and to provide the result in JSON. Normally you have to provide a Class for the Query result e.g.

var query = dbConn.Query<ClassTypes>("Select a as key, b as value FROM table WHERE id = ?", new object[] { ObjectID });

But in my case, I don't know the SQL Statement, because its provided by an external JavaScript from a Webview.

This Webview might ask my application to Execute

Select a.col1 as foo,b.col1, a.col2 FROM table1 a INNER JOIN table2 b ON a.id=b.aid

And wants me to return:

foo:xxx
col2:yyy

Which columns are "asked" by the SQL Statement is completely free, or which aliases are used, I just want to execute the Statement an return key value pairs with the aliases or column names and the values in a JSON (for each row).

So I'm not able to prepare a custom Class for the Query, because I don't know the format of the SQL Query.

Does anyone have an idea?

Answer 1

I just want to execute the Statement an return key value pairs with the aliases or column names and the values in a JSON (for each row).

For your scenario, You could use SqlDataReader to approach, SqlDataReader contains GetName method that could use to get the column name as key, and it also contains GetSqlValue method that could retrieve column's value. If you can't confirm the field count, you could also use FieldCount to get current reader 's field counts

For example

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    if (conn.State == System.Data.ConnectionState.Open)
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = GetProductsQuery;
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                var list = new List<Dictionary<string, object>>();
                while (reader.Read())
                {
                    var dict = new Dictionary<string, object>();
                    var i = 0;
                    do
                    {
                        var key = reader.GetName(i);
                        var value = reader.GetSqlValue(i);
                        dict.Add(key, value);
                        i++;
                    } while (i < reader.FieldCount);
                    list.Add(dict);       
                }
            }
        }
    }
}

For more detail please refer this document.