SQLiteWinRT: Opening databases shipped as app content or from an SD Card

Recently, I was asked about opening a database file inserted into a device on an SD card. I had to make a slight change to the SQLWinRT wrapper to enable it, so this post explains how, and also looks at opening databases shipped in the app package as content.

Opening databases shipped as content

You can include a prepopulated SQLite database in your Windows 8.x or Windows Phone app package. When the user installs the app, the database file ends up in the App Install folder where you can open it from your code – but only in a read-only fashion:

 public static async void LoadDatabase()
{
    // Get a reference to the SQLite database
    db = new SQLiteWinRT.Database(
        Windows.ApplicationModel.Package.Current.InstalledLocation, 
        "customers.sqlite");

    await db.OpenAsync(SQLiteWinRT.SqliteOpenMode.OpenRead);

    // PRAGMA temp_files=2 causes temporary files to be created in memory
    // rather than in a physicalfile in the same folder as the database
    // Note that temp files only really get used if you are going to do 
    // complicated JOINs etc
    await db.ExecuteStatementAsync("PRAGMA temp_files=2");
}

If you want to have read-write access to the database, you must copy it from the install folder to the local folder first.

 async Task CopyDatabaseAsync()
{
    // Has the database been copied already?
    try
    {
        await ApplicationData.Current.LocalFolder.GetFileAsync("customers.sqlite");
        // No exception? It exists...
        return;
    }
    catch (System.IO.FileNotFoundException)
    {   // Expected response - no-op 
    }

    StorageFile dbfile = await StorageFile.GetFileFromApplicationUriAsync(
        new Uri("ms-appx:///customers.sqlite"));
    await dbfile.CopyAsync(ApplicationData.Current.LocalFolder);
}

Opening a Database on an SD Card

A database file that you open from an SD Card is related to the previous case in that it is read-only. You cannot write to any file on an SD Card from any app in Windows Phone 8.

I haven’t researched it thoroughly, but you should be able to open a database file on removable storage in a Windows 8.x Store app. You will have to request the Removable Storage capability in the app manifest, and declare the file extensions you want to access in the app manifest as well. Then you should be able to get a StorageFile reference to the database file using techniques similar to those discussed here: https://lunarfrog.com/blog/2012/10/27/external-storage-devices/   . When I’ve tried it, I’ll update this post!

What I have tried though, is doing this on a Windows Phone 8 device that has an SD card slot, such as the Nokia Lumia 820. As with Windows 8, you have to declare the file extensions you want to access in the manifest. For example, add the following after the closing </Tokens> tag:

     <Extensions>
      <FileTypeAssociation Name="sqlite" TaskID="_default" NavUriFragment="fileToken=%s">
        <SupportedFileTypes>
          <FileType ContentType="application/sqlite">.sqlite</FileType>
        </SupportedFileTypes>
      </FileTypeAssociation>
    </Extensions>

Then in your code, you can open a read-only connection to the database as follows:

 public static async void LoadDatabase()
{
    // Get a reference to the SQLite database
    ExternalStorageDevice _sdCard = 
        (await ExternalStorage.GetExternalStorageDevicesAsync())
        .FirstOrDefault();

    if (_sdCard == null)
    {
        MessageBox.Show("No SD Cards found");
        return;
    }
    else
    {
        ExternalStorageFile dbStoragefile = 
            await _sdCard.GetFileAsync("customers.sqlite");
        db = new SQLiteWinRT.Database(dbStoragefile.Path);
    }

    await db.OpenAsync(SQLiteWinRT.SqliteOpenMode.OpenRead);

    // PRAGMA temp_files=2 causes temporary files to be created in memory
    // rather than in a physicalfile in the same folder as the database 
    // Note that temp files only really get used if you are going to do 
    // complicated JOINs etc
    await db.ExecuteStatementAsync("PRAGMA temp_files=2");
}

Important: I had to modify the wrapper code posted up on https://sqlwinrt.codeplex.com in order to enable this usage. I added a new override of the Database object constructor that just takes the path to the database file as a string. Earlier versions had one override of the constructor that took a Windows.Storage.StorageFile parameter and another that took a Windows.Storage.StorageFolder and the filename as string, but neither of those were usable since the ExternalStorageFile object is not as you might expect a derivative of Windows.Storage.StorageFile, but is instead in the Microsoft.Phone.Storage namespace – hence I had to create a new constructor.

Download the latest version of the wrapper code from https://sqlwinrt.codeplex.com to get this update – note that this is built against SQLite version 3.8.2 – make sure you update to this version using the Visual Studio – Tools – Extensions and Updates wizard.

Download the sample project here

Note that it covers both scenarios covered in this post. At the top of app.xaml.cs there is a compile time symbol #define USE_SDCARD – comment this out to test the ‘database shipped as content’, or leave it in to test the SD card access. You’ll need a real device for the latter and before testing the app use your PC to copy the customers.sqlite file from the project onto the sd card and insert into your phone. Sadly, the emulator does not emulate a removable storage card!