0

Prologue:

I am writing SQLite GUI client for UWP. I use Microsoft.Data.Sqlite library for SQLite API with C#. Also I use a redirection table to be able to open database within my sandbox app which is published in Microsoft Store. Redirection table replaces CreateFileW to CreateFileFromAppW calls and similar.

Problem:

User has File -> Save as feature. When user creates a new database file is created inside app local directory. Next when user saves his/her database as I need to move this file. I use StorageFile API cause I cannot use any other file API within a sandbox app. So I call:

var savePicker = new Windows.Storage.Pickers.FileSavePicker();
savePicker.SuggestedStartLocation = Windows.Storage.Pickers.PickerLocationId.DocumentsLibrary;
savePicker.FileTypeChoices.Add("SQLite3 Database", new List<string>() { ".sqlite", ".db" });
savePicker.SuggestedFileName = "Database";
var file = await savePicker.PickSaveFileAsync();
if(null != file)
{                    
    Windows.Storage.AccessCache.StorageApplicationPermissions.FutureAccessList.Add(file);
    sqliteConnection.Close();  // it is important cause if I skip this moving a file will fail
    var localFolder = ApplicationData.Current.LocalFolder;
    var currentStorageFile = await localFolder.GetFileAsync(App.UnsavedDatabaseFileName);  // here I obtain StorageFile for opened database
    await currentStorageFile.MoveAndReplaceAsync(file);  // here I move a file
    sqliteConnection = new SqliteConnection("Data Source=" + file.Path);
    sqliteConnection.Open();  // this line fails with error 14: cannot open database file
}

I also tried to skip closing and reopening a connection -> then moving a file fails. If I call FileOpenPicker between await currentStorageFile.MoveAndReplaceAsync(file); and sqliteConnection = new SqliteConnection("Data Source=" + file.Path); then everything will work fine but showing file open picker right after file save picker is a very bad user experience. I know that sandboxed app gives file access permission only after user selected a file manually. But it looks like that FileSavePicker does not give me a permission just like FileOpenPicker does. I could not find any info about it.

Epilogue:

This is the app https://sqliteman.dev. Please feel free to criticize cause it is what makes my app better.

fnc12
  • 2,241
  • 1
  • 21
  • 27

3 Answers3

0

If it is a permissions issue, then one solution would be to declare broadFileSystemAccess in your app's manifest.

https://learn.microsoft.com/en-us/windows/uwp/files/file-access-permissions

I find it odd that you don't have write access to a file you create.

Peter Dongan
  • 1,762
  • 12
  • 17
0

Try putting MoveAndReplaceAsync() in a using statement, and opening the SQLite connection outside of it.

Peter Dongan
  • 1,762
  • 12
  • 17
  • I am not sure whether it can help cause `StorageFile.MoveAndReplaceAsync` returns `IAsyncAction` which does nothing (more details here https://learn.microsoft.com/en-us/uwp/api/windows.storage.storagefile.moveandreplaceasync?view=winrt-20348#returns). Anyway I'll try – fnc12 Jun 12 '21 at 17:32
  • I cannot do it cause result of `StorageFile.MoveAndReplaceAsync` call cannot be saved into a variable – fnc12 Jun 12 '21 at 17:45
0

Update:

I have tested with broadFileSystemAccess capbility, and it doesn't work and still report the exception "cannot open database file". Therefore, it isn't feasible to use broadFileSystemAccess capbility.

If you want to use Sqlite in uwp, there are only two location can be accessed. ApplicationData and Application install directory, the difference is that ApplicationInstallation can only be read, and ApplicationData can be read and written. So it reported the exception when you connnect the file located in another location.

My meaning is that you could store the database file anywhere, but when you operate this database file, you need to move this file to the location that can access.(ApplicationData,Application install directory). OtherWise, you can't insert data to the table of database file, even, you can't connect the database file.

dear_vv
  • 2,350
  • 1
  • 4
  • 13
  • I modified `Package.appxmanifest` as you told to but I cannot find my app in `Settings > Privacy > File system`. The list is empty and toggle button `Allow apps to access you FS` is on. IDK why. BTW `Settings > Privacy > Videos` has list of three apps: camera, movies and Xbox-something. Why I cannot see my app in settings? – fnc12 Jun 14 '21 at 06:50
  • You need to run the application once, so that you can find the application in Settings(Settings > Privacy > File system) when you run the application next time. – dear_vv Jun 14 '21 at 08:58
  • @fnc12 I have updated my answer, please check it. Besides, I want to know whether you can operate this database file when you use FileOpenPick to get permission, such as insert and update data. – dear_vv Jun 14 '21 at 10:20
  • 1) I did run the app once and even more but it does not show up in `Settings > Privacy > File system`. The list is empty. Also I ran the app in release and debug mode. Also I've removed app version installed from the Microsoft Store but this did not help. 2) I don't understand your answer update. Does it mean that user can only store database files at mentioned folders? Also your C# code snippet looks weird: you have three `StorageFile` instances. Why three? There are only two of them required: the old one and the new one. But you have `currentStorageFile`, `file` and `file1` – fnc12 Jun 15 '21 at 06:19
  • @fnc12 I have tested with this capbility and modified my answer, you could check it. – dear_vv Jun 15 '21 at 08:58
  • Ok looks like that I can add `Export and close` instead of `Save as` and this is sad. And `broadFileSystemAccess` helps with nothing cause I can implement `Export and close` feature without `broadFileSystemAccess`. SQLite database can be opened at any location if user selected this location with `FileOpenPicker`. But I cannot open SQLite connection after `FileSavePicker`. When user creates a new file file is created inside AppData dir and works well. I want `Save as` feature and to be able to keep editing file after 'saving a file as' but I cannot even with `broadFileSystemAccess`. – fnc12 Jun 15 '21 at 17:18
  • You could move the file to the ApplicationData when you operate the database, then when you finished the operation, you could move this file from the ApplicationData to the location where the user previously saved it. So that you could operate this file after 'saving a file as'. – dear_vv Jun 16 '21 at 07:17
  • This is an undesired way. I was thinking about it in the beginning but I refused using it cause in case of crash user will be disappointed cause data he/she inserted is missing. This is not how most Windows apps work. I need my app to work like MS Word. If you open a file and then you work with this file not with its' clone. And when you perform `Save as` operation you keep editing this file after. – fnc12 Jun 16 '21 at 10:55
  • However, uwp has a strong access restriction for database file, I have to say there is no way to access file that the user `saved as` directly. – dear_vv Jun 18 '21 at 07:10