locked
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 

    Wednesday, September 17, 2014 1:30 PM

Answers

  • 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. 

    • Marked as answer by zakkar Wednesday, September 17, 2014 1:57 PM
    Wednesday, September 17, 2014 1:39 PM
    Owner

All replies

  • 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. 

    • Marked as answer by zakkar Wednesday, September 17, 2014 1:57 PM
    Wednesday, September 17, 2014 1:39 PM
    Owner
  • 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

    Wednesday, September 17, 2014 1:56 PM