Can I open Sqlite db from another location other than local storage ?

Question

Hi ,

I'm trying to open a sqlite db from another location , other than the local storage.

I can open and do everything from the local storage but what if I want to move my sqlite file db to another location and access it from there. So what I did was the below

 public StorageFolder MyFolder;

  public async Task GetFolder()
        {
            StorageFolder folder;
          
            if (!StorageApplicationPermissions.FutureAccessList.ContainsItem("Local Disk C"))
            {
                //"Local Disk C" is a random token
                FolderPicker fPicker = new FolderPicker();
                fPicker.ViewMode = PickerViewMode.Thumbnail;
                fPicker.SuggestedStartLocation = PickerLocationId.ComputerFolder;
                fPicker.FileTypeFilter.Add("*");
                folder = await fPicker.PickSingleFolderAsync();
                StorageApplicationPermissions.FutureAccessList.AddOrReplace("Local Disk C", folder);
            }
            else
            {
                folder = await StorageApplicationPermissions.FutureAccessList.GetFolderAsync("Local Disk C");

              
            }

            this.MyFolder = folder;
        }

private async void Button_Click(object sender, RoutedEventArgs e)
        {
            try
            {
               await GetFolder();

               StorageFile sampleFile = await this.MyFolder.GetFileAsync("mydatabase.db");

               app.DisplayToast(app.AppTitle, sampleFile.DisplayName, 0);

               string mypath = MyFolder.Path + "\\mydatabase.db";

              
               var db = new SQLite.SQLiteConnection(mypath);


               db.RunInTransaction(() =>
               {
                   //retrieve all the table.    
                   app.MyHelpMessages = null;

                              });








            }
            catch (Exception ex)
            {
                app.DisplayToast(app.AppTitle, ex.Message, 0);
            }




        }

I'm selecting the folder to give Permissions and I'm running the orageFile sampleFile = await this.MyFolder.GetFileAsync("mydatabase.db");

 which is fine and I can see the file but when I try to open the database Sqlite tells me that the db is not openned.

I saw the below link

http://social.msdn.microsoft.com/Forums/windowsapps/en-US/9144f647-5af0-412e-9cc4-f9e8f9e06080/is-it-possible-to-use-a-sqllite-file-from-a-directory-other-than-localstorage?forum=winappswithcsharp

and Rob says that this cannot be done. My example also agrees on that.

Does anyone has an idea or a workaround for this ?

Appreciate any help you could give me

thank you 

Answer 1

Quoting myself

No. SQLlite depends on having direct access to read and write the database file. Your app has direct access only to its install directory (read only) and application data directories (read and write). The app can only access the libraries via StorageFolders, which SQLlite does not use.

See File access and permissions .

There isn't a good workaround. You would need to update SQLite to use the StorageFile API to access its database. For help with that you'd need to talk to the SQLite developers, but I expect it would be extremely difficult and require major changes to maintain performance.

Why do you want the file elsewhere? You may be able to get away with copying the file from your external location to app data when the app needs it and then copy it back when the app is done writing it. 

Answer 2

Hi Rob ,

I thought of the solution of copying and pasting back but there ' s a large amount of data and doing this everytime ....
I want this because we have a different application which will read the same database and instead of pointing the database in the local storage path which is a pretty large string I thought that a more suitable and more easy to maintain "path" will be ideal for each installation.

Anyway.

Thank you Rob