时间戳

首页 > 解决方案 > 如何使用 SQLite（外键）创建关系

c# - 如何使用 SQLite（外键）创建关系

问题描述

我正在尝试在 SQLite 中创建与我的表的关系（我目前正在使用 Xamarin.Forms），但我没有得到预期的结果，添加了数据注释 [ForeignKey(typeof(UserLocal))] 但我没有在我的 BD 中创建关系，这是怎么回事？我的 BD 只做索引关系，但没有将它们与外键相关联

浏览器 SQLite

为了连接到我的数据库，我创建了一个接口，该接口在 Android 和 iOS 中获取路由，然后管理我的 INSERT、UPDATE、DELETE 等……我从 DataService.CS 服务中完成。

安卓：

[assembly: Xamarin.Forms.Dependency(typeof(PathService))]
namespace AppValora.Droid.Implementation
{
    public class PathService : IPathService
    {
        public string GetDatabasePath()
        {              
            string path = Android.OS.Environment.ExternalStorageDirectory.AbsolutePath;
            var directoryPath = Path.Combine(path, "Valora/Databases");
            if (!Directory.Exists(directoryPath))
            {
                try
                {

                    Directory.CreateDirectory(directoryPath);
                }
                catch (Exception ex)
                {

                }
            }
            return Path.Combine(directoryPath, "Valora.db3");
        }
    }
}

IOS：

[assembly: Dependency(typeof(PathService))]
namespace AppValora.iOS.Implementation
{
    public class PathService : IPathService
    {
        public string GetDatabasePath()
        {
            string docFolder = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            string libFolder = Path.Combine(docFolder, "..", "Library");

            if (!Directory.Exists(libFolder))
            {
                Directory.CreateDirectory(libFolder);
            }

            return Path.Combine(libFolder, "Valora.db3");
        }
    }
}

数据服务.CS：

#region Servicios
private SQLiteAsyncConnection connection;
private DialogService dialogService;       
#endregion

#region Constructor
public DataService()
{
    dialogService = new DialogService();
    OpenOrCreateDB();
}
#endregion

private async Task OpenOrCreateDB()
{
    var status = await CrossPermissions.Current.CheckPermissionStatusAsync(Plugin.Permissions.Abstractions.Permission.Storage);

    if (status != Plugin.Permissions.Abstractions.PermissionStatus.Granted)
    {
        if (await CrossPermissions.Current.ShouldShowRequestPermissionRationaleAsync((Plugin.Permissions.Abstractions.Permission.Storage)))
        {
            await dialogService.ShowMessage("!ATENCIÓN!", "Valora necesita el permiso de archivos para este proceso.");
        }

        var results = await CrossPermissions.Current.RequestPermissionsAsync((Plugin.Permissions.Abstractions.Permission.Storage));
        //Best practice to always check that the key exists
        if (results.ContainsKey(Plugin.Permissions.Abstractions.Permission.Storage))
            status = results[Plugin.Permissions.Abstractions.Permission.Storage];
    }

    if (status == Plugin.Permissions.Abstractions.PermissionStatus.Granted)
    {
        //CONSULTO PATH
        var databasePath = DependencyService.Get<IPathService>().GetDatabasePath();
        //CREO LA CONEXION
        this.connection = new SQLiteAsyncConnection(databasePath);
        //CREACION DE TABLAS
        await connection.CreateTableAsync<UserLocal>().ConfigureAwait(false);
        await connection.CreateTableAsync<Companie>().ConfigureAwait(false);
    }
    else if (status != Plugin.Permissions.Abstractions.PermissionStatus.Unknown)
    {

    }    

    public async Task Insert<T>(T model)
    {
        await this.connection.InsertAsync(model);
    }
}

除了建立连接之外，DataService 还创建了我要在其中生成关系的表，数据模型如下......

USERLOCAL.CS：

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

    public int IdLogin { get; set; }
    public string Token { get; set; }             
    public string Nombre { get; set; }
    public string Rut { get; set; }
    public bool Recordado { get; set; }
    public string Password { get; set; }

    [OneToMany]
    public List<Companie> Companies { get; set; }
}

公司.CS：

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

    public int IdLogin { get; set; }
    public string Nombre { get; set; }
    public bool Principal { get; set; }
    public bool ExternalCorp { get; set; }
    public bool IsCorporate { get; set; }

    [ForeignKey(typeof(UserLocal))]
    public int IdUser { get; set; }

    [ManyToOne]
    public UserLocal UserLocal { get; set; }
}

接下来，如何在我的表中插入记录的代码如下所示，我认为这是我错的部分，因为我无法创建关系

视图模型.CS：

ListaCompanie.Clear();          

// I WALK THE NUMBER OF COMPANIES THAT I WANT TO ADD
foreach (var item in loginResponse.Companies)
{
    var companie = new Companie
    {
        IdLogin = item.Id,
        Nombre = item.Name,
        ExternalCorp = item.ExternalCorp,
        IsCorporate = item.IsCorporate,
        Principal = item.Principal,
        //CLAVE FORANEA
        IdUser = loginResponse.Id,
    };

    ListaCompanie.Add(companie);

    await dataService.Insert(companie);
}

var user = new UserLocal
{
        IdLogin = loginResponse.Id,
        Nombre = loginResponse.Name,
        Recordado = Settings.Recordado,
        Rut = loginResponse.Rut,
        Token = loginResponse.Token,
        Password = GetSHA1(Settings.Password),
        Companies = ListaCompanie,
};

await dataService.Insert(user);

为什么没有生成这些关系？如何将我的表与 SQLite 关联起来？我究竟做错了什么？我正在使用具有 MVVM 架构模式的 Xamarin.Forms，对我有什么帮助吗？

标签： c#xamarinxamarin.formssqlite

解决方案

如果您使用SQLite.Net.Extensions创建关系，那么您需要设置CascadeOperations如下：

public class UserLocal
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public int IdLogin { get; set; }
    public string Token { get; set; }             
    public string Nombre { get; set; }
    public string Rut { get; set; }
    public bool Recordado { get; set; }
    public string Password { get; set; }

    // THIS BIT HERE 
    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<Companie> Companies { get; set; }
}

另一件事是，如果您正在使用这个库，那么您可以insertWithChildren像这样创建关系：

var user = new UserLocal
{
    IdLogin = loginResponse.Id,
    Nombre = loginResponse.Name,
    Recordado = Settings.Recordado,
    Rut = loginResponse.Rut,
    Token = loginResponse.Token,
    Password = GetSHA1(Settings.Password),
    Companies = ListaCompanie,
};

// I WALK THE NUMBER OF COMPANIES THAT I WANT TO ADD
foreach (var item in loginResponse.Companies)
{
    var companie = new Companie
    {
        IdLogin = item.Id,
        Nombre = item.Name,
        ExternalCorp = item.ExternalCorp,
        IsCorporate = item.IsCorporate,
        Principal = item.Principal,
        //CLAVE FORANEA
        UserLocal = user,
        // You dont need to set this as it will be assigned in InsertWithChildren
        // IdUser = loginResponse.Id,
    };

    ListaCompanie.Add(companie);

    await dataService.InsertWithChildren(companie);
}

推荐阅读