SQLite.Net Won't Create In Win IoT Library

Question

I have been struggling to find a way of persisting an SQLite database on a Pi under Win IoT which can be accessed by different background applications (not concurrently).

I thought I had the answer when I discovered Libraries (Music, Pictures, Videos - but perversely not Documents, without more work). I can create a text file in one app and write it to the Pictures library's default folder. I can then read the text file with another app. File.Exists returns true. Bingo (I thought)!

However, SQLite will not create a database in the folder or open an existing database that I copy to the folder. SQLite.Net.SQLiteConnection returns an SQLite exception: "Could not open database file: C:\Data\Users\DefaultAccount\Pictures\MyDb.db (CannotOpen)" - no further clues.

The folder appears to grant full permissions. Does anyone have any ideas, please?

Creating and Writing a text file:

using System;
using Windows.ApplicationModel.Background;
using System.IO;
using System.Diagnostics;

//*** NOTE: Pictures Library checked in Package.appxmanifest 'Capabilities'

namespace LibraryTest
{
    public sealed class StartupTask : IBackgroundTask
    {
        private BackgroundTaskDeferral Deferral;

        public async void Run (IBackgroundTaskInstance taskInstance)
        {
            Deferral = taskInstance.GetDeferral ();

            var myPictures = await Windows.Storage.StorageLibrary.GetLibraryAsync
                (Windows.Storage.KnownLibraryId.Pictures);

            string path = myPictures.SaveFolder.Path;
            Debug.WriteLine ($"'Pictures' Folder: {path}");

            string newFilePath = Path.Combine (path, "TestTextFile.txt");
            Debug.WriteLine ($"New File Path: {newFilePath}");

            try {
                using ( var stream = File.OpenWrite (newFilePath) ) {
                    using ( var writer = new StreamWriter (stream) ) {
                        writer.Write ("This is some test text.");
                    }
                }
                Debug.WriteLine ($"File created OK");
            }
            catch (Exception ex) { Debug.WriteLine ($"Exception: {ex.Message}"); }
        }
    }
}

Produced:

'Pictures' Folder: C:\Data\Users\DefaultAccount\Pictures
New File Path: C:\Data\Users\DefaultAccount\Pictures\TestTextFile.txt
File created OK

Reading:

using System;
using Windows.ApplicationModel.Background;
using System.IO;
using System.Diagnostics;

//*** NOTE: Pictures Library checked in Package.appxmanifest 'Capabilities'

namespace ReadLibraryTest
{
    public sealed class StartupTask : IBackgroundTask
    {
        private BackgroundTaskDeferral Deferral;

        public async void Run (IBackgroundTaskInstance taskInstance)
        {
            Deferral = taskInstance.GetDeferral ();

            var myPictures = await Windows.Storage.StorageLibrary.GetLibraryAsync
                (Windows.Storage.KnownLibraryId.Pictures);

            string path = myPictures.SaveFolder.Path;
            Debug.WriteLine ($"'Pictures' Folder: {path}");

            string newFilePath = Path.Combine (path, "TestTextFile.txt");
            Debug.WriteLine ($"New File Path: {newFilePath}");

            try {
                using ( var stream = File.OpenRead (newFilePath) ) {
                    using ( var reader = new StreamReader (stream) ) {
                        string fileContents = reader.ReadLine ();
                        Debug.WriteLine ($"First line of file: '{fileContents}'");
                    }
                }
                Debug.WriteLine ($"File read OK");
            }
            catch ( Exception ex ) { Debug.WriteLine ($"Exception: {ex.Message}"); }
        }
    }
}

Produced:

'Pictures' Folder: C:\Data\Users\DefaultAccount\Pictures
New File Path: C:\Data\Users\DefaultAccount\Pictures\TestTextFile.txt
First line of file: 'This is some test text.'
File read OK

Answer 1

However, SQLite will not create a database in the folder or open an existing database that I copy to the folder. SQLite.Net.SQLiteConnection returns an SQLite exception: "Could not open database file: C:\Data\Users\DefaultAccount\Pictures\MyDb.db (CannotOpen)" - no further clues.

Yes, I reproduced this issue. It seems this folder does not work with SQLite file operations but I don't know where the problem is.

As a workaround, you can use PublisherCacheFolder. I create the .db file and write data in one background app. And read the data from another background app. It works.

Contact class:

public sealed class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Create and write file:

            StorageFolder sharedFonts = Windows.Storage.ApplicationData.Current.GetPublisherCacheFolder("test");

            var sqlpath = System.IO.Path.Combine(sharedFonts.Path, "MyDb.db");

            using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
            {
                conn.CreateTable<Contact>();
                for (var i = 0; i < 100; i++)
                {
                    Contact contact = new Contact()
                    {
                        Id = i,
                        Name = "A"
                    };
                    conn.Insert(contact);
                }
            }

Read file:

            StorageFolder sharedFonts = Windows.Storage.ApplicationData.Current.GetPublisherCacheFolder("test");

            var sqlpath = System.IO.Path.Combine(sharedFonts.Path, "MyDb.db");

            using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
            {
                var query = conn.Table<Contact>().Where(v => v.Name.Equals("A"));

                foreach (var stock in query)
                    Debug.WriteLine("contact: " + stock.Id);
            }

To use this publisher folder you need add the following lines in Package.appxmanifest:

  <Extensions>
    <Extension Category="windows.publisherCacheFolders">
      <PublisherCacheFolders>
        <Folder Name="test" />
      </PublisherCacheFolders>
    </Extension>
  </Extensions>

Answer 2

Thanks, Rita. Worked very well. For the benefit of anyone reading, I am using the async version of SqlLite and create the connection as follows:

const string FileName = "MyFile.db";
string DbDir;
string DbPath;


Constructor:
    DbDir = Windows.Storage.ApplicationData.Current.GetPublisherCacheFolder("test").Path;
    DbPath = Path.Combine (DbDir, DbFileName);


public SQLite.Net.Async.SQLiteAsyncConnection GetConnectionAsync ()
    {
        var connectionFactory = new Func<SQLite.Net.SQLiteConnectionWithLock>(()=>
                new SQLite.Net.SQLiteConnectionWithLock(new SQLitePlatformWinRT(),
                        new SQLite.Net.SQLiteConnectionString(DbPath, storeDateTimeAsTicks: false)));
        var asyncConnection = new SQLiteAsyncConnection(connectionFactory);
        return asyncConnection;
    }

Then, for instance, read a table of type Parms:

public async Task<Parms> ReadParmsAsync ()
{
    var db = GetConnectionAsync ();
    var query = db.Table<Parms> ().Where (p => p.Id == 1);
    return await query.FirstOrDefaultAsync ();
}

My concern about the SQLite async connection is that it is not IDisposable. Therefore, will the 'factory' eventually run out of steam (memory, handles)? But I guess that is a subject for another thread.