Unable to retrieve data from SQLite DB on SD Card on WP8

Question

I've created a SQLite DB using System.Data.SQLite in a console app. I've then moved this to the Windows Phone's SD card.

I followed these instructions to add SQLite support to my WP8 app: https://github.com/peterhuene/sqlite-net-wp8

I locate the DB file and open it like so:

ExternalStorageFile file = null;    
IEnumerable<ExternalStorageDevice> storageDevices = await ExternalStorage.GetExternalStorageDevicesAsync();
    foreach (ExternalStorageDevice storageDevice in storageDevices)
    {
        try
        {
            file = await storageDevice.GetFileAsync("northisland.nztopomap");
        }
        catch
        {
            file = null;
        }
        if (file != null) break;
    }

    SQLiteConnection conn = new SQLiteConnection("Data Source=" + file.Path + ";Version=3;Read Only=True;FailIfMissing=True;");

    SQLiteCommand command = new SQLiteCommand(_dbNorthIsland);
    command.CommandText = "SELECT COUNT(*) FROM tiles";
    int count = (int)command.ExecuteScalar<int>();

This results in the following error:

{SQLite.SQLiteException: no such table: tiles
   at SQLite.SQLite3.Prepare2(Database db, String query)
   at SQLite.SQLiteCommand.Prepare()
   at SQLite.SQLiteCommand.ExecuteScalar[T]()}

Interestingly, I've also tried the following SQL statement:

"SELECT COUNT(*) FROM sqlite_master WHERE type='table'"

Which gives a result of 0 suggesting my "tiles" table cannot be found?

I suspect that ExternalStorageFile.Path is returning a path that SQLite is unable to resolve as an existing file, leading it to create a new database and so complain about the missing table when I try to access it.

This Microsoft article seems to suggest that I should be able to access files from the SD card from my app: http://msdn.microsoft.com/library/windowsphone/develop/jj720573%28v=vs.105%29.aspx

Feedback provided by a Microsoft Employee:

Your app doesn't have direct access to the files on the SD card. It can't open them directly with file system API, but needs to use the ExternalStorageFile and ExternalStorageFolder interfaces from Windows.Storage. To quote from Reading from the SD card on Windows Phone 8:

Windows Phone apps can read specific file types from the SD card using the Microsoft.Phone.Storage APIs.

I expect that the SQLite implementation for the phone tries to open the database using standard C file API rather than using the Storage objects and so requires that the database be in the Xap or isolated storage and cannot access a database on the SD card (this is definitely the case for SQLite for Windows Store apps).

In theory it would be possible to update SQLite to use Storage objects, but I suspect it would be a significant project to do so.

Example bare-bones project:

I've created a bare-bones example project that highlights my issue, just in case anyone wants to look and potentially try out any ideas quickly:

https://skydrive.live.com/?cid=de82af8533ac6d28&id=DE82AF8533AC6D28!242&ithint=file,.zip&authkey=!AF4IwcI0G7bsDFE

Copy the bx24.nztopomap file to the root of your SD card to test.

Feedback from SQLite SDK community:

Apparently it should be fairly straight forward to add support to the SQLite SDK for someone with some C++ skills (mine are a bit rusty!):

Replies: http://www.mail-archive.com/[email protected]/msg81059.html http://www.mail-archive.com/[email protected]/msg81060.html

To my original question: http://www.mail-archive.com/[email protected]/msg81055.html

Anyone know of a SQLite library for Windows Phone that can read from the SD card?

Answer 1

You don't need a library for read-only access, you just need to register the files extension in a manifest. That might look something like this:

<Extensions>
   <FileTypeAssociation Name="SQLite"
                        TaskID="_default"
                        NavUriFragment="fileToken=%s">
       <Logos>
           <Logo Size="small" IsRelative="true">...</Logo>
           <Logo Size="medium" IsRelative="true">...</Logo>
           <Logo Size="large" IsRelative="true">...</Logo>
       </Logos>
       <SupportedFileTypes>
         <FileType ContentType="application/sqlite">.sqlite</FileType>
       </SupportedFileTypes>
   </FileTypeAssociation>
</Extensions>