查询多个表 SQLite Windows 10 UWP [英] Query multiple tables SQLite Windows 10 UWP

查看:56
本文介绍了查询多个表 SQLite Windows 10 UWP的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在开发一个带有 SQLite 数据库的 Windows 10 UWP 应用程序.我需要从多个表中检索数据.我已经有了以下代码来从单个表中获取数据,但是需要一个与相关表的列的数量和名称完全相同的自定义对象.

I am developing a Windows 10 UWP app with a SQLite database. I need to retrieve data from multiple tables. I already have the following code to get data from a single table, but a custom object with the exact same amount and names of the columns of the table in question is needed.

public ObservableCollection<Employee> GetEmployees()
{
    using (var dbConn = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
    {
        List<Employee> myCollection = dbConn.Table<Employee>().ToList<Employee>();
        ObservableCollection<Employee> EmployeesList = new ObservableCollection<Employee>(myCollection);
        return EmployeesList;
    }
}

我也知道可以使用以下代码查询表,但仍然只能从单个表中查询.

I also know that it is possible to query a table using the following code, but still only from a single table.

public ObservableCollection<Question> GetQuestionsForAssessment(int assessment_id)
{
    using (var dbConn = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
    {
        List<Question> myCollection = dbConn.Query<Question>("select * from Question where AssessmentId = ?", assessment_id);
        ObservableCollection<Question> QuestionsList = new ObservableCollection<Question>(myCollection);
        return QuestionsList;
    }
}

那么有人知道我如何从多个表中查询吗?谢谢

So does anybody know how I can query from multiple tables? Thanks

推荐答案

没有什么可以阻止您使用联接编写查询:

There's nothing preventing you from writing queries with joins:

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;");
}

您确实需要一个包含来自多个表的字段的类来将结果映射到:

You do need to have a class with fields from multiple tables to map the result into:

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; }
}

或者,您可以尝试 SQLite-Net Extensions,它增加了对实体之间关系的支持.不过我从来没用过.

Alternatively you could try out SQLite-Net Extensions which adds support for relationships between entities. I've never used it, though.

这篇关于查询多个表 SQLite Windows 10 UWP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆