×

First time here?

You are looking at the most recent posts. You may also want to check out older archives. Please leave a comment, ask a question and consider subscribing to the latest posts via RSS or email. Thank you for visiting!

It looks like people are really glad about being able to use SQLite within their Metro style apps.  I had written two previous posts (Using SQLite in your Metro style app and HOWTO: Build and include SQLite) about this topic.  I’m pleased to report that since those posts the SQLite team released a build (3.7.13 as of the datestamp on this post) which also provides the binary (32- and 64-bit versions) pre-compiled for you for inclusion in your Metro style app.  You can get them from the SQLite download page.

I’ve received a few comments/questions that I thought I might clarify in my own opinion (and some facts) about using SQLite in your app.

Creating new databases

The first thing to understand is that your app lives in a secure sandbox during operation.  This is also referred to as the AppContainer in the Metro style app world.  What this means is that you can only do certain operations in certain places or through brokers provided by the various WinRT APIs.  The first stumbling block I’ve seen people try to do is create a database in places where they cannot create databases.  When using SQLite, regardless of whatever client method you use to program with it, you need to pass in a full path to where the database should be created (or an existing one that you are opening).  Simply passing “foo.db” in the open method is not enough as that will assume an incorrect path to create the database file.  Another incorrect thing that folks are doing is using the Windows.ApplicationModel.Package.Current.InstalledLocation.Path API.  This represents the location of where your app is installed which is not an area you can directly write files/content.

NOTE: SQLite uses the CreateFile2 API which is not a WinRT broker API.  This means that it is restricted to certain areas of the AppContainer.

The other area where people are trying to create files is in the document library location for the user.  If you have declared the Document Library capability as well as provided a file association for the file you want to create, then you can read/write files in the Document Library using the WinRT broker APIs.  This, however, is not possible using the CreateFile2 Win32 APIs. 

This leaves the app’s ApplicationData location.  So the correct path to create your database from your app is Windows.Storage.ApplicationData.Current.LocalFolder.Path as a starting point.  Here’s an example (using the sqlite-net library and C#):

   1: using (var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "foo.db")))
   2: {
   3:     // do stuff here
   4: }

Now whenever I need to query this database I would use that same path from my app.

Seeding your app with starting data

Some folks want to start their application with some seed data.  There are a few ways that you could do this.  One way would be to create a database during startup and execute SQL statements against the newly-created database.  You would basically be shipping a script in your app that you’d run on the first run of the app after install.  If you went this route, then you’d use the method above to create the database and then execute your INSERT statements.

Another method is to use an existing database file that perhaps you’ve already created.  The misconception here that people have is that since they include a seed database in their app that they can just open that database file and read/write on it.  The read part is correct, however you will fail to write to that file as it is in the package install location and not the ApplicationData location.  The first step you want to do in this approach is move your seed database to the place where you can write to it.  You can use the Windows.Storage APIs to accomplish this.  Here’s an example of how you might do this.  This assumes that your app has a file named “Northwind.sqlite” in your package:

   1: // grab the file from the package installed location into a StorageFile
   2: StorageFile seedFile = await StorageFile.GetFileFromPathAsync(
   3:     Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path,
   4:     "Northwind.sqlite"));
   5:  
   6: // copy the StorageFile to the ApplicationData folder
   7: await seedFile.CopyAsync(Windows.Storage.ApplicationData.Current.LocalFolder);

Now the code above does the copy.  Of course you would want to add some logic to verify that you aren’t overwriting an existing database.  Just like anything else there are various ways to do this so I am not prescribing any one way.  Once you get the data where you need it to be, then you can work with the database how you’d like.

I hope this helps understand the method of creating (in the right place) and seeding your app with a SQLite database.  Hope this helps!


This work is licensed under a Creative Commons Attribution By license.


6/28/2012 3:43 PM | # re: Seeding your Metro style app with a SQLite database
Wonderful! I'm transitioning my current application to use SQLite; knowing this will allow my application to (eventually) be ported to WinRT is great.

6/29/2012 12:00 AM | # re: Seeding your Metro style app with a SQLite database
Hi Tim,

Thanks for your great work. I am creating my first app Metro. I compiled sqlite following your tutorial but when I try to create a db I get an error 14 (SQLITE_CANTOPEN ). The path is correct. Any ideas which could be the problem?

Thanks again.
6/29/2012 12:18 AM | # re: Seeding your Metro style app with a SQLite database
@josus - hmm, what library are you using? c#? can you verify the path before you use the open command?
6/29/2012 1:24 AM | # re: Seeding your Metro style app with a SQLite database

Yes, I'm using C#. The code is clean (just your sample)

string dbRootPath = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
using (SQLite.SQLiteConnection db = new SQLite.SQLiteConnection(Path.Combine(dbRootPath, "mypeople.sqlite")))
...

I'll check the path this afternoon, now I'm on Win7.

Thanks for the quick response!!
6/29/2012 8:40 AM | # re: Seeding your Metro style app with a SQLite database
Well, I'm on Win 8 now. I'm trying to debug using simulator and the error code 14 persists. I'd downloaded the latest build from the sqlite webpage, 3.7.13 x64. I'm using sqlite-net library. The path just before the open seems to be a temporary deploy folder like

"C:\Users\user\AppData\Local\Packages\4b4593e0-f2b6-4d5e-9698-e025841bea9ecvk6sz7dbqcsc\LocalState\mypeople.sqlite"

running on localmachine throws the same error...

Any help would be apreciated.
Gravatar
6/29/2012 9:32 AM | # re: Seeding your Metro style app with a SQLite database
Hello

I had the same issue. I found a temporary solution. I create an empty file xx.sqlite in LocalState and after I can open as the sqlite db.
Some code :

private async void Button_Click_1(object sender, RoutedEventArgs e)
{
var localFolder = ApplicationData.Current.LocalFolder;
var fileName = "test2.sqlite";
await CreateFileAsync(localFolder, fileName);
var path = Path.Combine(localFolder.Path, fileName);
using (var db = new SQLiteConnection(path))
{
db.CreateTable<Stock>();
db.CreateTable<Valuation>();
db.Insert(new Stock { Symbol = DateTime.Now.ToString() });
ListView_Stock.ItemsSource = db.Table<Stock>().OrderBy(p => p.Id);
}
}

public static async Task CreateFileAsync(StorageFolder folder, String filename)
{
try
{
await folder.GetFileAsync(filename);
return;
}
catch (System.IO.FileNotFoundException)
{
}
await folder.CreateFileAsync(filename);
}

6/29/2012 10:08 AM | # re: Seeding your Metro style app with a SQLite database
Problem solved. My user profile path had some spanish chars.
Maybe a problem on non english systems...

Thanks for your workaround, Fox
6/29/2012 12:01 PM | # re: Seeding your Metro style app with a SQLite database
I've been curious about the situation regarding the following quote. What's the guidance for using SQLite with persistent and/or transferrable database storage?

"The other area where people are trying to create files is in the document library location for the user. If you have declared the Document Library capability as well as provided a file association for the file you want to create, then you can read/write files in the Document Library using the WinRT broker APIs. This, however, is not possible using the CreateFile2 Win32 APIs.

This leaves the app’s ApplicationData location. So the correct path to create your database from your app is Windows.Storage.ApplicationData.Current.LocalFolder.Path as a starting point."

However, the ApplicationData documentation states that "Application Data should not be used to store user data or anything that users might perceive as valuable and irreplaceable. The user's libraries and SkyDrive should be used to store this sort of information." And furthermore the guidance says "Don't use roaming to move large datasets."

So how should I get a database into a persistent storage location (and potentially a location I can access from other devices, e.g.: SkyDrive)? Create the file in the ApplicationData location, and then copy to persistent storage when idle, or on a timer, or on suspend/terminate (and hope it copies in time)? And copy from persistent storage back to ApplicationData storage on subsequent runs of the application? None of this sounds very clean or robust...
6/29/2012 7:19 PM | # re: Seeding your Metro style app with a SQLite database
Just downloaded the latest sqlite.dll-winrt from sqlite.org. I don't find a "winmd" and when I add a reference to the file I get an error msg cannot add not valid com. Any ideas?
7/2/2012 8:40 AM | # re: Seeding your Metro style app with a SQLite database
@Fritz - you don't 'add reference' to this DLL. This represents the SQLite engine using this method. For a C# app you need to use a client library that does the correct native code interop to call into this. If you are a C++ application you could use the source directly (sqlite3.c/h). This is not a WinRT component itself but is a build of the SQLite database runtime that is 'safe' for WinRT (will pass store certification and uses approved APIs).
7/3/2012 1:32 AM | # re: Seeding your Metro style app with a SQLite database
Hi Tim,

On topic:

GreAt posts and keep on bringing us some quality XAML information. For me personally I would like to know how you see WinRT validation happen in the future?

Off topic:

The website (blog) of your dear colleague Pete Brown isn't available from here in Belgium (Europe) since saterday AFAIK (something to do with IPv4 - IPv6 switch I don't know... :) )

Kind regards,
Thomas
7/4/2012 9:10 AM | # re: Seeding your Metro style app with a SQLite database
Greate job, thanks!
I have a probelm that I cannot solve it myself. I created a method. It will be used when the application is ran first.
public async Task CopyDatabase()
{
StorageFile seedFile = await StorageFile.GetFileFromPathAsync(Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path, "Northwind.sqlite"));
await seedFile.CopyAsync(Windows.Storage.ApplicationData.Current.LocalFolder);
}

I cannot find a way to call it. I tried to called it in the constructor of the MainPage, but I got the error which said I could not call an async methord from the constructor. Where can I call the methord? And how can I decide if the datase has to copied the the path? Thanks advance.
7/4/2012 7:04 PM | # re: Seeding your Metro style app with a SQLite database
I found the solution. I called the copy database method in the Page loaded event. So the root element of the xaml should have: Loaded="Page_Loaded_1". The following code can run smoothly, but but I am still not sure if the loaded event of the page element is the proper location to call the copy database method. Any suggestion?

private async void Page_Loaded_1(object sender, RoutedEventArgs e)
{
await CopyDatabase();
}

private async Task CopyDatabase()
{
StorageFolder Datafolder = Windows.Storage.ApplicationData.Current.LocalFolder;
string DataFilePath = Path.Combine(Datafolder.Path, "LabExam.db");
bool DataFileExisted = await DoesFileExistAsync(Datafolder, DataFilePath);
if (!DataFileExisted)
{
try
{
StorageFile seedFile = await StorageFile.GetFileFromPathAsync(Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path, "LabExam.db"));
await seedFile.CopyAsync(Datafolder);
}
catch (FileNotFoundException ex)
{
txtResult.Text = ex.Message;
}

}
}

private async Task<bool> DoesFileExistAsync(StorageFolder folder, string filePath) {
try {
await folder.GetFileAsync(filePath);
return true;
} catch {
return false;
}
}
7/5/2012 11:31 AM | # re: Seeding your Metro style app with a SQLite database
@Kevin - if you need to seed your app, I would do that first somewhere when your app starts. This could theoretically be done in the App instantiation before any views are used. However you may want to use an extended splash screen approach so that your app is indeed started.
7/5/2012 8:14 PM | # re: Seeding your Metro style app with a SQLite database
Tim, I added the code of copying database to the OnLaunched method of my app and it works. Thanks. I cannot find the dll of Sqlite for ARM in their official website. Any solution?
7/5/2012 11:45 PM | # re: Seeding your Metro style app with a SQLite database
@Kevin - you'd have to build the ARM version yourself.
7/11/2012 2:51 AM | # re: Seeding your Metro style app with a SQLite database
It would be great if you could address @Andrew's question.

As I understand the situation:

We can only use SQLite with the localFolder.

The application data docs on MSDN , as @Andrew points out, state that this shouldn't be used for important data since any data stored there will be deleted when the app is removed.

W8's File History back-up feature doesn't appear to support metro app data stored in localFolder so we can't benefit from that either.

Is this correct?
7/11/2012 10:19 AM | # re: Seeding your Metro style app with a SQLite database
@Chris - yes, the ApplicationData locations (as are any client-side locations) are susceptible to user deleting data. As such any app should be resilient to data not being there and never assume I would say. This could be a simple check to make sure any seed data is there or just handling error conditions properly. The guidance is just indicating that this is an area where users have permission. That said it is specific to the app (i.e., no other app can modify that data). If the app is removed, yes appdata may be removed as well and would not be there when re-installed on that machine.
7/12/2012 8:15 AM | # re: Seeding your Metro style app with a SQLite database
v
7/12/2012 9:51 AM | # re: Seeding your Metro style app with a SQLite database
Thanks Tim.

I guess I read the guidance on MSDN as being stronger than it was intended to be.

In my own circumstance I'm bringing what Apple terms a "shoebox app" (database + supporting files) rather than a document-based app to Metro so, since the app's the only way to interact with data, deletion of the app deleting the data as well isn't too unreasonable.
7/12/2012 9:57 AM | # re: Seeding your Metro style app with a SQLite database
Thanks Tim.

I guess I read the guidance on MSDN as being stronger than it was intended to be.

In my own circumstance I'm bringing what Apple terms a "shoebox app" (database + supporting files) rather than a document-based app to Metro so, since the app's the only way to interact with data, deletion of the app deleting the data as well isn't too unreasonable.
7/17/2012 8:44 AM | # re: Seeding your Metro style app with a SQLite database
Hello, I have the accent in path problem too - my user name and user folder name is "Bálint", which was auto generated for me by Windows8. I can create textfiles and move around images using the StorageFile calls; but SQLite3.open ALWAYS fails on this special path!

I've even tried to modify SQLite.cs' SQLiteConnection function:

var r = SQLite3.Open (Encoding.UTF8.GetBytes(databasePath), out handle, 0, Sqlite3Statement.Zero);

...and still no luck - however databasePath is the Path.Combined string from the example above.

Any hints?

(ps. of course I can create a new user without an accent for myself, but if it happens to me, it will surely will happen to anyone out in the wild too when we roll out this app to the store)
7/17/2012 9:02 AM | # re: Seeding your Metro style app with a SQLite database
I've found the solution. The correct call is (SQLITE_OPEN_READWRITE = 2):

var r = SQLite3.Open (Encoding.UTF8.GetBytes(databasePath), out handle, 2, Sqlite3Statement.Zero);

and it will work with accents in path too! :)
7/17/2012 9:21 AM | # re: Seeding your Metro style app with a SQLite database
@Balint - the latest version of sqlite-net has a fix for this for Unicode chars in paths. Alternatively you've found the param
7/17/2012 9:38 AM | # re: Seeding your Metro style app with a SQLite database
@timheuer: well yes - I've found it now - haven't scrolled that far in the source code. :)
However it's in the

public SQLiteConnection (string databasePath, SQLiteOpenFlags openFlags)

function only. possibly would be a good idea to include it in

public SQLiteConnection (string databasePath)


7/18/2012 6:59 PM | # re: Seeding your Metro style app with a SQLite database
Perhaps I'm confused. Sqlite sounds like a great option, but how do you account for x86, x64, and ARM based architectures and the windows store? I'm confused on how I would deploy my metro app to the windows store with 3 flavors. Any ideas?
7/18/2012 7:12 PM | # re: Seeding your Metro style app with a SQLite database
@Sam - you would create 3 different packages to upload to the store. The Store package creation process in VS2012 helps you do this simply.
7/19/2012 5:26 AM | # re: Seeding your Metro style app with a SQLite database
Thanks Tim for the clarification, I'm definitely on board with SQLite. I'll go ahead and will submit my app to the app store to go through that process to ensure I'm comfortable. My second release will include better offline support thanks to SQLite.
7/31/2012 10:12 AM | # re: Seeding your Metro style app with a SQLite database
I've managed to install SQLite fine but I get an unhandled exception when I try to run db.CreateTable().
More specifically, the exception rises from the Prepare2(IntPtr db, string query) method. The local variable r is not being instantiated correctly.

Any idea as to what could be going on here?
7/31/2012 2:20 PM | # re: Seeding your Metro style app with a SQLite database
Nevermind I've got everything working. I had not designated a primary key in my data class. I did that and now everything's working.
9/3/2012 4:47 AM | # re: Seeding your Metro style app with a SQLite database
Hi Tim,

Can I roam the data saved in Sqlite?
9/10/2012 1:34 PM | # re: Seeding your Metro style app with a SQLite database
Hi Tim,,
Am I missing something, I am getting Following Errors -
The wait operator can only be used within an async method. Consider marking this method with 'async' modifier and changing its return type to 'Task'.
same for second time I am using await again.
Thank you.
9/24/2012 11:01 AM | # re: Seeding your Metro style app with a SQLite database
I have changed the module to async Task and erroor is gone but it (async Task) is going to all the modules up in the heirarchy. Is it right?
10/13/2012 4:17 PM | # re: Seeding your Metro style app with a SQLite database
Hi Tim ,

Great post. Saved my time a lot. kevin le, Thanks for posting code. I used it..

10/18/2012 4:41 AM | # re: Seeding your Metro style app with a SQLite database
Hi,

Regarding the database folder, I found that each time a change is made to the app manifest file i.e. references etc Windows RT creates a new package folder for the app and then I do not have access to my old database!

Is there a recommended solution to this? I am thinking in what would happen once I start publishing updates to my app in Windows Store.

Regards
10/18/2012 10:38 AM | # re: Seeding your Metro style app with a SQLite database
How can I execute sql query without knowing table structure? I mean, we don’t know what is the client will pass. They will just give the query and we have to execute.

for example:

“CREATE TABLE IF NOT EXISTS employee (id REAL, name TEXT)”;
OR
“CREATE TABLE IF NOT EXISTS employee (id REAL, name TEXT, phone REAL)”;
OR
“CREATE TABLE IF NOT EXISTS employee (id REAL, name TEXT, add TEXT)”;

Any sql query they can ask, is it possible in metro app?
10/24/2012 11:54 AM | # 
Tim, Thank you for this post! I've been looking for like an hour on how to do this because my app was throwing the exception when I was trying to write to the DB file. I had never thought about making a copy of the file into the ApplicationData directory.

Question for you: would you consider deleting the seed file after installing the app package and copying the data into the ApplicationData? Normal SQLite DB's won't take up a lot of space and so this would normally be a meaningless gesture, but is there any real advantage of keeping the original db file around?
10/27/2012 2:23 PM | # re: Seeding your Metro style app with a SQLite database
Thanks for providing this essential information, now I feel like I can start to create a real program!
11/4/2012 12:13 AM | # re: Seeding your Metro style app with a SQLite database
Tim, thank you very much for your help and your patience answering all these questions.
I am one of those who "are trying to create files is in the document library location for the user" (as you wrote it).
I have several WinRT apps that need to share information. Your recent posts about SQLite gave me the idea of using a SQLite database in the user's Document folder. After much trying I found this current post.
My question: what would you recommend to solve this problem (specifically several WinRT apps that need to share data in real time)? I do not want to use a resource on the Cloud for that purpose.
I could use a simple file (in the user's folder) but then I would not have the SQL features.
Any help/suggestions would be welcome. Thank you.
Gravatar
11/19/2012 7:04 AM | # re: Seeding your Metro style app with a SQLite database
hi tim,
in my code ,there are some chinese chars, i also use the 'SQLiteConnection (string databasePath, SQLiteOpenFlags openFlags, bool storeDateTimeAsTicks = false)' one. but still, i got this:
Could not open database file: C:\Users\志强\AppData\Local\Packages\03d60b0b-9e03-4676-946c-1ec138d37f95_yps17xarsp2at\LocalState\Feeds.sqlite (CannotOpen)
Any idea of this?
my sqlite-net version is 1.0.5
12/5/2012 6:02 PM | # re: Seeding your Metro style app with a SQLite database- AppX folder
Hi ,

I am running into a problem where VS copies the sqlite.db file included in my project to
C:\work\projects\VS2012\SQLiteTestApp01\SQLiteTestApp01\bin\x86\Debug

however Windows.Storage.ApplicationData.Current.LocalFolder.Path
returns the string
C:\work\projects\VS2012\SQLiteTestApp01\SQLiteTestApp01\bin\x86\Debug\Appx
(note the Appx on the end)

Is there something I am missing.

(PS I have read your other SQLite articles and they have been a great help. Thanks!!)
12/5/2012 6:20 PM | # re: Seeding your Metro style app with a SQLite database
Sorry I posted the wrong Property. The problem still stands (I just made a mistake on the post here)

I meant
Windows.ApplicationModel.Package.Current.InstalledLocation.Path
returns the Appx Folder
C:\work\projects\VS2012\SQLiteTestApp01\SQLiteTestApp01\bin\x86\Debug\Appx
but VS copies the file to
C:\work\projects\VS2012\SQLiteTestApp01\SQLiteTestApp01\bin\x86\Debug\
12/5/2012 6:37 PM | # re: Seeding your Metro style app with a SQLite database
Apologies for spamming your comments but I did find the solution to my problem above.

In VS I needed to set the Build Action on the file properties to "Content" for it to be included in the Appx folder.
8/3/2013 5:28 PM | # re: Seeding your Metro style app with a SQLite database
I am still not sure about upgrades. If my someone has my app and has data they have entered, will SQLite overwrite their data or will it remain untouched? My app has only basic SQLite commands like INSERT and DELETE and SELECT. Nothing to detect current data or deal with an upgrade. The SQLite version and the data structure are unchanged.

 
Please add 7 and 5 and type the answer here:

DISCLAIMER:

The opinions/content expressed on this blog are provided "ASIS" with no warranties and are my own personal opinions/content (unless otherwise noted) and do not represent my employer's view in any way.