Referencing and reading from a .sqlite database file (C#/UWP)

Question

All I'm trying to do is use a .sqlite database file that I have stored in the Assets folder of the app (/Assets/CommonCore.sqlite). I'm trying to set up the connection and I've tried a few different things:

    SQLite.Net.SQLiteConnection conn;

    public MainPage()
    {
        this.InitializeComponent();

        conn = new SQLite.Net.SQLiteConnection("Data Source=Assets/CommonCore.sqlite");

but this throws an exception "does not contain a constructor that takes 1 arguments".

    string path;
    SQLite.Net.SQLiteConnection conn;

    public MainPage()
    {
        this.InitializeComponent();

        path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "CommonCore.sqlite");

        conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);

but that references a folder "\AppData\Local\Packages\8ed84aca-896d-4286-ba91-e52316db2e89_dzc2ymb8n4xk4\LocalState\CommonCore.sqlite" which obviously isn't the file I need.

I'm sure it's something silly I'm missing.

Answer 1

In UWP apps, the folders that you can access are limited:

• All the files that a user specifies using the FileOpenPicker or FolderPicker
• Files from the FutureAccessList or MostRecentlyUsedList. The FutureAccessList can be declared in the manifest file (e.g. Documents, Pictures, Videos folder). However, the user has to allow access to these folders.
• Files which are opened with a file extension association or via sharing.
• The application install directory.
• The application data locations.
• In some cases removable devices.

Source

In your case you were trying to write to the application data locations. However, each time you install your app, this folder is empty.

An alternative would be to read the sqlite database from the install folder. It is easy to add files from your project to this folder. To do this, you first have to declare which files you want to package. This means adding them to the install folder. First, create a subfolder in your work directory (the folder where you can find the AppxManifest.xml). This is not necessary but cleaner. I've called my folder 'Data'.

Next you can check if you did this correctly. Open your project in visual studio and click the 'Show all files' button in the solution explorer.

After that, you should be able to see the new folder and its content in the solution explorer. You can now right click on the folder and select 'Include in project'.

Finally, you should open the properties of your file (e.g. db.sqlite in the Data folder) and set the build action to content.

Now you can connect to the db using the code:

public class Database
{
    string path;
    SQLite.Net.SQLiteConnection conn;

    public Database()
    {
        path = Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path, "Data", "Questions.sqlite");
        conn = new SQLite.Net.SQLiteConnection(new
           SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
    }
}