One of the comments I’ve received in the blog post I wrote about using SQLite in a Windows 8 application was a really interesting question: what can I do if I have a pre populated SQLite database and I want to use it in my application? In fact, the blog post I wrote was based on a “code first” approach: according to some classes attributes, the database was generated during the first execution of the application.
The solution is pretty simple, thanks to the WinRTs APIs used to interact with the storage. If you recall the explanation in my previous post, you’ll know that sqlite-net (the library used to interact with the SQLite database using a LINQ syntax) looks for the database file inside the local folder of the application. What we’re going to do is to include our database file into the project and then, when the application is launched for the first time, copy it into the local storage, so that the library can access to it.
The first step is to copy your database in to the Visual Studio project and, from the Properties window, set the Build action to Content.
Once you’ve done this operation, you’ll be able to access to the files embedded in your project thanks to the Package.Current.InstalledLocation object that is available in the Windows.ApplicationModel namespace.
The InstalledLocation’s type is StorageFolder, which is the base class of all the folders mapping in WinRT: for this reason, it exposes all the standard methods to interact with the storage, like getting a file or a folder. This way we can use the GetFileAsync method to get a reference to the database embedded into the project and, after that, using the CopyAsync method we can copy it into the local storage of the application. We can copy it in the root of the local storage (like in the following example) or in a specific folder, by getting a reference to it first using the GetFolderAsync method.
Here is a sample code:
private async Task CopyDatabase()
{
bool isDatabaseExisting = false;
try
{
StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync("people.db");
isDatabaseExisting = true;
}
catch
{
isDatabaseExisting = false;
}
if (!isDatabaseExisting)
{
StorageFile databaseFile = await Package.Current.InstalledLocation.GetFileAsync("people.db");
await databaseFile.CopyAsync(ApplicationData.Current.LocalFolder);
}
}
The code should be simple to understand: the first thing we do is to check if the file already exists or not. If it doesn’t exist, we get a reference to the file embedded in the Visual Studio project (called people.db) and then we copy it in the local storage (that is mapped with the object ApplicationData.Current.LocalFolder). This way the file is copied in the root of the local storage.
After that, we can use the same code we’ve seen in the first post to access to the database and perform queries: it’s important to remember that the name of the classes that identify the database tables should have the same name of the tables in the database we’ve imported.
Really Great post 🙂 . i have doubt using this i was able to copy the data present in my sqlite database file into the storage file , now i want to do operations on it like insert for amount of data and manipulate some data in few data tables , but it is showing a exception only read data , please let me know how can i work around it . Thanks in Advance
Can you send an example? I’ve tried with my sample project and I’m able to add new data to the database that is copied into the storage at the first time.
first of all i am very sorry for the late reply , excuse me for this . i was able to implement it successfully.
I have imported and copied the sqlite db into local storage of the application using above code.I have for example say a database table named “abc” at sqlite db.So for doing db operations like you mentioned in previous post do I have to again define class “abc” for table.When I tried to use table directly like “var query = conn.Table();” without defining class I am getting error.
Hey I got it resolved.(should have done a better research..my bad)
Good to know! Which was the issue?
Was a mistake from my side.I dint define class for table as per table in sqlite which I have imported.
Forgot to mention….great article.:)Thanks a lot
Thanks for a great article! I’m however stuck, the database file is included and Build action set to Content, but it doesn’t get included when I create an app package. Any idea what I’m doing wrong?
Hi, did you manage to solve this problema? I having a hard time too with this
Not so far I have found new cool tool to work with SQLite on win – Valentina Studio. Its free edition can do things more than many commercial tools!!
I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview
Thanks, it looks interesting!
I have done with windows application in wpf. I have also done with setup of that appplication. I installed it on windows 8.I am not able to insert any row in sqlite database. it gives me an error that unable to open an database as it run well with windows7. Let me know what is issue & how can i solve it.
Thanks to all for help
Help is need for junior one.
Hi, I’m sorry but I can’t help, I don’t have experience with WPF applications and SQLite. I suggest you to post your questions in forums like the official MSDN one or StackOverflow.
How to wp8 sqlite Win/RT database migrate?Give me samples..
The sqlite-net library works in the same way both for Windows 8 and Windows Phone 8 and also the Storage APIs are the same, so the code described in this post can be used also in Windows Phone 8 without changes.
Thanks for this post. Great info.
Do you know of a resource, tutorial, etc.. that would show the best way, to.. instead of importing a sqlite file, instead use InsertAsync’s to insert just a couple of sample records upon app launching?
I’m running into a situation where, the OS is not finished doing the createTableAsync and record insertions (which happens in app.xaml.cs), and quickly reaches my ToListAsync() call that starts up in my MainViewModel constructor, and obviously crashes because I’m trying to read an un-init’d database into the listbox.
( i’m using a single Async Connection in App.xaml.cs, so maybe I shouldn’t use async for now or something.)
But I’m thinking maybe there’s a trick to it.
Hi, your approach is good, but I think you’re doing something wrong with the async and await approach. If you use correctly the async and await pattern, every method is “awaited”, so if you call the method to create the tables and, then, you call the one to insert the data, the insert operation isn’t performed until the create table one is completed.
A good idea would be to move the table creation logic in the main page, instead of the App.xaml.cs page, since the application lifecycle events (like Application_Launched) can’t be asynchronous.
Hi,
Thanks for the post. I’m having a hard time with something here. My App is already working when deploying it in visual studio. But when i install it on after making the package it stops working. I bielieve the problema is related to the momento when copying the db. Can you Help me with this please?
Hi, which is exactly the error you’re facing?
Hi,
When doing the App package the db is not copying into local state. But when running it into visual studio it Works fine. I need to make a reléase package and my App isnt working because it doesn’t copy the database
Hi, can you give me more details? What happens? If you explore the storage of your app, is the database there or not?
Thanks for your article! You helped me a lot! Saved my time!
hey I’m totally new to wp8. I’m trying to port my cordova app to wp8. I use a cordova-sqlite plugin to access native sqlite features. I need to use a pre populated db. Can you tell me where I have to put your code snippet? thanks
Hi, I don’t have experience with Cordova, so I’m not sure I’ll be able to help. However, usually this code is executed when the application starts: in my applications, I execute it in the Loaded method of the main page of the app (which is triggered when the page is loaded for the first time).
Hm thanks for your reply! So I placed it in the MainPage and called your function in the constructor. Where do I have to place my “.db”-file ?
In the local storage of the application, as it’s described in the post. The class that identifies the root of the local storage is ApplicationData.Current.LocalFolder.
Sorry but I don’t know where this folder is. I get an System.IO.FileNotFoundException. I’ve put the database in an folder called assets…
Did you follow all the steps described in the post? You have to copy your .db file in your project (the Assets folder is fine) and then, using the sample code provided in the post, copy it into the local storage when the app is launched for the first time. You can achieve it by using the Package.Current.InstalledLocation class, which allows access at runtime to the files that are part of the Visual Studio project.
Sorry I don’t get this to work :(. Can you please take a quick look on my gist? I would be so grateful for your help!
https://gist.github.com/pille72/9615840
thanks!
Hi, the problem I see in your code is that you aren’t using the async / await approach correctly, so the code may not be executed in the proper sequence.
First, you need to change your CopyDatabase() method so that it returns a Task instead of void:
private async Task CopyDatabase()Then, due to the asynchronous nature of the code, you can’t call it in the constructor of the page, but you can use the Loaded event you have already subscribed. In the end, you have to remember to call the CopyDatabase() method putting the await keyboard before:
private void CordovaView_Loaded(object sender, RoutedEventArgs e)
{
await CopyDatabase();
this.CordovaView.Loaded -= CordovaView_Loaded;
}
thank you, I’ve changed the code. But I still get two Exceptions (“System.IO.IsolatedStorage.IsolatedStorageException” and “System.IO.FileNotFoundException”. But the file is still there…
I still get System.IO.FileNotFoundException. But when I list all files in the directory with c#, the file shows up :/
Check that the database file you’ve added to the project is added as Content. You can verify it by checking the Build Action property in the Properties window.
this works perfect, But i m not able to update values for a field in the imported db after copying it to isolated storage.Can some one pls help
When I update a record, I am able to view the changes from DB in isolated storage using sqlite explorer, but running a query on it gives me only the old values, not updated ones.Can you please help me..
Weird. Are you correctly implementing the asynchronous approach to read and write data?
Can you elaborate? Which kind of error are you experiencing?
Hey qmatteoq,
I’ve got a .sqlite file in my project folder and it works fine using the code mentioned in this article.
Is it possible to overwrite this file by an updated version of it (downloaded) in the same local folder or should I move the process to IsolatedStorage?
So far I couldn’t find a way to overwrite it and re-establish a connection with the new db file.
Here’s the code I use to store the downloaded database file:
void client_OpenReadCompleted(object sender, OpenReadCompletedEventArgs e)
{
var file = IsolatedStorageFile.GetUserStoreForApplication();
using (IsolatedStorageFileStream stream = new IsolatedStorageFileStream(“mydb.sqlite”, System.IO.FileMode.Create, file))
{
byte[] buffer = new byte[1024];
while (e.Result.Read(buffer, 0, buffer.Length) > 0)
{
stream.Write(buffer, 0, buffer.Length);
}
}
}
Any thoughts?
Hi Ian,
I’ve replied to you with a private mail, following the mail you’ve sent me using the contact form.
Gr8 post…
I have a slightly different situation.
I have my db file in the project.
I want to copy it to Local (on the device) only once, when the app is installed.
How can i achieve that..??
Check if the file already exists before performing the copy operation. You call the CopyAsync() method only if the file is not existing.
When I install App coping database and when uninstall app database deleted and catch not deleted and reinstall app , function thinks database is exist and do not copy Database. when I restart device catch removed and function coping database how can solve this ?
Hi, can you explain better your scenario? I didn’t understand which is your problem. Thanks!
Hello, I am facing an issue.
What i did was, I inserted few records into my sqlite file and extracted it. Now i placed it onto my project in VS and Done the changes of “Content” as stated in your article. The file is copyied successfully onto Local Folder and i am able to read that file from my code.
But still when i try to fetch the records from that file, no records are fetched.
Make sure that you’re overwriting the original file when you perform the copy operation.
Hi. I’ve come late to this party. I hope someone can help.
I am getting the following error with a Windows Phone 8.1 application (Universal App) while using the technique from this article:
SQLitePCL.SQLiteException: Unable to prepare the sql statement: SELECT * FROM Album Details: no such table: Album
I am following this article http://developer.nokia.com/community/wiki/Connect_Universal_App_with_SQLite_Database_and_MVVMLight_Toolkit#Creating_Our_ViewModels for building an MvvmLight/Sqlite Universal Application, and that article has a reference to this article for handling working with an existing SQLite database.
I’ve verified that the database (the Chinook sample DB) exists and does indeed contain a definition for an album table and that it contains data. I have also verified that the database is part of my solution (in the root folder of the Shared project) and that it’s Build Action is marked as ‘Content’ and Copy to Output Directory is set to ‘Copy if newer’. I have confirmed that the database is indeed copied over if it does not exist, or ‘opened’ if it does. In both cases the storageFile variable is fully populated with details of the SQLite file – which implies to me that the database is indeed present in the Current.LocalFolder (by one means or another).
Given all of the above it is hard to see why it is failing with the noted error – the database is in the right place (as far as I can tell) and contains the requested table. Am I falling foul of some weird security issue that means deep down in the SQLite library I am not actually able to open the database. If so, no such error is visible in the exception chain – in fact there is no inner exception object so the only error information I have is that noted above.
The peculiar thing is that the technique described here works perfectly with the Windows 8.1 project of this Universal Application…it just won’t work with the Windows Phone 8.1 project.
I’m fairly new to Windows 8.x/Windows Phone 8.1/Universal app development so any pointers/suggestions would be welcomed.
Thanks.
Grazie, Thank you. Funziona perfettamente.
Hi Matteo,
I’m coming across a bug mentioned in one or two of the comments here but no answer found yet.
I run the project in Visual Studio and test on my Windows Phone 8.1 device, works fine.
Bit when I create the appx bundle and upload to the store the database doesn’t seem to get included and my queries don’t run.
I checked over the properties of the DB file which is set to content as it should be so not sure why it isn’t copied over when generating the bundle.
Any ideas looking at my code what could be up there? It’s on the store at the moment and not working because of this bug so reaching out for some advice.
My repo is located here:
https://github.com/BrianJVarley/Parking-Tag-SMS-Client/blob/master/Parking%20Tag%20Picker%20WRT/Helpers/DataBaseHelper.cs
I’ve linked the generated appx bundle file here:
https://drive.google.com/file/d/0BzlcEkJ1DNI1bkxVVVBNaElTOFk/view?usp=sharing
Thanks a lot,
Brian
Hello Brian, apologize for the huge delay. Can you show me the code where you actually call the Init() method?