Open SQLite connection in UWP right after moving StorageFile

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.