UWP SQLite query result class contains another class

I know that we can do a multiple tables query, like this:

using (var db = new SQLiteConnection(new SQLitePlatformWinRT(), App.DB_PATH))
{
    var result = db.Query<PersonWithAddress>(
        @"SELECT Person.Id, Person.Name, Person.Surname, 
            Address.Street, Address.City, Address.Country
        FROM Person INNER JOIN Address ON Person.AddressId = Address.Id;");
}

private class PersonWithAddress
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

*Reference from Query multiple tables SQLite Windows 10 UWP

But how about I need the result class PersonWithAddress should be :

private class PersonWithAddress
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public Address mAddress { get; set}
}

private class Address {
    public string Street { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

I'm looking for a way of DataReader, but it's seem SQLite on UWP doesn't support it.

Answers


Here is a Portable Class Library for SQLite that provides method resembles the DataReader.

To use this library, we can install it form NuGet and then use it like following:

List<PersonWithAddress> PersonWithAddressList = new List<PersonWithAddress>();

using (var connection = new SQLitePCL.SQLiteConnection(DB_PATH))
{
    using (var statement = connection.Prepare(@"SELECT Person.Id, Person.Name, Person.Surname, Address.Street, Address.City, Address.Country FROM Person INNER JOIN Address ON Person.AddressId = Address.Id;"))
    {
        while (statement.Step() == SQLitePCL.SQLiteResult.ROW)
        {
            var personWithAddress = new PersonWithAddress();
            personWithAddress.Id = Convert.ToInt32(statement[0]);
            personWithAddress.Name = (string)statement[1];
            personWithAddress.Surname = (string)statement[2];
            personWithAddress.mAddress.Street = (string)statement[3];
            personWithAddress.mAddress.City = (string)statement[4];
            personWithAddress.mAddress.Country = (string)statement[5];

            PersonWithAddressList.Add(personWithAddress);
        }
    }
}

For more information, you can refer to this blog: The new Portable Class Library for SQLite.

Another way for multiple tables query is using SQLite-Net Extensions, you can specify the relationships in the entities. For example, in your project, add a reference to SQLiteNetExtensions NuGet package. Also available with Async support in SQLiteNetExtensions.Async NuGet package. Then change your class like following:

public class Person
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [ForeignKey(typeof(Address))]
    public int AddressId { get; set; }

    public string Name { get; set; }
    public string Surname { get; set; }

    [ManyToOne]
    public Address mAddress { get; set; }
}

public class Address
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Street { get; set; }

    public string City { get; set; }
    public string Country { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<Person> Persons { get; set; }
}

After this, we can use following code to get all persons with address:

using (var connection = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DB_PATH))
{
    List<Person> PersonsWithAddress = connection.GetAllWithChildren<Person>();
}

Need Your Help

Code highlighting and autocomplete gone in Xcode

xcode cocoa xcode4 xcode4.2 xcode4.3

I am working in a team and recently we have lost all of our syntax highlighting in our Xcode project. It is a problem across all of our environments, so it's not a problem with the local machine....