Wednesday, August 22, 2012

Getting started with SQLite in Windows Store / WinRT apps

In this blog post I will expand the blog post by Tim Heuer  to include information on how to include and access a pre-populated SQLite database file, maybe even a file created by migrating from a SQL Server Compact database file, as I blogged about recently.

First, download the "SQLite for Windows Runtime" Extension via Tools/Extensions and Updates/Online. Restart Visual Studio.

Then add references to the SQLite and C++ extensions as described by Tim Heuer. Remember to change the Build Configuration to either x64 or x86 in Configuration Manager.

Now add the sqlite-net nuget package to the project, from the References node, select "Manage NuGet Packages" and search online for "sqlite-net":

clip_image002

This will add SQLite.cs and SQLiteAsync.cs to your project.

Now add the SQLite database file to your project as Content:

+clip_image003

If you want the database file to be writeable, you will have to copy it to your local appdata folder. Keep in mind, that when your app is uninstalled, the file will be removed.

You can use code like the following to ensure that the file has been copied:

private string dbName = "chinook.db";
private async void LoadData()
{
await CreateIfNotExists(dbName);
}




private async Task CreateIfNotExists(string dbName)
{
if (await GetIfFileExistsAsync(dbName) == null)
{
StorageFile seedFile = await StorageFile.GetFileFromPathAsync(
Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path,
dbName));
await seedFile.CopyAsync(Windows.Storage.ApplicationData.Current.LocalFolder);
}
}

private async Task<StorageFile> GetIfFileExistsAsync(string key)
{
try
{
return await ApplicationData.Current.LocalFolder.GetFileAsync(key);
}
catch (FileNotFoundException) { return default(StorageFile); }
}



And code like this to access data (see the sqlit-net site for more samples) https://github.com/praeclarum/sqlite-net

protected override void OnNavigatedTo(NavigationEventArgs e)
{
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, dbName);
using (var db = new SQLite.SQLiteConnection(dbPath))
{
var list = db.Table<Artist>().OrderBy(a => a.Name).ToList();
}
}


//This would reside in another file or even project
public class Artist
{
[SQLite.PrimaryKey, SQLite.AutoIncrement]
public int ArtistID { get; set; }
public string Name { get; set; }
}

public class Album
{
[SQLite.PrimaryKey, SQLite.AutoIncrement]
public int AlbumID { get; set; }
public string Name { get; set; }
public int ArtistID { get; set; }
}



clip_image004

Hope this will be able to get you started using SQLite with your Windows Store app.

You can download the complete sample with a database file from this link (all code above is in MainPage.xaml.cs): http://sdrv.ms/Pd1xeL

16 comments:

Jerry Nixon said...

Excellent article! Now, do you save dual places (local and cloud or roam)? And, what's your strategy to keeping them in sync if you fall offline? //Jerry

ErikEJ said...

Thanks, Jerry. Well, this initial article mainly deals with local reference data. It sounds like your questions are topics for other blog posts, but for backup, a feature like Copy to SkyDrive comes to mind, and one could do inserts to a separate database file, and implement a synchronize mechanism, that clear the local inserted rows when done. Updates and deletes are more compilcated, but in many scenarios less common.

A Wieser said...

Hi Erik,
Stumbled across a bug today to do with Unicode characters in user names causing databases to fail.

I've been bit by the Irish problem as I call it before, after a customer named O'Casey (name changed obviously) caused problems because of his user name.

Well, there seem to have been some problems here:
http://social.msdn.microsoft.com/Forums/en-AU/winappswithcsharp/thread/875fc167-b8c4-4d45-a74d-67e311d3c436
to do with Unicode, so I tried to create my SQLite database with this filename:

"⛇.db"

(yes that's a snowman!)

Well, needless to say, it doesn't work. So if your user has a name with Unicode characters (or snowmen) in it, you're in for app failure!

My cure was to modify SqlLiteConnection in SQLite.cs as follows:

// var r = SQLite3.Open (DatabasePath, out handle);
var utf8 = new UTF8Encoding();
var r = SQLite3.Open(utf8.GetBytes(DatabasePath), out handle, (int)(SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create), Sqlite3Statement.Zero);

This forces the database open with a UTF8 filename, and processes the database as UTF-8.

You can also use Open16, but that I believe opens the file in UTF16.

ErikEJ said...

Hi Anthony: Thanks for the useful information, maybe you should report this at the sqlite-net project on github

Sibin P said...

Hi ErikEJ,

Thanks for the excellent article!

Would you please tell me how to use an already existing sqlite database(like Chinook.db) in windows 8.1 RT app using WinJS/Javascript and HTML instead of C#?Do you have any sample code or tutorial?

ErikEJ said...

Sibin: No, I do not, I think you will have to use soemthing like: https://sqlwinrt.codeplex.com/ but not sure.

Sibin P said...

Thanks Erik for the url,

I got it solved in a similar way as https://sqlwinrt.codeplex.com/.

Like you have mentioned in your article first i pasted my db in the project folder. Then in the db file properties set it to copy always.

Then i used the following code:-

var dbPath = Windows.ApplicationModel.Package.current.installedLocation.path + '\\mydb.db';

It worked!

Ankit Soni said...

hi there i made an app package but when i install it using power shell it make database with 0kb and without any table and data what is the problem. i take database as content and make copy always option enable.

ErikEJ said...

Ankit: suggest you ask in a forum, and provide more info, for example some code

Unknown said...

Hi! Your code is very helpful. I like to ask some out topic question. What is best practice for the database connection? I am thinking of maintaining one and open SQLiteConnection object since Store apps are not multi-user.

ErikEJ said...

Unknown: the sqlite connection is not thread safe, that is all I know

S.R said...

I have always seen that in all tutorials, databases are made in the form of code-first, but already there is a sqlite database that I want to use in my project. And I want to use entity 7 too. To do so, I made some classes from my database by means of a dll but it didn't work. In fact, I made these classes so that I could use my own database. I didn't want it to create a new database. Any suggestions?

ErikEJ said...

S.R: I beleiv you can use scaffolding: http://ef.readthedocs.org/en/latest/cli/powershell.html#scaffold-dbcontext

S.R said...

It is possible to explain with an example.
I'm really confused and need help :(

ErikEJ said...

S.R: Yes it is, but not is blog comments - please ask on Stackoveflow or similar

S.R said...

http://stackoverflow.com/questions/36361731/how-can-i-use-the-database-that-i-already-have-got-with-entity7-in-uwp