Set result of query to DataGrid or ListView by SQLite.NET

Question

Is it possible to set result of query directly to DataGrid (without mapping to object). I have some dynamic reports and I want to only display it in Grid, without set columns,etc.

Usually it'not problem because I "mapped" my data to an objects in model for example:

using (var db = new SQLiteConnection(new SQLite.Net.Platform.Generic.SQLitePlatformGeneric(), "zakupy.db"))
{
    listPerson = db.Table<Persons>().Where(x => x.Property == "P" && x.Status == 0).ToList();
}
lstPersons.DataContext = listPerson;

Answer 1

You need to map the results of the "select * from Events" query into an IEnumerable somehow.

You could of course use an SQLiteDataReader and add anonymous objects to the ItemsSource. Something like this:

System.Collections.ArrayList list = new System.Collections.ArrayList();
using (var db = new SQLiteConnection(new SQLite.Net.Platform.Generic.SQLitePlatformGeneric(), "zakupy.db"))
{
    db.Open();
    const string sql = "select * from Events";
    SQLiteCommand command = new SQLiteCommand(sql, db);
    SQLiteDataReader reader = command.ExecuteReader();
    while (reader.Read())
        list.Add(new { Name = reader["name"].ToString() });
}
lstPersons.ItemsSource = list;

I don't really see why you would want to do this instead of binding to a generic IEnumerable<T> though.

Anyway, you can set or bind the ItemsSource of a DataGrid or ListView to any IEnumerable. It doesn't really matter how you choose to populate this sequence as far as the control is concerned.

Answer 2

Ok, so this is how I figure it out:

 string ConString = "sale.db";
 string passedquery = "select * from Events";
        DataTable dt = new DataTable();
        using (SQLiteConnection con = new SQLiteConnection(ConString))
        {
            try
            {
                using (var statement = con.Prepare(passedquery))
                {
                    for (int i = 0; i < statement.ColumnCount; i++)
                    {
                        dt.Columns.Add(statement.ColumnName(i));
                    }
                    while (statement.Step() == SQLiteResult.ROW)
                    {

                        DataRow dr;
                        dr = dt.NewRow();

                        for (int i = 0; i < statement.ColumnCount; i++)
                        {
                            dr[i] = statement[i];
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }

            catch(SQLitePCL.SQLiteException)
            {

                MessageBox.Show("Nieprawidłowe zapytanie");
            }             
        }
        dtgCustomReport.DataContext = dt.DefaultView;

But I still don't know if this simpliest way:)