SQLite WinRT allows Windows and Windows Phone developers to create and manipulate database objects through the SQLite Data Definition Language (DDL).

SQLite supports DDL statements including CREATE, ALTER, and DROP to work with objects like Tables, Indexes, Triggers, and Views.  This is complimented by simplified, dynamic data types including INTEGER, NULL, REAL, TEXT, and BLOB.  Admittedly, this takes some getting used to by those of you who use database engines with static typing.  Here’s a quick explanation:

  • INTEGER: A signed integer that is flexibly stored in 1, 2, 3, 4, 6, or 8 bytes depending on the size of the value you INSERT.
  • NULL: Nothing to see here.
  • TEXT: A variable-sized text string stored using UTF-8, UTF-16BE or UTF-16LE encoding.
  • REAL: A floating point value stored as an 8-byte IEEE floating point number.
  • BLOB: Stores whatever you INSERT into it.

Lets cut to the chase so you can see how to create a new SQLite database:

private async void OpenCreateDatabase()
{
    var db = new SQLiteWinRT.Database(Windows.Storage.ApplicationData.Current.LocalFolder, "Contoso.db");
    try
    {
        await db.OpenAsync();
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
        var result = SQLiteWinRT.Database.GetSqliteErrorCode(ex.HResult);
        throw new Exception("Failed to create database " + result);
    }
}

Walking through the code above, you quickly notice that I created an async function.  This is because I use await when opening the database asynchronously on a worker thread to keep things fast and fluid.  The first line of code is flexible enough to either create new database called Contoso.db or use an existing one which make our lives easier.  You’ll also notice that I’m placing the database in the LocalFolder used for storage by my Windows or Windows Phone app.  SQLiteWinRTPhone is used for Windows Phone and SQLiteWinRT is used for Windows tablets, laptops, and desktops.  I’m wrapping the aforementioned code to open the database in a try/catch block specifically designed to deal with COM exceptions since our WinRT code is interoperating with native C++ code from SQLite.  You’ll use GetSqliteErrorCode in order to retrieve the HResult from SQLite and then throw the error up the stack using a standard Exception object.

With your database created, the next thing you’ll want to do is create one or more tables.  The structure for the CREATE TABLE statement can be found at http://www.sqlite.org/lang_createtable.html.  Below, I’ll show you the code to create a typical Parent/Child table relationship between a Product and ProductDetails table:

private async void CreateTables()
{
    var db = new SQLiteWinRT.Database(Windows.Storage.ApplicationData.Current.LocalFolder, "Contoso.db");
    try
    {
        await db.OpenAsync();
        string sql = @"CREATE TABLE IF NOT EXISTS Products
                       (ProductId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                       Name TEXT)";
        await db.ExecuteStatementAsync(sql);

        sql = @"CREATE TABLE IF NOT EXISTS ProductDetails
                (ProductDetailId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                Color TEXT,
                Quantity INTEGER,
                ProductId INTEGER,
                FOREIGN KEY(ProductId) REFERENCES Products(ProductId) ON DELETE CASCADE)";
        await db.ExecuteStatementAsync(sql);
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
        var result = SQLiteWinRT.Database.GetSqliteErrorCode(ex.HResult);
        throw new Exception("Failed to create tables " + result);
    }
}

In the code above, I use a sql string variable to create two different DDL statements. While they both start out with CREATE TABLE, we also have the luxury of using IF NOT EXISTS before the table name.  This means if the table already exists, this DDL statement is harmless and no operation will be performed.  The next part of the statement creates one or more columns and constraints.  In both tables I create an integer-based Id column that serves as the primary key which enforces uniqueness.  SQL Server DBAs and developers who like to use Identity columns will be happy to see the AUTOINCREMENT constraint that automatically increments the value of the integer each time a new row is inserted.  Other columns and their data types are separated by commas.  Last but not least, the ProductDetails Child table enforces a referential integrity constraint with the Products Parent table.  FOREIGN KEY points to the local ProductId column and REFERENCES the ProductId in the Products table.  ON DELETE CASCADE ensures that when a Product is deleted, associated ProductDetails are also deleted.

Once you’ve created tables, you’ll want to speed up your queries by creating indexes, prepackage SELECT statements into views, and trigger various actions whenever an INSERT, UPDATE, or DELETE occurs:

Sometimes you need to make changes to an existing table.  SQLite allows you rename tables and add columns via ALTER TABLE: http://sqlite.com/lang_altertable.html.  If you find you no longer need certain indexes, views, triggers, and tables, you can drop them:

No matter which DDL operation you need to perform, you’ll follow the same pattern I showed you in the CREATE TABLE code sample above.  You’ll piece together the appropriate statement in a string variable and then call the ExecuteStatementAsync() method.

Now that you know how to use SQLite WinRT to work with SQLite’s Data Definition Language, you can build any data structure that you mobile app needs.  In my next article I’ll show you how to work with SQLite’s Data Manipulation Language (DML) to bring you database and app to life.

– Rob

Tagged on: