SQLiteManager: Getting started

Preparations

First of all you'll need the dependency. You can either

• Pull it from NuGet (recommended): Install-Package TastyApps.Core.SQLiteManager -Version 2.0.0.1
• Pull from GitHub packages: dotnet add PROJECT package TastyApps.Core.SQLiteManager --version 2.0.0.1
• Clone and build this repository yourself

Database setup

Before you can work with your database you need to set it up, but this is done very easily. Using class and property attributes you are able to turn any object into a table!

In this example we have a class "User" we want to turn into a table, so we can save user data in our database.

class User
{
    public string Guid {get; set; }

    public string Name { get; set; }
	
    public DateTime Birthday { get; set; }
	
    public string Password { get; set; }
	
    public bool DarkMode {get; set; }
	
    public Image ProfilePicture {get; set; }
	
    // Constructors, methods, etc...
}

Here we have a class with 4 public properties, but we may also have methods, private fields and so on in our class. Also we do not want that data from our "ProfilePicture" property get saved into our table. This is where we will use attributes and the new DatabaseEntry class. There are multiple attributes available for properties, each of which configures the corresponding column:

• [SqlitePrimaryKey]: If you use the DatabaseEntry class (recommended) you will barely use this attribute. Defines the column as PRIMARY KEY
• [SqliteNotNull]: Defines the column for this property as NOT NULL
• [SqliteUnique]: Defines the column as UNIQUE
• [SqliteDefaultValue(dynamic value)]: Configure a default value for this column. value is the default value for the column.
• [SqliteIgnore]: Tells SQLiteManager to ignore this property, thus excluding it from column generation.

Also a very important attribute: [SqliteTable]. Attaching this to a class allows SQLiteManager to discover the class and automate generation. This attribute also has an optional parameter "tableName", which allows you to overwrite the default name picked by SQLiteManager. (Name of class in uppercase + plural)

So in our example we would need to change our "User" class accordingly:

[SqliteTable]
class User : DatabaseEntry<User>
{
    [SqliteUnique]
    public string Guid {get; set; }
	
    public string Name { get; set; }
	
    public DateTime Birthday { get; set; }
	
    [SqliteNotNull]
    public string Password { get; set; }
	
    SqliteDefaultValue(true)]
    public bool DarkMode {get; set; }
	
    [SqliteIgnore]
    public Image ProfilePicture {get; set; }
	
    public User(TableDefinition<User> table): base(table) { } // Default constructor; required by DatabaseEntry<T>
	
    // Constructors, methods, etc...
}

No matter how many objects you want to turn into a table, using this system allows for maximum flexibility!

Tip: SQLiteManager will automatically update your tables when you add or remove properties!

Getting a table object

To create new instances of a DatabaseEntry objects (in our case an instance of "User"), we need to pass the right table to the constructor. For this we simply pull the table from our database instance like this:

ITable userTable = Database.Instance.GetTable<User>();

User user new new User(userTable);

But we can also pull a table by using either indexing:

ITable tableWithIndex = Database.Instance[0]; // Returns the first table in the database
ITable tableWithName = Database.Instance["users"]; // Returns a table with the name "users"

Alternative way of constructing DB objects

As you've seen before our "User" class is based on the "DatabaseEntry" class, which requires passing a "TableDefinition" object to the base constructor. Since we can pull any table from the Database instance, we can simplify the constructor for our "User" class like this:

[SqliteTable]
class User : DatabaseEntry<User>
{
    // ...
	
    public User(): base(Database.Instance.GetTable<User>()) { } // Here we select the correct table for this class via the static database instance
	
    // Constructors, methods, etc...
}

Initializing the database

Once you're done setting up classes as database objects, we just need to initialize the database itself! The best place to do so is before your main code starts executing.

Example for console applications:

class Program
{
    private static string dbPath = "data.db";

    static void Main()
    {
        Database.Initialize(dbPath); // Initialize database

        Console.WriteLine("Hello world!");
    }
}

Example for WinForms applications:

class Program
{
    private static string dbPath = "data.db";

    [STAThread]
    static void Main()
    {
        Database.Initialize(dbPath); // Initialize database

        Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);
        Application.Run(new Form1());
    }
}

Example for WPF applications:

Note: Make sure to change your "App.xaml" build action to "Page" (select your "App.xaml" file -> Properties -> Build Action)

public partial class App : Application
{
    private static string dbPath = "data.db";

    [STAThread]
    static void Main()
    {
        Database.Initialize(dbPath); // Initialize database

        App app = new App()
        {
            ShutdownMode = ShutdownMode.OnMainWindowClose
        };
        app.InitializeComponent();
        app.Run();
    }
}

Once your program calls "Database.Initialize(...)" the database is created, or in case a database with the same name already exists, applies changes like added or removed columns/tables.

---

Next up: Defining relationships between tables