如何创建与 SQLite 的关系(外键) [英] How to create relationships with SQLite (Foreign key)
问题描述
我正在尝试在 SQLite 中创建与我的表的关系(我目前正在使用 Xamarin.Forms)，但我没有得到预期的结果，添加了数据注释 [ForeignKey(typeof(UserLocal))]但我并没有在我的 BD 中建立关系，这是怎么回事?我的 BD 只做索引关系，但没有将它们与外键相关
I'm trying to create relationships with my tables in SQLite (I'm currently working with Xamarin.Forms), but I'm not getting the expected result, adding the Data Annotations [ForeignKey(typeof(UserLocal))]
But I'm not creating the relationship in my BD, what's going on? my BD is only doing the indexed relationships, but it is not relating them to foreign keys
为了连接到我的数据库，我创建了一个接口来获取 Android 和 iOS 中的路由，然后管理我的 INSERT、UPDATE、DELETE 等......我从 DataService.CS 服务中完成.
For the connection to my database, I created an interface which gets the route in Android and iOS, and then to manage my INSERT, UPDATE, DELETE, etc ... I do it from the DataService.CS service.
安卓:
[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");
}
}
}
DATASERVICE.CS:
DATASERVICE.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 除了建立连接之外，还创建了我想在其中生成关系的表，数据模型如下...
The DataService in addition to making the connection creates the tables in which I want to generate the relationship, The data model are the following...
USERLOCAL.CS:
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; }
}
COMPANIE.CS:
COMPANIE.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; }
}
接下来，如何在我的表中插入记录的代码如下所示，我认为在这部分我错了，因为我无法创建关系
Next, the code of how to insert the records in my tables is shown below, I think it is in this part where I am wrong, since I am not able to create the relationships
VIEWMODEL.CS:
VIEWMODEL.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，对我有什么帮助吗?
Why are these relationships not generated? How can I relate my tables to SQLite? what am I doing wrong? I am working with Xamarin.Forms with an MVVM architectural pattern, any help for me?
推荐答案
如果您使用的是 SQLite.Net.Extensions 用于创建关系，然后您需要像这样设置 CascadeOperations :
If you are using SQLite.Net.Extensions for creating the relationships then you need to set the CascadeOperations like so:
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 像这样创建关系:
The other thing is if you are using this library then you can insertWithChildren to create the relationship like so:
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);
}
这篇关于如何创建与 SQLite 的关系(外键)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
