Tips how to deal with SQLite on Windows 10 & UWP (1)

Microsoft.Data.Sqlite namespace

When you want to use a relational database easily on your UWP application, it’s easy to use SQLite. The SQLite management package is maintained on Nuget by Microsoft, and you can use this with the Microsoft.Data.Sqlite.

But, SQLite has been developed to be a lightweight and highly stable, it’s very conservative for adopting extended SQL statements. So, datetime information has been just TEXT data, not as a primitive data type. Date and Time functions are provided to insert data into SQLite and selecting data from it, which substitute some complex extended SQL statements. For example, when you create a table called NOTIFICATION and set its Time field as a primary key, inserting data and retrieving data from/into SQLite are carried out by the datetime function (In this case, datetime datum is configured as localtime, but SQLite stores datetime data as UTC. For more information, check it out at another many blogs! 🙂

CREATE TABLE NOTIFICATION (Time TEXT NOT NULL PRIMARY KEY,Level INTEGER NOT NULL,Name TEXT NOT NULL,Description TEXT NOT NULL)
INSERT INTO NOTIFICATION VALUES(datetime('now', 'localtime'), 10, "test", "This is a test.");
SELECT datetime(Time) FROM NOTIFICATION;

Microsoft.Data.Sqlite automatically convert incompatible data into SQLite’s primitive data…!

I’m not sure it’s very convenient for us, but Microsoft.Data.Sqlite automatically convert the data, the type of which isn’t supported as a primitive data type when you insert them. When you try to retrieve them from SQLite, on the other hand, the GetDateTime method is provided in the QueryReader class. You can convert the data in SQLite into a DateTime class with this method. I think it isn’t so intuitive, especially when you try to insert some data into SQLite, but this is the design right now… After two hour hard work, I can figure out this design.

That is, in the case of the following code, you don’t need to include the “datetime(…)” function string in the SqliteCommand class. If you know much about SQLite and its accepted statements, you might try hard to implement a query string in a SqliteCommand class like this: “INSERT INTO NOTIFICATION VALUES(datetime(” + “\”@TIME\”, ‘localtime’)…”. I’m sure you will receive a SqliteException which says “Syntax Error” and you will feel a little frustrated.

Insert some data:

public bool InsertNotification(Domain.Notification notif)
{
    bool ret;

    string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, db_path);
    using (SqliteConnection conn = new SqliteConnection($"Filename={dbpath}"))
    {
        conn.Open();

        SqliteCommand comm = new SqliteCommand("INSERT INTO NOTIFICATION VALUES (" +
                    "@TIME, @Level, @Name, @Desc)");
        comm.Parameters.AddWithValue("@TIME", DateTime.Now); // set directly to the above SqliteCommand
        comm.Parameters.AddWithValue("@Level", (int)notif.Type);
        comm.Parameters.AddWithValue("@Name", notif.Function);
        comm.Parameters.AddWithValue("@Desc", notif.Description);
        comm.Connection = conn;
        int ins = comm.ExecuteNonQuery();
        if (ins == 1)
        {
            ret = true;
        }
        else
        {
            ret = false;
        }
    }
    return ret;
}

Retrieve data:

public List<Domain.Notification> GetNotifications()
{
    List<Domain.Notification> list = new List<Domain.Notification>();

    string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, db_path);
    using (SqliteConnection conn = new SqliteConnection($"Filename={dbpath}"))
    {
        conn.Open();
        SqliteCommand comm = new SqliteCommand("SELECT datetime(Time), Level, Name, Description FROM NOTIFICATION");
        comm.Connection = conn;
        SqliteDataReader query = comm.ExecuteReader();
        while(query.Read())
        {
            Domain.Notification notif = new Domain.Notification(
            query.GetDateTime(0), 
            (Domain.Notification.MessageType)Enum.ToObject(typeof(Domain.Notification.MessageType), query.GetInt16(1)), query.GetString(2), query.GetString(3));
            list.Add(notif);
        }
     }
     return list;
}