SQLite Net not being able to create text file in Win IoT Library

0 votes

I have a Raspberry Pi running on Windows 10 IoT Core and I've been trying to create a SQLite database in a folder in the file system on the Windows running on my Pi such that multiple background applications can access it one after the other. And, after I had finally managed to locate the filesystem where I could access the LIbraries in Windows IoT as I was thinking accessing the file system would let me write text files onto an app and save it to the Library's default folder to again run it with another app. But the SQLite database just is just not being able to create databases in a folder or open existing database for me to just edit those files or add more data to that folder. It keeps throwing the following exception when I run it (FYI: MyDb.db is the name of the file I was trying to add to the 'Picture' Folder of the Windows 10 IoT Core's filesystem):

SQLite.Net.SQLiteConnection::."Could not open database file: "C:\Data\Users\DefaultAccount\Pictures\MyDb.db (CannotOpen)"

Although I could open the file systems, I could just read data from it. 

This is where I create and write the text file:

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

//*** NOTE: Pictures Library is 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}"); }
        }
    }
}

Output: 

'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}"); }
        }
    }
}

Output:

'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

Aug 3, 2018 in IoT (Internet of Things) by Bharani
• 4,660 points
926 views

1 answer to this question.

0 votes

That exception comes when access limited to any path like "C:\Data\Users\DefaultAccount\Pictures\" of UWP app. You can check https://docs.microsoft.com/en-us/windows/uwp/files/file-access-permissions
In fact, I did it myself and got the same exception. It seems that this folder is just not accessible with SQLite file operations but I can't really tell why it is so.
Alternatively though, you try the PublisherCacheFolder. I tried creating a .db file and writing data onto a background app. And even read the data from another background app. It worked.

Contact class:

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

Creating and writing the 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);
                }
            }

Reading the 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);
            }

And finally, append the flollowing to Package.appxmanifest:

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

answered Aug 3, 2018 by nirvana
• 3,130 points

Related Questions In IoT (Internet of Things)

0 votes
1 answer

Publishing commands to device in IBM IoT using MQTT in Java

If you are publishing from an application, ...READ MORE

answered Jul 27, 2018 in IoT (Internet of Things) by anonymous2
• 4,280 points
1,462 views
0 votes
1 answer

Using FIWARE to start Cygnus as a Service in IoT Solution

Cygnus is supposed to create /var/run/cygnus/ when started. You ...READ MORE

answered Aug 13, 2018 in IoT (Internet of Things) by anonymous2
• 4,280 points
898 views
0 votes
1 answer
0 votes
1 answer

Mono C# tutorial?

For learning the language, any C# tutorial ...READ MORE

answered May 30, 2022 in C# by rajiv
• 1,620 points
658 views
+1 vote
1 answer

Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

answered Aug 22, 2018 in Power BI by nirvana
• 3,130 points
2,675 views
0 votes
1 answer

How to get a value from SQLite to convert it?

Server side: b = BtcConverter(force_decimal=True) p1btc = b.get_latest_price('USD') p1btcr = ...READ MORE

answered Sep 6, 2018 in Blockchain by digger
• 26,740 points
682 views
0 votes
1 answer
0 votes
1 answer

Display time in a Windows Core IoT app with a clock!

It is possible, but you should understand ...READ MORE

answered Jul 10, 2018 in IoT (Internet of Things) by nirvana
• 3,130 points
1,520 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP