CODEWITHSUNDEEP
c#databasesqliteuwp
David Jones
David Jones

Reputation: 582

UWP App SQLite Access database in Documents Library

Q: I can't open a SQLite database in my Documents folder:

App: Universal Windows (10) Platform
Development: C#, Visual Studio 2015
SQLite: Using SQLite.net
Targets: Windows 10 Desktop and Windows 10 Phone

Deployment: From Visual Studio or Sideloaded (Do not need Store deployment) 

using SQLite;
using SQLite;   
using SQLite.Net;   
using SQLite.Net.Async;   
using SQLite.Net.Attributes

I can:

Open and read from a database included in the project as content: 

using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection
(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), ".\\mydb.db3"))
{
//Read queries
}

Open/Create and Read/Write a database to the apps workspace: 

string path = Path.Combine
(Windows.Storage.ApplicationData.Current.LocalFolder.Path,"another.db3");

using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection
(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path))
{
//CRUD
}

I can't

I use a FilePicker to choose the db in Documents or the SD
I then use the File.Path property when attempting to open the database connection 

Windows.Storage.StorageFile File = await FilePicker.PickSingleFileAsync();  
string path = File.Path;

I get the following error message when I attempt to open that connection:

Exception thrown: 'SQLite.Net.SQLiteException' in SQLite.Net.dll
SQLite-GetAllVendors: Could not open database file: C:\Users\me\Documents\another.db3 (CannotOpen)

Have added SQLite .db and .db3 file associations. 

      <Extensions>
        <uap:Extension Category="windows.fileTypeAssociation">
          <uap:FileTypeAssociation Name=".db">
            <uap:DisplayName>SqliteDB</uap:DisplayName>
            <uap:EditFlags OpenIsSafe="true" />
            <uap:SupportedFileTypes>
              <uap:FileType>.db</uap:FileType>
              <uap:FileType>.db3</uap:FileType>
            </uap:SupportedFileTypes>
          </uap:FileTypeAssociation>
        </uap:Extension>
      </Extensions>
    </Application>
  </Applications>

Have added the relevant Capabilities

  <Capabilities>
    <Capability Name="internetClient" />
    <uap:Capability Name="picturesLibrary" />
    <uap:Capability Name="documentsLibrary" />
    <uap:Capability Name="videosLibrary" />
    <uap:Capability Name="userAccountInformation" />
    <uap:Capability Name="removableStorage" />
    <DeviceCapability Name="webcam" />
    <DeviceCapability Name="location" />
  </Capabilities>

Surely there is some way to open a SQLite database in Documents or on a memory stick form a Universal Windows (10) app.
Thx in advance

Upvotes: 8

Views: 4351

Answers (1)

Rob Caplan - MSFT
Rob Caplan - MSFT

Reputation: 21899

Because SQLite opens the database file directly rather than going through the file broker it can only see databases in the app install and application data (the directories that the app has direct file permissions to read and read/write respectively).

Changing this would require an update to SQLite to use streams from the file broker objects (StorageFile and StorageFolder) to access locations that have permissions granted via capabilities, pickers, etc.

Upvotes: 13

Related Questions