Creating an SQLite table programmatically

Question

I am creating a Xamarin Forms application. The application contains a button which creates a new list. The list's name and date is saved in one table but the list's contents must be stored in another table, which needs to be created using a query. I have been able to successfully insert records into the table that holds the names and dates of the lists using the following approach:

[Table("ToDoLists")]
public class ShoppingList : INotifyPropertyChanged
{
    private int _id;

    [PrimaryKey, AutoIncrement]
    public int Id
    {
        get
        {
            return _id;
        }

        set
        {
            _id = value;
            OnPropertyChanged(nameof(Id));
        }
    }

    private string _name;

    [NotNull]
    public string Name
    {
        get
        {
            return _name;
        }

        set
        {
            _name = value;
            OnPropertyChanged(nameof(Name));
        }
    }

    private string _date;

    [NotNull]
    public string Date
    {
        get
        {
            return _date;
        }

        set
        {
            _date = value;
            OnPropertyChanged(nameof(Date));
        }
    }

    private string _description;

    [NotNull]
    public string Description
    {
        get
        {
            return _description;
        }

        set
        {
            _description = value;
            OnPropertyChanged(nameof(Description));
        }
    }

    public event PropertyChangedEventHandler PropertyChanged;

    private void OnPropertyChanged(string propertyName)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

I have tried the following things:

• Pass on the list name as a table name in the [Table(tableName)] statement but the IDE tells me that only constant fields are allowed to be used, meaning that I can't dynamically specify a table name
• I have tried reading about SQLiteCommand but the examples I'm given use functions that aren't available in SQLite PCL (unless I've installed SQLite PCL incorrectly).

How exactly do I create a table using a query in SQLite PCL? Is it even possible? Any suggestions?

Answer 1

Take a look at SQLite-net:

SQLite-net is an open source, minimal library to allow .NET and Mono applications to store data in SQLite 3 databases. It was first designed to work with Xamarin.iOS, but has since grown up to work on all the platforms (Xamarin.*, .NET, UWP, Azure, etc.).

SQLite-net was designed as a quick and convenient database layer. Its design follows from these goals:

• Very easy to integrate with existing projects and runs on all the .NET platforms.
• Thin wrapper over SQLite that is fast and efficient. (This library should not be the performance bottleneck of your queries.)
• Very simple methods for executing CRUD operations and queries safely (using parameters) and for retrieving the results of those query in a strongly typed fashion.
• Works with your data model without forcing you to change your classes. (Contains a small reflection-driven ORM layer.)

First, create a SQLiteAsyncConnection to your database:

private SQLiteAsyncConnection database; 
database = new SQLiteAsyncConnection("YourDatabasePath");

Then you can use the method CreateTableAsync() to create your table:

await database.CreateTableAsync<ShoppingList>();

For adding data to the table, you can do something like this:

public async Task SaveShoppingObjects(List<ShoppingObjects> shoppingsObjects)
{
    await database.RunInTransactionAsync(tran =>
    {
        foreach (ShoppingObject s in shoppingObjects)
        {
            tran.InsertOrReplace(SqliteEntityFactory.Create(s));
        }
    });
}

SqliteEntityFactory.Create is a method which helps you creating the table element. It could look something like this:

public static ShoppingList Create(ShoppingObject s)
{
    ShoppingList slist = new ShoppingList();
    if (s == null)
    {
        return slist;
    }
    slist.Id = s.Id;
    slist.Name = s.Name;
    // etc...

    return slist;
}

If I understand your question right, that should make the trick!