How to resolve an incorrect path to SQLite Databas

2019-03-03 10:58发布

问题:

Overview - I've added some code to copy an existing database to the device's local folder. So far the first condition if the existing db doesn't already exist works fine.

Issue - But when the line of code to copy the existing db from the solution folder to the device folder executes, I get an SQLite error. The error tells me that the db file couldn't be opened.

During debugging I see that the DBPath is the same as the file location in my solution. So I'm not too sure what could be wrong with the path.

(The db is attached as content and set to "copy always".)

The full path to the db file in the package is :

C:\Users\Brian\Documents\Visual Studio 2013\Projects\Parking Tag Picker WRT\Parking Tag Picker WRT\Databases\ParkingZoneDatabase.db

Question: How can I resolve the db path to the correct path required by the SQLite class?

Error log - The exact dump of the exception that get's thrown here is as follows in the SQLite class:

SQLite.SQLiteException was unhandled by user code
  HResult=-2146233088
  Message=Could not open database file: C:\Data\Users\DefApps\APPDATA\Local\Packages\6d00c25c-39d2-443f-a29b-2c30c8ce7e99_gevy8cezwa384\LocalState\Databases\ParkingZoneDatabase.db (CannotOpen)
  Source=Parking Tag Picker WRT
  StackTrace:
       at SQLite.SQLiteConnection..ctor(String databasePath, SQLiteOpenFlags openFlags, Boolean storeDateTimeAsTicks)
       at SQLite.SQLiteConnection..ctor(String databasePath, Boolean storeDateTimeAsTicks)
       at Parking_Tag_Picker_WRT.Helpers.DatabaseHelper.ReadZones(String tableName)
       at Parking_Tag_Picker_WRT.ViewModel.TagRequestViewModel.InitZoneInfoAsync()
       at Parking_Tag_Picker_WRT.TagRequestPage.OnNavigatedTo(NavigationEventArgs e)
  InnerException: 

DBHelper code: (code that copies the existing db to local folder on device)

    public const string DBPath = @"Databases\ParkingZoneDatabase.db";

    /// <summary>
    /// Load SQL_LiteTable from Solution   
    /// </summary>
    /// <param name="DBPATH"></param>
    /// <returns></returns>
    public async Task<bool> Init()
    {


        bool isDatabaseExisting = false;

        try
        {
            StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync(DBPath);
            isDatabaseExisting = true;
        }
        catch
        {
            isDatabaseExisting = false;
        }

        if (!isDatabaseExisting)
        {
            //Fails at this line when retrieving the existing db
            StorageFile databaseFile = await Package.Current.InstalledLocation.GetFileAsync(DBPath);
            await databaseFile.CopyAsync(ApplicationData.Current.LocalFolder);
        }

        return true;          
    }

I also checked the permissions on the db file itself which isn't set to READ ONLY -

回答1:

Firstly thanks for the repo. The problem is the path that you are specifying.
When you use await databaseFile.CopyAsync(ApplicationData.Current.LocalFolder);, that means you are copying the selected data to Local folder(not inside Database folder) to copy it inside a database folder you would require to create a Database folder first and then copy the file inside that folder.
For now I am giving the simple solution of making changes to your AppDBPath to fix the error. This solution copies the database to your Local folder only not inside Database folder.

public const string AppDBPath = @"ParkingZoneDatabase.db";
public const string PackageDBPath = @"Databases\ParkingZoneDatabase.db";


        /// <summary>
        /// Load SQL_LiteTable from Solution   
        /// </summary>
        /// <param name="DBPATH"></param>
        /// <returns></returns>
        public async Task<bool> Init()
        {


            bool isDatabaseExisting = false;

            try
            {
                StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync(AppDBPath);
                isDatabaseExisting = true;
            }
            catch 
            {
                isDatabaseExisting = false;
            }


                if (!isDatabaseExisting)
                {

                    StorageFile databaseFile = await Package.Current.InstalledLocation.GetFileAsync(PackageDBPath);
                    await databaseFile.CopyAsync(ApplicationData.Current.LocalFolder);
                }


            return true;          
        }

Since you are using AppDBPath as reference to your DB rest of code should work perfectly.