Xamarin Forms, SQLite relationship between tables

Question

Hi Xamarin Forms Forum,

I have a question about the relationship between two tables using sqlite, for example if I have a table of employees, and another one with duties, I want to know how I can configure the tables so when I can insert specific employee I can add to that employee especif duties, and how can I retreive the data using MVVM,

Hope you can help me and understand my issue

Regards

Answer 1

Hi Amasu-3175,

Welcome to our Microsoft Q&A platform!

According to your pdf, there seems to be a one-to-one relationship between "ToDo" and "Detail". So you don't need any other configuration. Just create two tables and then create corresponding static classes for these two tables. Both tables have the same field "Name".

Here take "Detail" as an example.

public static class DetailService  
{  
    public static SQLiteAsyncConnection db;  
    static async Task Init()  
    {  
        if (db != null)  
            return;  
  
        var databasePath = Path.Combine(FileSystem.AppDataDirectory, $"data.db");  
        db = new SQLiteAsyncConnection(databasePath);  
        await db.CreateTableAsync<Detail>();  
    }  
  
    public static async Task AddDetail(string name, string d1, string d2)  
    {  
        await Init();  
        var detail = new Detail  
        {  
            Name = name,  
            Detail1 = d1,  
            Detail2 = d2  
        };  
        var id = await db.InsertAsync(detail);  
    }  
  
    public static async Task<Detail> GetDetail(string name)  
    {  
        await Init();  
        var detail = await db.Table<Detail>().FirstOrDefaultAsync(c => c.Name == name);  
        return detail;  
    }  
}  

Then declare a method to extract data from the two tables. And store it in an anonymous object.

public static async Task<object> GetAllFields(string name)  
{  
    Todo todo = await TodoService.GetToDo(name);  
    Detail detail = await DetailService.GetDetail(name);  
  
    var item = new { Name = name, Date= todo.Date, Detail1 = detail.Detail1, Detail2 = detail.Detail2 };  
    return item;  
}  

---

Update
Not clear why all items are updated. The name of the item should be specified when defining the method in the Service.
The following is part of my test code.

model class:

 class ItemsClass  
 {  
     [PrimaryKey]  
     public string Name { get; set; }  
     public string Date { get; set; }  
     public string ItemsString { get; set; }  
 }  

service class:

 class ItemClassService  
 {  
     public static SQLiteAsyncConnection db;  
  
     static async Task Init() {  // ... }  
     public static async Task AddItem(string name, string date, string itemsstring) {  //... }  
     public static async Task<ItemsClass> GetItem(string name)  
     {  
         await Init();  
         var item= await db.Table<ItemsClass>().FirstOrDefaultAsync(c => c.Name == name);  
         return item;  
     }  
     public static async Task UpdateItem(string name, string newitemstring)  
     {  
         await Init();  
         var item = await db.Table<ItemsClass>().FirstOrDefaultAsync(c => c.Name == name);  
         item.ItemsString += newitemstring;  
         await db.UpdateAsync(item);  
     }  
     public static async Task<IEnumerable<ItemsClass>> GetAllItems() { //... }  
 }  

DetailsPage.xaml(second page):

 <StackLayout>  
     <Label Text="{Binding Name}"/>  
     <Label Text="{Binding Date}"/>  
     <Label Text="{Binding ItemsString}"/>  
     <Entry x:Name="newItemString"/>  
     <Button Text="Add" Clicked="Button_Clicked"/>  
 </StackLayout>  

DetailsPage.xaml.cs:

 public string ItemName { get; set; }  
 public DetailsPage(string name)  
 {  
     InitializeComponent();  
     ItemName = name;  
 }  
      
 protected override async void OnAppearing()  
 {  
     base.OnAppearing();  
     BindingContext = await ItemClassService.GetItem(ItemName);  
 }  
      
 private async void Button_Clicked(object sender, EventArgs e)  
 {  
     await ItemClassService.UpdateItem(ItemName, newItemString.Text);  
 }  

Regards,
Kyle

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.