Is it possible to return dynamic objects or Dataset from a Sqlite Query?

Question

I am using Sqlite.Net in my Xamarin.Forms application. So far it has been great at returning lists of objects if my object is a class like so:

SqliteDatabase.Connection.Query<Customer>("Select * from Customers");

I would now like to return the equivalent of a DataSet dynamically from my query

SqliteDatabase.Connection.Query("Select * from Customers inner join Calls on Customers.Id=Calls.CustomerId")

Now from the second query I would like to return a DataSet instead of a list of objects. I know I could create a new object which combines the columns of Customers and Calls but I don't want to have to create objects every time I want to query the database.

Is it possible to just dynamically return a Dataset or Object?

Answer 1

In the end I actually managed to come up with a method that will run any query and return the rows as items in the list and the columns as objects in the array:

    public List<object[]> RunSql(string sqlString, bool includeColumnNamesAsFirstRow)
    {
        var lstRes = new List<object[]>();
        SQLitePCL.sqlite3_stmt stQuery = null;
        try
        {
            stQuery = SQLite3.Prepare2(fieldStrikeDatabase.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 columnType = SQLitePCL.raw.sqlite3_column_decltype(stQuery, i);

                     switch (columnType)
                     {
                         case "text":
                              obj[i] = SQLite3.ColumnString(stQuery, i);
                              break;
                         case "int":
                               obj[i] = SQLite3.ColumnInt(stQuery, i);
                               break;
                         case "real":
                               obj[i] = SQLite3.ColumnDouble(stQuery, i);
                               break;
                         case "blob":
                               obj[i] = SQLite3.ColumnBlob(stQuery, i);
                               break;
                         case "null":
                               obj[i] = null;
                               break;
                      }
                }
            }
            return lstRes;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            if (stQuery != null)
            {
                SQLite3.Finalize(stQuery); 
            }
        }
    }

Answer 2

Unlike @Fabian Monkemoller, i was unable to get @User1's code to work straight away. This is a modified version that make use of nullable reference types and method-nesting to seperate the main-code from the try-catch block:

     public static object?[][]? ToDataSet(this SQLiteConnection sqlConnection, string query , bool includeColumnNamesAsFirstRow = true)
    {
        var stQuery = SQLite3.Prepare2(sqlConnection.Handle, query );
        var colLength = SQLite3.ColumnCount(stQuery);
        try
        {
            return SelectRows().ToArray();
        }
        catch (Exception e)
        {
            return null;
        }
        finally
        {
            if (stQuery != null)
            {
                SQLite3.Finalize(stQuery);
            }
        }

        IEnumerable<object?[]> SelectRows()
        {
            if (includeColumnNamesAsFirstRow)
            {
                yield return SelectColumnNames(stQuery, colLength).ToArray();
            }

            while (SQLite3.Step(stQuery) == SQLite3.Result.Row)
            {
                yield return SelectColumns(stQuery, colLength).ToArray();
            }

            static IEnumerable<object> SelectColumnNames(SQLitePCL.sqlite3_stmt stQuery, int colLength)
            {
                for (int i = 0; i < colLength; i++)
                {
                    yield return SQLite3.ColumnName(stQuery, i);
                }
            }

            static IEnumerable<object?> SelectColumns(SQLitePCL.sqlite3_stmt stQuery, int colLength)
            {
                for (int i = 0; i < colLength; i++)
                {
                    var x = SQLitePCL.raw.sqlite3_column_decltype(stQuery, i);
                    yield return x switch
                    {
                        "text" => SQLite3.ColumnString(stQuery, i),
                        "integer" => SQLite3.ColumnInt(stQuery, i),
                        "bigint" => SQLite3.ColumnInt64(stQuery, i),
                        "real" => SQLite3.ColumnDouble(stQuery, i),
                        "blob" => SQLite3.ColumnBlob(stQuery, i),
                        "null" => null,
                        _ => throw new Exception($"Unexpected type encountered in for query {stQuery}")
                    };
                }
            }
        }
    }

Answer 3

SQLite.NET PCL is a .NET wrapper around sqlite.

Therefore you can query similar to EF by using a join in in LINQ or Lambda than in the Query. The wrapper will handle the conversion to sqlite query for you.

You can then return a new datatype of the joined type or a dynamic type.

Note : Joins are not directly supported in sqlite (more info) and work around is mentioned here.

Sample code:

var conn = new SQLiteConnection(sqlitePlatform, "foofoo");
var query = from customer in conn.Table<Customers>().ToList()
            join call in conn.Table<Calls>().ToList()
                         on customer.ID equals call.CustomerId                
            select new { Customer = customer , Calls = call };

Lambda version:

conn.Table<Customer>().ToList().Join
(conn.Table<Call>().ToList(),
customer => customer.Id,
call => call.CustomerId, 
(customer, call) => new { Customer = customer, Calls = call });

Answer 4

It sounds like what you want to do is essentially recreate ADO.NET. When you say "DataSet", I'm guessing that you are talking about ADO.NET. This probably means that you don't want to use the ORM functionality built in to the SQLite.Net library.

I have created this version of the library that will allow you to do flat table reads from an SQLite database. It means that you CAN read the data in to an ADO.NET dataset if you like.

https://github.com/MelbourneDeveloper/SQLite.Net.Standard

Answer 5

thank u so much user1! works perfect. here is just an example how to use ur method:

var objects = mySQLiteConnection.RunSql("SELECT * FROM Persons", true);

// ColumnNames
List<string> ColumnNames = new List<string>();
for (int column = 0; column < objects[0].Length; column++)
{
    if (objects[0][column] != null) spaltennamen.Add((string)objects[0][column]);
}

// RowValues
for (int row = 1; row < objects.Count; row++)
{
    for (int column = 0; column < objects[row].Length; column++)
    {
        if (objects[row][column] != null) System.Diagnostics.Debug.WriteLine(spaltennamen[column] + " : " + objects[row][column]);
    }
}