locked
[UWP] SQLite.net Path question? RRS feed

  • Question

  • I'm creating UWP app with SQLite.Net-PCL  I already have a SQLite db that I want to use (I do not want to create a new one in the app). I've copied it to the root of my project folder. How do I access it?  I've tried:

    var sqlpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "testDB.sqlite");  But that looks in the AppData directory.  Is this were I MUST store this file?  If so, when I compile and go to distribute the program, will the db be included?  Remember I want use a prepopulated db and not create one in-app?

    Looking for best-use solutions for adding an existing SQLite db to a project.

    Thanks,

    Mike


    • Moved by Kristin Xie Thursday, March 24, 2016 7:20 AM UWP related
    • Edited by Fred Bao Friday, March 25, 2016 3:17 AM add the tag
    Wednesday, March 23, 2016 9:33 PM

Answers

  • If you want to include your db file in your project, you need to store the .db file to /Assets folder.
    However, SQLite can't access the /Assets folder directly. You need to copy the .db file to app local folder.

    For example... run the following code at the initialization part of the app.

    var dbFile = await ApplicationData.Current.LocalFolder.TryGetItemAsync("hoge.db") as StorageFile;
    
    if(null == dbFile)
    {
      // first time ... copy the .db file from assets to local  folder
      var localFolder = ApplicationData.Current.LocalFolder;
      var originalDbFileUri = new Uri("ms-appx:///Assets/hoge.db");
      var originalDbFile = await StorageFile.GetFileFromApplicationUriAsync(originalDbFileUri);
    
      if(null != originalDbFile)
      {
    	dbFile = await originalDbFile.CopyAsync(localFolder, "hoge.db", NameCollisionOption.ReplaceExisting);
      }
    }

    Then, sqlite can load the db file with the file path.

    protected SQLiteAsyncConnection GetConnection(string dbName)
    {
      // dbName = 'hoge.db'	
      // pool is acquired by ' new SQLiteConnectionPool(new   SQLitePlatformWinRT());'
      if (null == pool) { return null; }
      return new SQLiteAsyncConnection(() => pool.GetConnection(new SQLiteConnectionString(ApplicationData.Current.LocalFolder.Path + "\\" + dbName, false)));
    }

    • Proposed as answer by Fred Bao Wednesday, March 30, 2016 9:11 AM
    • Marked as answer by Barry Wang Wednesday, April 6, 2016 10:10 AM
    Friday, March 25, 2016 7:29 AM
  • Hello MiLambert,

    I firstly recommend you have a check the File access permissions to have a quick understand about the storage in UWP platform. After you check the link, you could be able to know that it is not a choice to place your sql file to the program folder, because the app's install directory is a read-only location.

    For me, I usually place the db file in the local folder which is also recommended by the team:

    https://channel9.msdn.com/Series/A-Developers-Guide-to-Windows-10/10

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. Click HERE to participate the survey.

    • Proposed as answer by Fred Bao Wednesday, March 30, 2016 9:10 AM
    • Marked as answer by Barry Wang Wednesday, April 6, 2016 10:10 AM
    Friday, March 25, 2016 3:26 AM

All replies

  • This might be better asked in the Windows Phone Development Subforum. But at least for Desktop applications the rule is:
    Never store data in the Programms directory. That is was a faulty FAT16+32 era pattern, that was burried with Windows XP or Vista latest.

    If you need to store stuff, use the userprofile or appdata folders. Get thier path using the SpecialFolders enumeration from the system:
    https://msdn.microsoft.com/en-us/library/system.environment.specialfolder.aspx

    The article mentions nothing about it being part of .NET Core (wich Afaik is what UWP is based on), but there might be something similar or it might work as well.

    Wednesday, March 23, 2016 11:22 PM
  • Hello MiLambert,

    I firstly recommend you have a check the File access permissions to have a quick understand about the storage in UWP platform. After you check the link, you could be able to know that it is not a choice to place your sql file to the program folder, because the app's install directory is a read-only location.

    For me, I usually place the db file in the local folder which is also recommended by the team:

    https://channel9.msdn.com/Series/A-Developers-Guide-to-Windows-10/10

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. Click HERE to participate the survey.

    • Proposed as answer by Fred Bao Wednesday, March 30, 2016 9:10 AM
    • Marked as answer by Barry Wang Wednesday, April 6, 2016 10:10 AM
    Friday, March 25, 2016 3:26 AM
  • If you want to include your db file in your project, you need to store the .db file to /Assets folder.
    However, SQLite can't access the /Assets folder directly. You need to copy the .db file to app local folder.

    For example... run the following code at the initialization part of the app.

    var dbFile = await ApplicationData.Current.LocalFolder.TryGetItemAsync("hoge.db") as StorageFile;
    
    if(null == dbFile)
    {
      // first time ... copy the .db file from assets to local  folder
      var localFolder = ApplicationData.Current.LocalFolder;
      var originalDbFileUri = new Uri("ms-appx:///Assets/hoge.db");
      var originalDbFile = await StorageFile.GetFileFromApplicationUriAsync(originalDbFileUri);
    
      if(null != originalDbFile)
      {
    	dbFile = await originalDbFile.CopyAsync(localFolder, "hoge.db", NameCollisionOption.ReplaceExisting);
      }
    }

    Then, sqlite can load the db file with the file path.

    protected SQLiteAsyncConnection GetConnection(string dbName)
    {
      // dbName = 'hoge.db'	
      // pool is acquired by ' new SQLiteConnectionPool(new   SQLitePlatformWinRT());'
      if (null == pool) { return null; }
      return new SQLiteAsyncConnection(() => pool.GetConnection(new SQLiteConnectionString(ApplicationData.Current.LocalFolder.Path + "\\" + dbName, false)));
    }

    • Proposed as answer by Fred Bao Wednesday, March 30, 2016 9:11 AM
    • Marked as answer by Barry Wang Wednesday, April 6, 2016 10:10 AM
    Friday, March 25, 2016 7:29 AM