Name table in SQLite-net

Question

I'm building a Windows 8 C#/XAML app that uses SQLite as a storage database, and I'm trying to create multiple tables using the SQLite-net syntax.

From what I've researched so far, a table is created based off of a class. First, I've created an "Account" class via:

public class Account
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string Name { get; set; }
    public string Type { get; set;}
}

And then create a table and enter in initial data later on in the code via:

    private static readonly string _dbPath =
        Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.sqlite");


        using (var db = new SQLite.SQLiteConnection(_dbPath))
        {
            db.CreateTable<Account>();

            db.RunInTransaction(() =>

               db.Insert(new Account()
                    {
                        Name = "MyCheckingAccount",
                        Type = "Checking",
                    })
                    );
        }

I want to create multiple account tables, but the db.CreateTable<Account>() syntax just creates a table and the data is inserted into the columns with db.Insert(). I don't see where to enter the name of the table itself.

How do I create multiple tables, i.e. one named "BusinessAccounts" and another "PersonalAccounts" based off of the Account class?

Is there a way to do this with SQLite-net? Or do I need to write out the SQLite command explicitly somehow?

Answer 1

Sqlite-Net uses the class name to create the table, as well as to update the data. To do what you want, you'll need to create separate classes. One way to get around repeating common fields is to use inheritance:

public class Account
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string Name { get; set; }
    public string Type { get; set;}
}

public class BusinessAccounts : Account { }

public class PersonalAccounts : Account { }

To create tables:

db.CreateTable<BusinessAccounts>();
db.CreateTable<PersonalAccounts>();

To insert data:

db.Insert(new BusinessAccounts() {...});
db.Insert(new PersonalAccounts() {...});

Just a note that the above code is untested. You'll want to make sure that the tables are created correctly (e.g. with the proper primary key and autoincrement field).