CODEWITHSUNDEEP
c#listsqlitewin-universal-app
TexMoonbeam
TexMoonbeam

Reputation: 23

How to populate a List from results of sqlite query in Universal Windows App

I have a List:

public class DateAmountList
    {
        public string Date { get; set; }
        public int Amount { get; set; }
    }

List<DateAmountList> itemsList1 = new List<DateAmountList>();

I have an sqlite query:

var getmonthsquery = "SELECT Date, SUM(Amount) FROM incomeamounts GROUP BY Date";

The result looks like you'd expect for the query, something like:

Jan, 200
Feb, 250
Mar, 700

etc...

I'm not sure why in Windows Universal Apps, but sqliteDataReader is not available even though I've installed the nuget package, so I'd like an alternative way to populate the list with the query results and also the best way to 'execute' the query.

Upvotes: 0

Views: 960

Answers (2)

Jerry Li
Jerry Li

Reputation: 1042

so I'd like an alternative way to populate the list with the query results and also the best way to 'execute' the query.

I am using SQLite.Net-PCL to implement SQLite Local Storage in Universal Windows App:

// get the total amount for each date
public static List<DateAmountList> GetTotalAmount()
{
    List<DateAmountList> results = new List<DateAmountList>();
    using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DBPath))
    {
        results = conn.Query<DateAmountList>("SELECT Date, SUM(Amount) AS 'Amount' FROM DateAmountList GROUP BY Date");
    }
    return results;
}

Here is the entire sample for your reference, and following is the output:

enter image description here

Upvotes: 1

Andrei Ashikhmin
Andrei Ashikhmin

Reputation: 2451

I think if you prefer to work with queries directly, you should take a look at SQLitePCL library. Executing query and populating a list is pretty simple:

var results = new List<DateAmountList>();

using (var statement = this.connection.Prepare(
    "SELECT Date, SUM(Amount) FROM incomeamounts GROUP BY Date"))
{
    while (statement.Step() == SQLiteResult.ROW)
    {
        var dateAmount = new DateAmountList
        {
            Date = DateFromInternalValue(statement[0]),     // This depends on what format you use to store your date
            Amount = (long)statement[1],                    // Could also be: Amount = statement.GetInteger(1),
        };

        results.Add(dateAmount);
    }
}

Here are more samples of working with SQLitePCL.

As opposite approach, you can try sqlite-net and get nice LINQ-like syntax for working with your sqlite database.

Upvotes: 0

Related Questions