xamarin-forms sqlite multi tables

Question

Cannot work with more than one table. What to do to get to work with two, three or more tables?

Visual Studio >>> Xamarin-Forms

/// I think maybe this code needs to be fixed somehow.
/// this code is placed in App.cs (up)
static Data.TableTwo tabletwo;
static Data.TableOne tableone;

/// crashed
 public Task<int> SaveItemAsync(TableTwo item)
        {
            if (item.ID != 0)
            {
                return tabletwo.UpdateAsync(item);
            }
            else
            {
                return tabletwo.InsertAsync(item);
            }
        } 

/// ***I think maybe this code needs to be fixed somehow.
/// this code is placed in App.cs (down below)
public static Data.TableTwo tabletwo
        {
            get
            {
                if (datadistance == null)
                {
                    tabletwo = new Data.TableTwo(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "TodoSQLite.db3"));
                }
                return tabletwo;
            }
        }

/// ***I think maybe this code needs to be fixed somehow.
/// this code is placed in App.cs (down below)
        public static Data.TableOne tableone
        {
            get
            {
                if (tableone == null)
                {
                    tableone = new Data.TableOne(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "TodoSQLite.db3"));
                }
                return tableone;
            }
        }

above code works correctly. When the code above is called. Application falls.

I have two tables. With one table when the user works (saves and deletes data), then everything works. If the user starts working with another table (save data) the application crashes.

!!!application tree!!! DataBase(folder) TableTwo(file) TableOne(file) Models(folder) TableTwo(file) TableOne(file)

Everything was done according to the code from the article https://learn.microsoft.com/en-us/xamarin/xamarin-forms/app-fundamentals/databases#using-sqlite

In fact, I just copied the code a second time, and pasted into the project. - this is what I have done creating the second table and working with it (deleting insert data)

Answer 1

Let's assume your two tables are of types Load and Category and your database is of type MyDatabase You might want to keep a single connection to SqlLite inside the MyDatabase class and add the methods to interact with your tables as follows:

public class MyDatabase
{
    private readonly SQLiteAsyncConnection _connection;

    public MyDatabase():this(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "MyDatabase.db3"))
    {
    }

    internal MyDatabase(string dbPath)
    {
        _connection = new SQLiteAsyncConnection(dbPath);

        _connection.CreateTableAsync<Load>().Wait();
        _connection.CreateTableAsync<Category>().Wait();
    }

    public Task<List<Load>> GetLoads() => 
        _connection.Table<Load>().ToListAsync();

    public Task<List<Category>> GetCategories() => 
        _connection.Table<Category>().ToListAsync();

    public Task<Load> GetLoad(int id) => 
        _connection.Table<Load>().Where(i => i.Id == id).FirstOrDefaultAsync();

    public Task<int> SaveLoad(Load item) =>
        item.Id != 0 ? _connection.UpdateAsync(item) : _connection.InsertAsync(item);

    public Task<int> DeleteLoad(Load item) => 
        _connection.DeleteAsync(item);
}

Here is a good sample: https://github.com/xamarin/xamarin-forms-samples/blob/master/Todo/Todo/Data/TodoItemDatabase.cs, but it contains a single table 😊