UWP SQLite - Can't find/create .db file

Question

I'm really new to SQLite and I want to use it in an UWP app. The thing is, I currently have this to make the connection:

public static void InitializeDatabase()
        {
            using (SqliteConnection db =
                new SqliteConnection("Filename=DatabaseFile.db"))
            {
                db.Open();

                String tableCommand = "CREATE TABLE IF NOT " +
                    "EXISTS MyTable (Primary_Key INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "Text_Entry NVARCHAR(2048) NULL)";

                SqliteCommand createTable = new SqliteCommand(tableCommand, db);

                createTable.ExecuteReader();
            }
        }

But I want a database system that Keeps all it's data in it (like mysql). Is this possible with SQLite? And how? Also, if I use that code, I nowere get the file created? How is this possible?

I have create a .db file, with the tables in it (using this software)

Project structure:

And added some data in it already, that file can I use, but how/where, I have no clue?

score 4 · Answer 1 · answered Dec 31 '17 at 18:52

4

The code snippet you provided will create the database file within the app's local storage folder, which is usually found at the windows path C:\Users\[USER_NAME]\AppData\Local\Packages\[PACKAGE_ID]\LocalState (the PACKAGE_ID of your app is listed as Package Name on the Packaging tab of the appxmanifest file). Since this is basically one of very few folders your app has full read / write access to, there are not so many options for placing the database file somewhere else.

If you have a predefined database that already contains some tables, and you would like to package that as part of the app, you can simply add it to the project tree as correctly shown in your screen shot, but don't forget to open its Properties and set the Build Action to Content, otherwise it will not be part of the final app package:

Then, you would need to copy this file to the app's storage folder before creating the SQLite Connection, e.g. using something like:

var file = await StorageFile.GetFileFromApplicationUriAsync(new Uri("ms-appx:///DatabaseFile.db"));
await file.CopyAsync(ApplicationData.Current.LocalFolder);

The ms-appx:/// prefix tells the file system that the file is present within the app package's root folder.

In a real life app, you'll like to add a check whether the file exists already (only copy it on the first app startup probably!), excception handling, etc.

answered Dec 31 '17 at 18:52

andreask

4,248
1
20
25

I don't get it, so I must run it on the app startup, but only once? SQLite is a .db file, so you can save to the file and keep it in there, right? I don't get it how I can use it then or how I can say "use that file" :/ Since I have "standard" data that has to be used. – Robin Dec 31 '17 at 22:56
If the db only contains standard data that will not be changed throughout the app, I guess it does not matter if you copy it once or on every startup. The core point is: FIRST copy the packaged db to disk (as shown in my answer), THEN create the connection as in your code snippet - if the copied db file has the exact same name as specified in the `SqliteConnection` command, the copied file will be used automatically instead of creating a new database, allowing you to read all the pre-packaged data! – andreask Dec 31 '17 at 23:08
The thing is, I don't want to loose my data every time. So it has to read the file, save it back modified and re read (like a mysql database) – Robin Dec 31 '17 at 23:10
Remember it is a database - just do UPDATE / INSERT operations and you are set, there is no need to explicitly save the *database file* to the file system after each operation. – andreask Dec 31 '17 at 23:17
So I just need to do an init of the database file I made and then I'm set.. okey, but I have to only run it one, so how can I do that? Or I must save the latest file from the local storage to the initial database file? Or how should I da that? – Robin Dec 31 '17 at 23:22
Well, copy the pre-filled db file only on the first run (e.g. by checking whether it already exists on disk, see https://stackoverflow.com/questions/8626018/how-to-check-if-file-exists-in-a-windows-store-app), then work on this file for all the subsequent application runs. This way the data is maintained as long as no one uninstalls the app. – andreask Dec 31 '17 at 23:25
It sort of works, except I can't get data from it? I t's strange, but I'll have a default database (wich I will create and work durther on that one), and then work on that one, but I can't get data from it? – Robin Jan 02 '18 at 14:44
@Robin if this has resolved your issue, could you please accept the answer as solution so that the question is closed? – Martin Zikmund Dec 01 '18 at 06:57

score 1 · Answer 2 · answered Jan 01 '18 at 11:48

Just as an addition to the answer by @andreask, it is also possible to work directly with the DB in application package, albeit just in read-only mode. You can get the "real" filesystem path like follows:

var file = await StorageFile.GetFileFromApplicationUriAsync(
              new Uri("ms-appx:///DatabaseFile.db"));
var dbPath = file.Path;

And then connect to the DB normally. Any modification queries will fail however.

score -1 · Answer 3 · answered Dec 31 '17 at 18:48

-1

SQLite is not a server database like MySQL or SQL server. The database is created in the apps directory. Since UWP apps run in a sandbox it cannot be used by another UWP app.

answered Dec 31 '17 at 18:48

Ken Tucker

4,126
1
18
24

UWP SQLite - Can't find/create .db file

3 Answers3