.NET Zone is brought to you in partnership with:

Erik Ejlskov Jensen is a .NET Data Developer, and SQL Server Compact MVP. He is also the author of a number of tools for SQL Server Compact. He has been working in IT for too many years, and enjoy blogging (http://erikej.blogspot.com) and tweeting (@ErikEJ) Data Development related news and tips. Erik Ejlskov is a DZone MVB and is not an employee of DZone and has posted 58 posts at DZone. You can read more from them at their website. View Full User Profile

Getting Started With SQLite In Windows Store / WinRT Apps

08.22.2012
| 6313 views |
  • submit to reddit

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 

Published at DZone with permission of Erik Ejlskov Jensen, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)